What we need

• Webspace or Server
• PHP Support
• SQL (we use MySQL here)/li>
• Excel or OpenOffice/Libreoffice for viewing results

0 - Introduction

We want to analyse and evaluate access data for our website i.e. Hits per Day/Month/Year, What OS is used, what browser is used etc.
We accomplish this by collection raw data from PHP Server Variables (so we don't need Javascript activated).
Afterwards we define a SQL-view that extracts usefull data for us to be displayed in Excel later.



In addition we use a 3rd party API for resolving IP addresses to geo information like country, state and city.

1 - Database scheme

We use a simple raw data table to store our logging information. We extract certain information later with views so that we don't have to addept the
/> information staging part.



In detail we save: ID, IP address, metainfo, date and file.

CREATE TABLE `counter` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `ip` varchar(250) NOT NULL,

  `metainfo` text NOT NULL,

  `logdate` datetime NOT NULL,

  `file` text NOT NULL,

  PRIMARY KEY (`id`)

);

Below you can see a sample output for the table `counter`. Notice that i blackened the sensitive information i.e. IP-addresses.








the next two tables are needed for adding geo information. Doing this is not that simple but we will get to it after our basic version works.
Just add this tables although we won't fill them until later.

CREATE TABLE `counter_city` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `countryCode` varchar(5) NOT NULL,

  `country` varchar(50) NOT NULL,

  `state` varchar(50) NOT NULL,

  `city` varchar(50) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `countryCode` (`countryCode`,`country`,`state`,`city`)

);



CREATE TABLE `counter_city_todo` (

  `ip_num` int(10) unsigned NOT NULL,

  `active` tinyint(1) NOT NULL,

  `done` tinyint(1) NOT NULL,

  `FKCity` int(11) DEFAULT NULL,

  PRIMARY KEY (`ip_num`)

);

2 - Collect the raw data

To collect the raw data we have to log every access to our webpage.
Assuming we can use PHP we create a php file that handles all the logging by just including the file to each of our files.



An easy way to do this is to use a header.php that is included by all of our websites.



But first let's have a look at the iplog.php:

<?php

$ip = $_SERVER["REMOTE_ADDR"];

$date = date("Y-m-d H:i:s");

$meta = $_SERVER["HTTP_USER_AGENT"];

$data = $_SERVER["PHP_SELF"];



$conn = mysql_connect("localhost","yourdbuser","yourpassword");

mysql_select_db("ksmm",$conn);



mysql_query("INSERT into counter (id,ip,metainfo,logdate,file) VALUES (NULL,'".$ip."','".$meta."','".$date."','".$_SERVER["REQUEST_URI"]."')");

mysql_close($close);

?>

Include this file to all your pages you want to get log information from. Again adding it to a global header.php would be the best solution.

3 - Creating nice data views

Now we have to extract the data from the source view. We do this by using a view.

Our view contains information about a certain website visit i.e.: operating system, browser, city, state, country, year, month, day, hour, minute.

CREATE VIEW counter_processed

 AS 

SELECT

	`yourDBName`.`counter`.`id` AS `id`,

	`yourDBName`.`counter`.`ip` AS `ip`,

	`yourDBName`.`counter`.`metainfo` AS `metainfo`,

	cast(`yourDBName`.`counter`.`logdate` as date) AS `date`,

	concat(right(concat('0',hour(`yourDBName`.`counter`.`logdate`)),2),':00:00') AS `Hour`,

	concat(concat(concat(right(concat('0',hour(`yourDBName`.`counter`.`logdate`)),2),':'),

		right(concat('0',minute(`yourDBName`.`counter`.`logdate`)),2)),':00') AS `Minute`,

	concat(concat(year(`yourDBName`.`counter`.`logdate`),'-M'),right(concat('0',

		month(`yourDBName`.`counter`.`logdate`)),2)) AS `Month`,

	year(`yourDBName`.`counter`.`logdate`) AS `Year`,

	cast(cast(`yourDBName`.`counter`.`logdate` as time) as varchar(20)) AS `time`,

	(case when (`yourDBName`.`counter`.`metainfo` like '%Android%') then 'Android' 

		when (`yourDBName`.`counter`.`metainfo` like '%Linux%') then 'Linux' 

		when (`yourDBName`.`counter`.`metainfo` like '%Windows%') then 'Windows' 

		when (`yourDBName`.`counter`.`metainfo` like '%Apple%') then 'Apple' 

		when (`yourDBName`.`counter`.`metainfo` like '%Mac OS%') then 'Apple' 

		when (`yourDBName`.`counter`.`metainfo` like '%Bot%') then 'Bot' 

		else 'unknown' end) AS `OS`,

	(case when (`yourDBName`.`counter`.`metainfo` like '%MSIE%') then 'Internet Explorer' 

		when (`yourDBName`.`counter`.`metainfo` like '%Opera%') then 'Opera' 

		when (`yourDBName`.`counter`.`metainfo` like '%Bot%') then 'Bot' 

		when (`yourDBName`.`counter`.`metainfo` like '%Firefox%') then 'Firefox' 

		when (`yourDBName`.`counter`.`metainfo` like '%Chrome%') then 'Chrome' 

		when (`yourDBName`.`counter`.`metainfo` like '%Safari%') then 'Safari' 

		else 'unknown' end) AS `Browser`,

	`yourDBName`.`counter`.`file` AS `file`,

	`ci`.`countryCode` AS `countryCode`,

	`ci`.`country` AS `country`,

	`ci`.`state` AS `state`,

	`ci`.`city` AS `city` 

FROM		`yourDBName`.`counter` 

LEFT JOIN 	`yourDBName`.`counter_city_todo` `todo` on inet_aton(`yourDBName`.`counter`.`ip`) = `todo`.`ip_num`

LEFT JOIN 	`yourDBName`.`counter_city` `ci` on `todo`.`FKCity` = `ci`.`id`;

Below you can see an example output of that view. Note that i blackened the IP information again.

4 - Import data in pivot table

4.1 - Using Excel

Install MySQL for Excel: http://dev.mysql.com/downloads/installer/.

In Excel use the DATA tab do install your connection to your database. Then select the view and import the data to your worksheet.

Afterwards navigate to the INSERT tab and click on "create pivot table" and voilá your data is ready to be explored.

4.2 - Using LibreOffice / OpenOffice

todo

5 - Watch beautiful statistics

I will just leave some pictures here. You can do a lot of stuff with pivot tables in general. In the last picture i also used the geo information but in this tutorial we didn't evaluate them.



In my next post we will learn how to evaluate the geo information from the IP data in a nice automatically way.