Анализ посетителей веб-сайта

Рассмотрим одни из самых простейших и первых скриптов, которые появились на веб-сайтах - это учет и анализ посетителей сайта. Этим скриптам необходимо сохранять, редактировать и удалять информацию на сервере, поэтому без СУБД никак не обойтись. Каждый раз при заходе посетителя на страницу, будет вызываться CGI-скрипт счетчика, который будет записывать информацию об одном посещении в нашу базу. Ниже приведена таблица, где мы будем хранить информацию о посещении пользователем страницы нашего сайта:

  CREATE TABLE hit (
  owner_id int unsigned NOT NULL default '0', // id счетчика
  it_date datetime default NULL,              // дата посещения
  
  shref varchar(255) default NULL, // сервер, с которого пользователь
                                   // пришел на нашу страницу

  href varchar(255) default NULL,  // страница сервера, с которой
                                   // пользователь пришел на нашу 
                                   // страницу
                                                
  ip varchar(16) default NULL,     // ip-адрес пользователя
  os varchar(32) default NULL,     // операционная система
  browser varchar(32) default NULL,// броузер
  version varchar(32) default NULL,// версия броузера
  x int unsigned default NULL,     // разрешение экрана по горизонтали
  y int unsigned default NULL,     // разрешение экрана по вертикали
  depth int unsigned default NULL, // количество цветов (глубина цвета)
  cookie enum('0','1') default '0',// поддерживается ли механизм Cookie
  java enum('0','1') default '0',  // поддерживается ли java
  spage varchar(255) default NULL, // url посещаемого сервера
  page varchar(255) default NULL,  // url посещаемой страницы
  frame enum('0','1') default '0', // страница с фреймом или без
  js char(3) default '0'           // версия javascript
) 
  

  
<script language="JavaScript">js=10;</script><script language="JavaScript1.1">
js=11;</script><script language="JavaScript1.2">js=12</script>
<script language="JavaScript1.3">js=13;</script>


<script language="JavaScript">
d=document;n=navigator;s=screen;d.cookie="testparam=testvalue";
d.write('<img width=1 height=1 src="http://itsoft.ru/common/counter?id=1' +
'&r='+escape(d.referrer)+
'&n='+escape(n.appName)+
'&v='+escape(navigator.appVersion)+
'&c='+(d.cookie?"1":"0")+
'&f='+((self!=top)?"1":"0")+
'&j='+(n.javaEnabled()?"1":"0")+
'&x='+s.width+
'&y='+s.height+
'&d='+(s.colorDepth?s.colorDepth:s.pixelDepth)+
'&js='+js+
'&o='+n.platform+'&'+Math.random()+'">');
</script>

Обратите внимание, что код на JavaScript состоит из двух блоков. В первом определяется версия JavaScript, а во втором идет код самого счетчика. Самым последним параметром передается случайное число, оно не используется, но необходимо, чтобы броузеры и прокси-сервера не кэшировали наш счетчик. Если бы этого случайного числа не было, то скрипт счетчика вызывался бы только один раз, при первом заходе пользователя, а в остальных случаях броузер или прокси-сервер выдавал бы прозрачный GIF из кэша.
Нас интересуют отчеты, которые показывают: количество уникальных посетителей сайта, общее количество запрошенных страниц, среднее количество страниц, запрашиваемых одним пользователем, самые популярные страницы нашего сайта, страницы, с которых пользователи приходят на наш сайт, динамика визитов, операционные системы и броузеры пользователей.
Сам скрипт counter, который вносит в таблицу hit новую запись об очередном открытии нашей страницы, очень прост. Но он отличается от CGI-скриптов, рассмотренных ранее тем, что выдает прозрачный GIF-файл размером 1х1 пиксел. Все счетчики устроены по такому принципу, т.к. единственная возможность вызвать CGI-скрипт одновременно с загрузкой HTML-страницы - это вызвать его в команде img.

/*
 * (c) Copyright 1995-2000, Igor Tarasov
 * http://itsoft.ru
 * FidoNet: 2:5020/370.2 620.20 1103.5
 * email: igor@itsoft.ru itarasov@rtuis.miem.edu.ru
 * Phone: (095)916-89-51 916-89-63
 */

#include <stdio.h>
#include <mysql/mysql.h>
#include <itcgi.h>



int main()
{

MYSQL* pDB;

LString* sref = CreateString();
LString* pref = CreateString();
LString* spage = CreateString();
LString* ppage = CreateString();

LString* id = CreateString();
LString* ref = CreateString();
LString* browser = CreateString();
LString* version = CreateString();
LString* cookie = CreateString();
LString* frame = CreateString();
LString* java = CreateString();
LString* x = CreateString();
LString* y = CreateString();
LString* depth = CreateString();
LString* js = CreateString();
LString* os = CreateString();
char str[4096];



pDB = mysql_init(NULL);
 
 if(!pDB)
  {
   printError(mysql_error(pDB));
   return -1;
  }
  
//получаем значения CGI-параметров,
//сформированных javascript'ом, приведенным выше  
GetParamByName("id", id);
GetParamByName("r", ref);
GetParamByName("n", browser);
GetParamByName("v", version);
GetParamByName("c", cookie);
GetParamByName("f", frame);
GetParamByName("j", java);
GetParamByName("x", x);
GetParamByName("y", y);
GetParamByName("d", depth);
GetParamByName("js", js);
GetParamByName("o", os);

//соединяемся с базой
if( !mysql_real_connect(pDB, NULL, "counter", "хххххх", "counter", 0, NULL, 0) )
 {
  printError("mysql_real_connect: %s\n", mysql_error(pDB));
  goto LABEL_END;
 }

//получаем адрес сайта, на который пришел пользователь
//эти адреса будут различаться в случае, если ваш счетчик 
//установлен на несколько ваших веб-сайтов
GetServer(getenv("HTTP_REFERER"), spage);
//получаем адрес страницы сайта, на которую пришел пользователь
GetPage(getenv("HTTP_REFERER"), ppage);

//получаем адрес сайта, с которого пришел пользователь
GetServer(*ref, sref);
//получаем адрес страницы сайта, с которой пришел пользователь
GetPage(*ref, pref);


//формируем строку SQL-запроса 
//обратите внимание, что мы используем функцию snprintf
//застраховавшись от переполнения буфера
 snprintf(str, 4096, "INSERT INTO hit ( owner_id, shref, href, browser, version, \
 cookie, frame, java, x, y, depth, js, os, ip, spage, page, it_date) VALUES (\
 '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', \
 '%s', '%s', '%s', '%s', NOW())",
 *id, *sref, *pref, *browser, *version, *cookie, *frame, *java, *x, *y, *depth, 
 *js, *os, getenv("REMOTE_ADDR"), *spage, *ppage);

//вставляем новую запись в таблицу hit
if( mysql_query(pDB, str) )
 {
  printError("mysql_query: SQL=%s<br> %s\n", str,  mysql_error(pDB));   
  goto LABEL_END;
 }


//выдаем прозрачный GIF размером 1х1 пиксел
printf("Content-type: image/gif\n\n");
fwrite("GIF89a
 
 ?  яяя   !щ
    ,    
 
 @D
 ;", 1, 43, stdout);

LABEL_END:
//закрываем соединение с базой
mysql_close(pDB);


//освобождаем память
DeleteString(sref);
DeleteString(pref);
DeleteString(spage);
DeleteString(ppage);

DeleteString(id);
DeleteString(ref);
DeleteString(browser);
DeleteString(version);
DeleteString(cookie);
DeleteString(frame);
DeleteString(java);
DeleteString(x);
DeleteString(y);
DeleteString(depth);
DeleteString(js);
DeleteString(os);

return 0;
}



******Makefile***********
all: counter 


counter: counter.c itcgi.a
        gcc counter.c -L/usr/local/lib/mysql \
-L/usr/local/lib -I/usr/local/include \
-o counter -lmysqlclient /usr/lib/itcgi.a -Wall -O3 
        strip counter 

Создайте базу данных counter, а в ней таблицу hit, приведенную выше. Соберите данный CGI-скрипт и разместите код счетчика на ваших страницах.
Далее мы напишем программу построения отчетов.

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#include <itcgi.h>



int main()
{
MYSQL* pDB;
MYSQL_RES* res;
MYSQL_ROW row;  

//переменные для хранения дня, за который строится отчет, и запроса
LString* day = CreateString();
LString* sql_query = CreateString();

pDB = mysql_init(NULL);

 if(!pDB)
 {
  printError("Внимание! Ошибка!!!", mysql_error(pDB));
  return -1;
 }



if( !mysql_real_connect(pDB, NULL, "counter", "xxxx", "counter", 0, NULL, 0) )
 {
  printError("Внимание! Ошибка!!!", "mysql_real_connect: %s\n", mysql_error(pDB));
  goto LABEL_END;
 }


//выдаем HTTP-заголовок
printf("Content-type: text/html; charset=windows-1251\n\n");

//получаем день
GetParamByName("day", day);
if(!strlen(*day))
{
 //если из CGI-параметров день не удалось получить,
 //берем текущую дату
 //CURDATE() вернет дату в виде 2002-01-08, т.е. 8 января 2002 года
 LString_Format(sql_query, "SELECT CURDATE()");
 mysql_query(pDB, *sql_query);
 res = mysql_store_result(pDB);  
 row = mysql_fetch_row(res);    
 LString_SetString(day, row[0]); 
 mysql_free_result(res);
}

//Создаем ссылки для навигации по дням.
//Этот код непосредственно к отчетам не имеет отношения,
//поэтому его комментировать не будем.
LString_Format(sql_query, 
"SELECT DATE_SUB('%s', INTERVAL 1 DAY), 
DATE_ADD('%s', INTERVAL 1 DAY)", *day, *day);
mysql_query(pDB, *sql_query);
res = mysql_store_result(pDB);  
row = mysql_fetch_row(res);
printf("<table border=0 width=95%%><tr><td align=left> \
<a href=/common/report?day=%s>Предыдущий день</a> \
<td align=center> %s
<td align=right><a href=/common/report?day=%s>Следующий день</a></table>",
row[0], *day, row[1]);
mysql_free_result(res);



//далее идут отчеты

//сначала печатаем заголовок
printf("<h4>Общее количество хостов и хитов</h4>"); 
//затем результат SQL-запроса
printTable(pDB, "SELECT COUNT(DISTINCT ip) as host, COUNT(*) as hit FROM hit");

printf("<h4>Динамика визитов по дням</h4>"); 
printTable(pDB, "SELECT count(distinct ip) as host, COUNT(*) as hit, \
DATE_FORMAT(it_date, '%Y-%m-%d') as date FROM hit \
GROUP BY date");

printf("<h4>Распределение хитов и хостов по часам</h4>"); 
LString_Format(sql_query, "SELECT count(distinct ip) as host, COUNT(*) as hit, \
DATE_FORMAT(it_date, '%%H') as hour FROM hit \
WHERE DATE_FORMAT(it_date, '%%Y-%%m-%%d')='%s' \
GROUP BY hour", *day);
printTable(pDB, *sql_query);



printf("<h4>Страницы - распределение хостов и хитов по страницам</h4>"); 
LString_Format(sql_query,
"SELECT  COUNT(DISTINCT ip) as host, COUNT(*) as hit, \
CONCAT(spage,page) as url FROM hit \
WHERE DATE_FORMAT(it_date, '%%Y-%%m-%%d')='%s' \
GROUP BY url ORDER BY host DESC, hit DESC LIMIT 0,20", *day);
printTable(pDB, *sql_query);

printf("<h4>Страницы - распределение по доменным именам</h4>"); 
LString_Format(sql_query, 
"SELECT  COUNT(DISTINCT ip) as host, COUNT(*) as hit, spage FROM hit \
WHERE DATE_FORMAT(it_date, '%%Y-%%m-%%d')='%s' \
GROUP BY spage ORDER BY host DESC, hit DESC  LIMIT 0,20", *day);
printTable(pDB, *sql_query);


printf("<h4>Ссылки - распределение по страницам</h4>"); 
LString_Format(sql_query, "SELECT  COUNT(*) as q, shref, href FROM hit \
WHERE DATE_FORMAT(it_date, '%%Y-%%m-%%d')='%s'   \
GROUP BY shref, href ORDER BY q DESC LIMIT 0,20", *day);
printTable(pDB, *sql_query);


printf("<h4>Ссылки - распределение по доменным именам</h4>"); 
LString_Format(sql_query, "SELECT  COUNT(*) as q, shref FROM hit \
WHERE DATE_FORMAT(it_date, '%%Y-%%m-%%d')='%s' \
GROUP BY shref ORDER BY q DESC LIMIT 0,20", *day);
printTable(pDB, *sql_query);


printf("<h4>Распределение хитов по хостам</h4>"); 
LString_Format(sql_query, "CREATE TEMPORARY TABLE IF NOT EXISTS hh \
SELECT COUNT(*) as hit, ip FROM hit \
WHERE DATE_FORMAT(it_date, '%%Y-%%m-%%d')='%s' \
GROUP BY ip ORDER BY hit DESC", *day);

mysql_query(pDB, *sql_query);
printTable(pDB, "SELECT * FROM hh LIMIT 0,20");

printf("<h4>Среднее количество страниц, открываемых одним пользователем</h4>"); 
printTable(pDB, "SELECT AVG(hit) FROM hh");

printf("<h4>Распределение по операционным системам </h4>"); 
printTable(pDB, "SELECT COUNT(*) as q, os FROM hit \
GROUP BY os ORDER BY q DESC");


printf("<h4>Распределение по броузерам</h4>"); 
printTable(pDB, "SELECT COUNT(*) as q, browser FROM hit \
GROUP BY browser ORDER BY q DESC");


printf("<h4>Распределение по версиям броузеров</h4>"); 
printTable(pDB, "SELECT COUNT(*) as q, browser, version FROM hit \
GROUP BY browser, version ORDER BY q DESC ");


printf("<h4>Распределение по разрешению экрана</h4>"); 
printTable(pDB, "SELECT COUNT(*) as q, CONCAT(x, 'x', y) as res FROM hit \
GROUP BY res ORDER BY q DESC ");


printf("<h4>Распределение по количеству цветов</h4>"); 
printTable(pDB, "SELECT COUNT(*) as q, depth FROM hit
  GROUP BY depth ORDER BY q DESC");

printf("<h4>Распределение по версиям javascript</h4>"); 
printTable(pDB, "SELECT COUNT(*), js FROM hit 
 GROUP BY js ORDER BY 1 DESC");


printf("<h4>Распределение по наличию java</h4>"); 
printTable(pDB, "SELECT count(*), java FROM hit 
GROUP BY java ORDER BY 1 DESC");


printf("<h4>Распределение по наличию cookie</h4>"); 
printTable(pDB, "SELECT count(*), cookie FROM hit 
 GROUP BY cookie ORDER BY 1 DESC");


/*
printf("<h4></h4>"); 
printTable(pDB, "");
*/


LABEL_END:
mysql_close(pDB);

DeleteString(day);
DeleteString(sql_query);
return 0;
}

=========Makefile==========
all: report


report: report.c itcgi.a
        gcc report.c -L/usr/local/lib/mysql \
-L/usr/local/lib -I/usr/local/include \
-o report -lmysqlclient /usr/lib/itcgi.a -Wall -O3 
        strip report

printf("<h4>Распределение хитов по хостам</h4>"); 
LString_Format(sql_query, "CREATE TEMPORARY TABLE IF NOT EXISTS hh \
SELECT COUNT(*) as hit, ip FROM hit \
WHERE DATE_FORMAT(it_date, '%%Y-%%m-%%d')='%s' \
GROUP BY ip ORDER BY hit DESC", *day);

mysql_query(pDB, *sql_query);
printTable(pDB, "SELECT * FROM hh LIMIT 0,20");

printf("<h4>Среднее количество страниц, открываемых одним пользователем</h4>"); 
printTable(pDB, "SELECT AVG(hit) FROM hh");

Во-первых, обратите внимание на то, как формируется строка SQL-запроса. При использовании LString_Format вы застрахованы от переполнения буфера и имеете возможность формировать строку с заранее неизвестным размером. Во-вторых, обратите внимание на сам SQL-запрос. Мы сначала создаем временную таблицу hh, а затем выполняем для нее два SQL-запроса. Необходимость использовать временную таблицу вызвана тем, что получить одним запросом среднее количество страниц, открываемых одним пользователем, невозможно. Мы сначала должны посчитать сколько страниц открыл каждый пользователь, а затем посчитать среднее арифметическое. Временная таблица уничтожается автоматически, после завершения соединения с базой либо же после явного вызова DROP TABLE. Помните об этом!

Результат работы CGI-скрипта report за 7 января 2002 года представлен ниже.

Предыдущий день

2002-01-07

Следующий день

Общее количество хостов и хитов

Динамика визитов по дням

Распределение хитов и хостов по часам

Страницы - распределение хостов и хитов по страницам

Страницы - распределение по доменным именам

Ссылки - распределение по страницам

Ссылки - распределение по доменным именам

Распределение хитов по хостам

Среднее количество страниц, открываемых одним пользователем

Распределение по операционным системам

Распределение по броузерам

Распределение по версиям броузеров

Распределение по разрешению экрана

Распределение по количеству цветов

Распределение по версиям javascript

Распределение по наличию java

Распределение по наличию cookie