Статистика посещений WEB-сайта в формате SQL

Сразу после создания сайта встает вопрос – а кто-нибудь еще смотрит на него или автор является единственным зрителем и читателем?
Конечно, простейший ответ на вопрос даст практически любой провайдер – например, включить в главную страницу счетчик посещений в текстовом или даже графическом виде. Казалось бы все хорошо, но остаются проблемы:
— Счетчик считает только посещения главной страницы, или тех на которых указана ссылка на этот счетчик.
— Счетчик не отделяет посещения различных пользователей. Это может быть один пользователь зашел на сайт 20 раз или 20 различных пользователей по одному разу.
— Счетчик никогда не ответит на вопрос – когда и, главное, почему пользователь зашел на сайт.
Конечно, продвинутые провайдеры предлагают различные варианты статистики, основанные на данных сервера, и часто показываемая статистика посещений выглядит весьма презентабельно и дает достаточно много информации. Для примера, можно посмотреть статистику посещений, которую дает DateForce ISP — для этого достаточно кликнуть на счетчике на главной странице нашего сайта.
Но форма этой статистики посещений определяется провайдером и это его право. А хотелось бы иметь что-то более продробное и управляемое. Забегая вперед, можно сказать, что эксплуатация скрипта StatSQL.cgi в течении нескольких месяцев показала, что все виды статистики не отражают реальности посещений, а счетчики, какие они красивые ни были, вообще бесполезны.
Итак мы хотим иметь:
— когда, кто и на какую страницу сайта зашел,
— что он искал и что нашел,
— и все это должно быть в текстовом виде и легко читаться без применения дополнительных средств,
— но, при этом, без дополнительных преобразований все это должно засовываться в какую-нибудь базу данных.
Именно этим целям служит этот CGI-script.
Во-первых, нужно определиться с форматом. Есть стандартный формат обмена данными CSV (с разделителями), но вставить такой файл даже в Excel проблема – приходиться потом подстраивать ширину колонок, форматы ячеек и прочее. С другой стороны, при добавлении столбцов в таблице приходится подправлять скрипт, что не всегда правильно.
Идеально для данной задачи подходит формат SQL-скрипта. Т.е. наша статистика должна оформляться как набор SQL-команд типа "Insert into TABLE() values();", который затем считывается в любую СУБД клиент/серверного типа стандартными средствами.
Во-вторых, нужно определится какую информацию передавать администратору сайта.
Здесь много думать не приходиться – есть переменные окружения, которые передаются в CGI-скрипт, просто далеко не все они нужны.
И, в-третьих, нужно определить, как отделить одного посетителя от другого.
Известный способ – определять ip-адрес посетителя, работает плохо. У провайдера может просто не хватить постоянных адресов на всех пользователей, поэтому адреса как правило выделяются динамически. Следовательно с одним и тем же IP-адресом может оказаться несколько пользователей. Одно дело пропустить посещение пользователя (точнее спутать его с посещением другого пользователя), другое дело при голосовании запретить голосовать еще 10 пользователям с таким же IP-адресом. Результаты такого голосования ничего не отражают.
В данном случае было принято решение генерировать случайное число и записывать его в HTTP_Cookie файл браузера при первом посещении и считывать его в дальнейшем для указания в протоколе. Спецификация этого файла приведена по адресу http://www.netscape.com/newsref/std/cookie_spec.html Конечно в таком случае мы теряем пользователей, которые не разрешили браузеру использовать cookie. В таком случае они переходят в категорию "неопределенные", также как и роботы поисковых систем.
Скрипт, который собирает протокол посещений приведен ниже:

001 #!/usr/bin/perl
002 ######################################################################
003 #     Copyright AZ Design Corp. 2001                                 #
004 #     Все права зашищены(2001).                                      #
005 #     Статистика в SQL v.1.0                                         #
006 #     По всем вопросам - Архангельский Андрей                        #
007 #     email: azdesign@az-design.ru                                   #
008 #     url: http://www.az-design.ru/                                  #
009 # Config = interval:left:report:oldint:oldreprt"                     #
010 ######################################################################
011 use CGI qw(:all);
012 use Socket;
013 @days = ('Sun','Mon','Tue','Wed','Thu','Fri','Sat');
014 @months = ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
015 ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime();
016 $year = $year+1900;  $mon  = $mon+1;
017 $date = "'$mon/$mday/$year $hour:$min:$sec'";

Строка 001 всегда должна содержать полный путь к интерпретатору Perl, если это не так - проконсультируйтесь у своего провайдера. Вообще для правильной работы любого cgi-скрипта требуется выполнение некоторых мелочей, которые определяет провайдер. Поэтому не стесняйтесь обращаться к нему.
Строки 013-017 формируют значения переменных текущей даты и времени. Переменная $date в строке 017 в результате содержит текущую дату и время в формате необходимом для Interbase.

019 #
020 $mail="/usr/bin/sendmail";            # путь к программе для отправки почты
021 $emailto="aga\@az-design.ru";         # адрес куда отправлять отчет
022 $bccmail="aga\@df.ru";                # еще один адрес куда отправлять отчет
023 $sendermail="azdesign\@az-design.ru"; # от имени кого отправлять отчет
024 $errormail="aga\@df.ru";              # адрес куда отправлять в случае ошибки
025 $conf = "conf.txt";                   # имя файла конфигурации
026 $logfile = "log.txt";                 # имя файла протокола
027 $oldlog  = "oldlog.txt";              # имя запасного файла протокола
028 $baklog  = "baklog.txt";              # имя 2 запасного файла протокола

Строки 020-028 задают константы почтовых адресов и имена необходимых файлов. По умолчанию файлы протоколов находяться в том же каталоге, где и скрипт. Второй почтовый адрес ($bccmail) необходим в случае если вы поддерживаете несколько сайтов. В этом случае протокол посылается владельцу сайта, а копия администратору. Структура протокола, а также, планируемая структура базы данных, позволяет не только анализировать несколько сайтов в одной базе данных, но и отслеживать переходы между родственными сайтами. Естественно в качестве почтового адреса для отправки отчета нужно указать адрес своего администратора. Здесь и далее жирным шрифтом выделены значения, которы необходимо заменить на свои.

029 #
030 if ($ENV{'HTTP_COOKIE'}=~m'AZDesignID=') {
031     foreach $i (split(/;/,$ENV{'HTTP_COOKIE'})) {
032         ($Name,$Val) = split(/=/,$i);
033         if ($Name=~'AZDesignID') {$AZUserID = $Val;}
034     } 
035 }

Строки 030-035 вытаскивают из файла HTTP_COOKIE значение cookie с именем AZDesignID если оно есть и присваивает его переменной $AZUserID. Каждый сайт должен задавать свое имя cookie. Соответственно здесь нужно имя cookie AZDesignID и имя переменной $AZUserID заменить на то, которое подходит для конкретного сайта.

036 #
037 use integer;
038 #
039 #
040 $DocFile = "$ENV{'DOCUMENT_ROOT'}"."$ENV{'DOCUMENT_URI'}";
041 ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$lmtime,$ctime,$blksize,$block) = stat($DocFile);
042 $DFsize = $size;
043 
044 ($LMsec,$LMmin,$LMhour,$LMmday,$LMmon,$LMyear,$LMwday,$LMyday,$LMisdst)=localtime($lmtime);
045 $LMyear = $LMyear+1900;  $LMmon  = $LMmon+1;
046 $LMDate = "'$LMmon/$LMmday/$LMyear $LMhour:$LMmin:$LMsec'";

Строки 040-046 получают информацию о файле. В переменную $DocFile записывается полный путь файла, затем формируется массив характеристик файла, из которого используются только размер ($size) и дата последней модификации ($lmtime). Время последнего доступа ($atime) использовать бессмысленно, так как оно равно текущему иначе бы скрипт не запустился. Значение даты также переводиться в формат необходимый для Interbase.

047 #
048 require '../lib/ip_integ.pl';
049 require '../lib/addrhost.pl';
050 $remote_ip   = &ip_integer($ENV{'REMOTE_ADDR'});
051 $remote_host = gethostbyaddr (inet_aton($ENV{REMOTE_ADDR}), AF_INET);
052 $remote_host = "could not resolve" if $remote_host eq '';

Строки 048-050 получают IP-адрес посетителя и переводят его значение в целочисленный 32-разрядный эквивалент, для того чтобы его можно было использовать в качестве ключевого поля в таблице REM_HOST в дальнейшем.
Строки 051-052 получают имя удаленного хоста и если это невозможно выдают надпись "could not resolve". Возможность определить имя хоста, не зависит от нас или нашего провайдера, поэтому берем что есть.

053 #
054 $HeadRecord = "$HeadRecord"."Insert into VizitLog(";
055 $LogRecord = "$LogRecord"." values(";

Начало генерации команды "Insert into…". Так как команда состоит из двух частей – описания полей и описания значений, то стоить команду проще всего на двух текстовых переменных - $HeadRecord и $LogRecord. В первую записывается "Insert into VizitLog(", где VizitLog имя таблицы протокола. Во вторую – "values (" – т.е. начало перечисления значений. Следующие строки попарно добавляют к указанным переменным "имя поля" и его значение соответственно, включая разделяющую запятую в конце.

056 #
057 $HeadRecord = "$HeadRecord"."VizDate,";
058 $LogRecord = "$LogRecord"."$date,";       # текущая дата и время
059 #
060 $HeadRecord = "$HeadRecord"."RemoteIP,";
061 $LogRecord = "$LogRecord"."$remote_ip,";  # удаленный адрес в формате Integer
062 #
063 if ($AZUserID) {
064 $HeadRecord = "$HeadRecord"."UserID,";
065 $LogRecord = "$LogRecord"."$AZUserID,\n";  # UserID из HTTP_COOKIE"
066 }

Если имя переменной $AZUserID было изменено выше, то здесь также нужно это сделать.

067 #
068 $HeadRecord = "$HeadRecord"."RemoteAddr,";
069 $LogRecord = "$LogRecord"."'$ENV{'REMOTE_ADDR'}',";  # удаленный адрес в формате xxx.xxx.xxx.xxx
070 #
072 $HeadRecord = "$HeadRecord"."RemoteHost,";
073 $LogRecord = "$LogRecord"."'$remote_host',\n";  # удаленный адрес в формате "user.domen.com"
075 #
076 $HeadRecord = "$HeadRecord"."http_via,";
077 $LogRecord = "$LogRecord"."'$ENV{'HTTP_VIA'}',\n"; # адрес proxy-сервера
078 #
079 $HeadRecord = "$HeadRecord"."http_agent,";
080 $LogRecord  = "$LogRecord"."'$ENV{'HTTP_USER_AGENT'}',"; # название браузера пользователя
081 #
082 $HeadRecord = "$HeadRecord"."http_lang,\n";
083 $LogRecord  = "$LogRecord"."'$ENV{'HTTP_ACCEPT_LANGUAGE'}',\n"; #Языки, поддерживаемые пользователем
084 #
085 $HeadRecord = "$HeadRecord"."http_host,";
086 $LogRecord  = "$LogRecord"."'$ENV{'HTTP_HOST'}',\n";    # адрес сайта
087 #
088 $HeadRecord = "$HeadRecord"."http_ref,";
089 $LogRecord  = "$LogRecord"."'$ENV{'HTTP_REFERER'}',\n"; # ссылка – откуда пришли
090 # Document
091 $HeadRecord = "$HeadRecord"."http_page,";
092 $LogRecord  = "$LogRecord"."'http:\/\/$ENV{'SERVER_NAME'}"."$ENV{'DOCUMENT_URI'}',\n";
093 #
094 $HeadRecord = "$HeadRecord"."doc_uri,";
095 $LogRecord  = "$LogRecord"."'$ENV{'DOCUMENT_URI'}',";
096 #
097 $HeadRecord = "$HeadRecord"."doc_name,";
098 $LogRecord  = "$LogRecord"."'$ENV{'DOCUMENT_NAME'}',";
099 #
100 $HeadRecord = "$HeadRecord"."doc_size,";
101 $LogRecord  = "$LogRecord"."$DFsize,\n";
102 #
103 $HeadRecord = "$HeadRecord"."doc_lmdate)";
104 $LogRecord  = "$LogRecord"."$LMDate);\n";
105 #

Строка 086 – "HTTP_HOST" дает адрес сайта, на котором лежит документ.
Строка 089 – ссылку откуда посетитель пришел на текущий документ. Если посетитель пришел на страницу в результате поиска на какой-либо поисковой системе, то в этой строке указывается полный путь запроса в поисковую систему. Т.е. теперь известно "что искал" посетитель. Результаты очень неожиданные. Постоянно анализируя эти значения можно управлять информацией на сайте для привлечения необходимой аудитории.
Строка 092 – дает полный путь текущего документа, строка 095 – его путь на сервере, строка 098 – только его имя. Некоторая избыточность этой информации введена для облегчения дальнейшей обработки информации. Строки 101 и 104 дают размер документа и дату последней модификации.

108 
109 open(LOG,">>$logfile") || die "$!\n";
110   flock LOG,2;
111   print LOG "$HeadRecord\n";
112   print LOG "$LogRecord\n\n";
113 close(LOG);

Строки 109-113 дописывают получившуюся команду в файл протокола. Файл сразу закрывается, чтобы другой посетитель мог записать в него свою информацию.

114 #
115 open CONF,"$conf";  $a=<CONF>;  close CONF;
116 chomp $a; ($interval,$left,$report,$oldint,$oldreprt)=split ":",$a;
117 $left=$left-1;

Строки 115-117 определяют нужно ли отправлять письмо с протоколом. Частота отправления определяется переменной $interval в файле конфигурации (первое число в строке), которая задает количество записей в файле протокола. Оно выбирается таким, чтобы письма отправлялись в среднем раз в день. Если пошел поток больше одного письма в день, то это значение соответвенно увеличивается.

118 if ($left<0){
119    $report=$report+1;
120    $oldreprt=$oldreprt-1;
121    $left=$interval;
122    open L,"$logfile";   @logline=<L>;  close L;

Если нужно отправить файл протокола, то устанавливается новый номер протокола и файл считывается в массив @logfile

123 
124    open (MAIL, "|$mail $email");
125    print MAIL "From: $sendermail\n";
126    print MAIL "To: $emailto\n";
127    print MAIL "Bcc: $bccmail\n";
128    print MAIL "Reply-To: $sendermail\n";
129    print MAIL "Return-Path: $errormail\n";
130    print MAIL "Subject: Log-files of your site [www.az-design.ru] #$report\n\n";
131    print MAIL "This is access log of your site [www.az-design.ru] #$report.\n";
132    print MAIL "---*/\n";

Строки 124-132 формируют заголовок письма, причем строки 131-132 – начало тела письма. Символы в тексте письма "/* */" определяют текст комментария для СУБД Interbase. Естественно, что www.az-design.ru в Subject и теле письма нужно заменить на имя конкретного сайта, хотя бы ради удобства, но на работу скрипта это не влияет.

133    open BCLOG,">>$oldlog";
134    print BCLOG "--sended_as_part_$report\n";
135    foreach $itm(@logline)
136       {
137       chomp $itm;
138       print MAIL "$itm\n";
139       print BCLOG "$itm\n";
140       };
141    close BCLOG;

Строки 133-141 переписывают файл протокола в тело письма и одновременно в запасной файл протокола, после чего запасной файл протокола закрывается.

142    print MAIL "/*--- End of report #$report.\n\n";
143    print MAIL "Generated By Log Script\n(c)AZ Design Corp, 2001\n---*/\ncommit;\n\n";
144    close (MAIL);

Строки 142-143 завершают текст письма сопроводительной инофрмацией и SQL-командой commit;

145    open L,">$logfile";   close L;
146    if ($oldreprt<0) {
147       $oldreprt = $oldint;
148       open L,"$oldlog";   @logline=<L>;  close L;
149       open BCLOG,">$baklog";
150       foreach $itm(@logline)
151          {
152          chomp $itm;
153          print BCLOG "$itm\n";
154          };
155       close BGLOG;
156       open L,">$oldlog";   close L;
157    }

Строки 145-156 создают при необходимости второй запасной файл протокола. Делов том что при одном запасном файле возникает ситуация когда сброшены и текущий и запасаной файл протокола. Здесь возможны более красивые варианты, которые будут реализованы позднее (возможно).

158 }
159 open CONF,">$conf"; print CONF "$interval:$left:$report:$oldint:$oldreprt"; close CONF;

Строка 159 вносит изменения в файл конфигурации.
Файл конфигурации состоит из 5 целых чисел, разделенных символом ":"
$interval – число записей в файле протокола
$left – числе записей оставшихся до конца файла протокола (изменяет StatSQL.cgi)
$report – номер отправленного протокола (изменяет StatSQL.cgi)
$oldint – Число запасных файлов протокола
$oldreprt – Число оставшихся до конца файлов протокола.
Для правильной работы скрипта необходимо установить права доступа к каталогу 700 и к файлам конфигурации и протоколов 600, в любом случае по этому поводу проконсультируйтесь у своего провайдера.
Для вызова скрипта вставьте в каждую страницу своего сайта следующую строку:

<!--#exec cgi="cgi/stat/statSQL.cgi"--><br>

Хорошее правило для каждого скрипта выделять отдельный каталог, в данном случае /stat/, если решите иначе - измените путь к скрипту.
Для получения SQL-script'а необходимо сохранить полученное письмо в формате .txt (установлено по умолчанию) и в полученном файле в самое начало добавить знак начала комментария "/*". Переименовав файл в тип .sql вы сможете ввести его в СУБД.

Пример получившегося SQL-script'а приведен ниже:

В этом протоколе сразу видны и достоинства и недостатки. Если не по имени хоста, то по имени proxy-сервера можно определить откуда пришел пользователь, например, 1.0 prefuvao.uvao.mos.ru:8080 (Squid/2.3.STABLE4) - префектура юго-восточного административного округа г.Москвы.
Легко видеть как работает поисковая система - 'FAST-WebCrawler/3.3 (crawler@fast.no; http://fast.no/support.php?c=faqs/crawler)' - в таких случаях поле http_ref имеет пустое значение.
Недостаток - запрос к поисковой системе передается в виде кодированных чисел. Код простой и можно с помощью блокнота заменить числа на символы, но неприятность заключается в том, что каждая поисковая система использует свою кодировку. Поэтому лучше написать небольшую программу, которая выполнит эту работу. В ближайщем будущем это будет сделано.

О том, как построить базу данных и работать с полученной информации будет рассказано в следущей статье.

Продолжение следует