Создаем статистику для сайта своими руками

Введение

Практически у каждого владельца сайта в какой-то момент возникает желание узнать какие-то статистические данные о своих посетителях. Сколько посетителей заглядывает на сайт, как долго они находятся на сайте, какие страницы смотрят, откуда приходят и т.д. И, в принципе, для получения подобной информации хватает разнообразных бесплатных (и не очень) сервисов типа top.mail.ru или www.spylog.com, а так же парсеров логов веб серверов. Но все эти службы и сервисы имеют немало недостатков – например потеря информации из-за недоступности онлайн службы или блокировки пользователем изображений в своем браузере или же избыточность лог файлов веб сервера и невозможность более-менее однозначно с помощью парсинга лог файлов определить сессию пользователя. А кроме того они совершенно неспособны дать ответы на какие-то специфические вопросы, например, на вопрос «показать список пользователей, пришедших с поисковых систем и зарегистрировавшихся в сессию прихода». Или же «показать все сессии конкретного пользователя за весь период времени». Что приводит постепенно к мысли о создании собственной системы логирования и набора отчетов для получения нужной статистики.

В какой-то момент встала подобная задача и передо мной. После серьезного редизайна сайта заказчик потребовал статистику, связанную с учетом посетителей с поисковых систем в разрезе их регистраций на сайте и совершения, допустим, покупок. А, кроме того, заказчика очень интересовали моменты как хорошо и быстро поисковые системы пройдутся по сайту и положат его страницы в свой индекс. Соответственно пришлось мне заняться созданием системы логирования посещений сайта.

База данных

На самом деле сам по себе процесс логирования захода посетителя на сайт ничего сложного из себя не представляет. Есть страницы (таблица Page), идентифицируемые их виртуальным адресом и сайтом, есть просмотры пользователями этих страниц (таблица Request) и есть сессии пользователей (совокупность просмотренных пользователем страниц сайта). Соответственно для сохранения всех этих данных у меня получилась вот такая база:

Небольшое пояснение – как я раньше уже упоминал в списке отчетов по посетителям у меня немаловажную роль играли зарегистрированные пользователи. Что и побудило меня добавить столбец UserID, куда будет записываться имя пользователя если логируется аутентифицированный запрос. И хотя я, например, предпочитаю использовать для идентификации пользователя целое число (identity ключ соотв. таблицы базы) в таблице Session это поле имеет тип varchar для максимальной совместимости. Кроме того в сессии сохраняется информация о первой и последней страницах сессии, а так же о дате начала и окончания сессии для удобства построения отчетов по точкам входа/выхода и времени. Ну а с каждым запросом страницы я сохраняю информацию о том, аутентифицирован ли пользователь и является ли этот запрос постбеком.

Для записи запосов в БД используются две не самые сложные процедуры:

  create procedure p_SessionStart 
     @UserID           int = null, 
     @IPAddress        varchar(15), 
     @BrowserString    varchar(1024), 
     @ReferralURL      varchar(4096) = null, 
     @Site                varchar(100) = null, 
     @PageUrl          varchar(255) 
  as 
  begin tran 
  declare @PageID int 
  select @PageID = PageID from Page where PageURL = @PageUrl and (@Site is null and Site = @Site or Site is null) 
  if @PageID is null 
  begin 
     insert into Page (PageURL, Site) values (@PageUrl, @Site) 
     select @PageID = @@IDENTITY 
  end 
  insert into Session (UserID, DateStart, FirstPageID, LastPageID, DateEnd, IPAddress, BrowserString, ReferralURL) 
     values (@UserID, getDate(), @PageID, @PageID, getDate(), @IPAddress, @BrowserString, @ReferralURL) 
  commit 
  return @@identity 
  go 
  create procedure p_DoRequest 
     @SessionID  int, 
     @UserID     varchar(100) = null, 
     @Site          varchar(100) = null, 
     @PageUrl    varchar(255), 
     @QueryString   varchar(1024), 
     @IsPostBack    bit, 
     @IsAuthenticated     bit 
  as 
  begin tran 
  declare @PageID int 
  select @PageID = PageID from Page where PageURL = @PageUrl and (@Site is null and Site = @Site or Site is null) 
  if @PageID is null 
  begin 
     insert into Page (PageURL, Site) values (@PageUrl, @Site) 
     select @PageID = @@IDENTITY 
  end 
  insert into Request (PageID, SessionID, RequestDate, QueryString, IsPostBack, IsAuthenticated) 
     values (@PageID, @SessionID, getDate(), @QueryString, @IsPostBack, @IsAuthenticated) 
  update  
     Session 
  set 
     LastPageID = @PageID,  
     DateEnd = getdate() 
  where  
     SessionID = @SessionID 
  if @UserID is not null 
     update  
        Session 
     set 
        UserID = @UserID 
     where  
        SessionID = @SessionID 
  commit 
  go 
  

Процедура p_SessionStart, как мне кажется, не требует никаких дополнительных пояснений, а для процедуры p_DoRequest сделаю короткое замечание, что кроме логирования самого запроса к странице она так же изменяет некоторые значения сессии.

На этом пока что работа по созданию базы данных завершена и можно переходить к написанию программного кода для логирования заходов пользователей на сайт.

Модуль логирования

Программную часть системы логирования посещений сайтов я решил сделать в виде HTTP модуля. Предпосылки очевидны – система логирования должна работать с сайтами без изменения их кода, просто настраиваться и обрабатывать все asp.net запросы. Опять таки код этого модуля прост до безобразия и в сумме занимает едва за сотню строк кода.

Так как вся настройка работы модуля будет вестись с помощью конфигурационного файла, то самым оптимальным решением для этого будет создание своей секции в конфигурационного файла для модуля. Модулю для своей работы нужна строка подключения к БД, имя сайта (необязательно) и место хранения идентификатора сессии пользователя (asp.net сессия или куки, по умолчанию куки). Соответственно код структуры параметров модуля:

  public enum PersistIDPlace 
  { 
        Session, 
        Cookie 
  } 
  public class SiteStatsSettings 
  { 
        public string ConnectionString; 
        public string Site; 
        public PersistIDPlace SessionIDPlace; 
  } 
  

Сама же секция конфигурационного файла суть класс, реализующий интерфейс System.Configuration.IConfigurationSectionHandler. Этот интерфейс содержит единственный метод Create, который должен вернуть объект. Я не буду долго рассказывать что и как здесь необходимо сделать (это можно прочитать и в MSDN) и просто приведу код класса:

  public class SiteStatsConfigHandler : IConfigurationSectionHandler 
  { 
        public SiteStatsConfigHandler(){} 
        public object Create(object parent, object configContext, System.Xml.XmlNode section) 
        { 
              SiteStatsSettings ret = new SiteStatsSettings(); 
              ret.ConnectionString = section.SelectSingleNode("ConnectionString").InnerText; 
              ret.Site = section.SelectSingleNode("Site") != null ? section.SelectSingleNode("Site").InnerText : ""; 
              if(section.SelectSingleNode("SessionIDPlace") != null) 
                    ret.SessionIDPlace = (PersistIDPlace) Enum.Parse(typeof(PersistIDPlace), section.SelectSingleNode("SessionIDPlace").InnerText); 
              else 
                    ret.SessionIDPlace = PersistIDPlace.Cookie; 
              return ret; 
        } 
  } 
  

Как видите ничего сложного в вышеприведенном коде нет – он всего лишь читает параметры из XmlNode и заполняет класс параметров модуля. Для того, чтобы добавить созданную выше секцию в конфигурационном файле в секции <configuration> теперь достаточно добавить вот такие строки:

  <configSections> 
  <section name="siteStats" type="SiteStats.SiteStatsConfigHandler, SiteStats"/> 
  </configSections> 
  И теперь можно использовать секцию <siteStats> для задания параметров модуля: 
  <siteStats> 
        <ConnectionString>server=localhost;uid=sa;pwd=;database=SiteStats</ConnectionString> 
        <Site>Mania</Site> 
        <SessionIDPlace>Cookie</ SessionIDPlace> 
  </siteStats> 
  

А получить эти параметры в коде класса можно с помощью следующей строки кода:

  SiteStatsSettings settings = (SiteStatsSettings) ConfigurationSettings.GetConfig("siteStats");
  

Теперь осталось всего ничего – реализовать класс для записи в БД и собственно сам HTTP модуль. Код класса для работы с БД настолько банален, что их можно привести даже без комментариев:

  public class SiteStatsBLL 
  { 
        private static int SessionStart(HttpContext context) 
        { 
              SiteStatsSettings settings = (SiteStatsSettings) ConfigurationSettings.GetConfig("siteStats"); 
              SqlConnection myConn = new SqlConnection(settings.ConnectionString); 
              SqlCommand myCmd = new SqlCommand("p_SessionStart", myConn); 
              myCmd.CommandType = CommandType.StoredProcedure; 
              if (context.User.Identity.IsAuthenticated) 
                    myCmd.Parameters.Add("@UserID", Int32.Parse(context.User.Identity.Name)); 
              myCmd.Parameters.Add("@IPAddress", context.Request.UserHostAddress); 
              myCmd.Parameters.Add("@BrowserString", context.Request.UserAgent == null ? "" : context.Request.UserAgent); 
              if (context.Request.UrlReferrer != null) 
                    myCmd.Parameters.Add("@ReferralURL", context.Server.UrlDecode(context.Request.UrlReferrer.ToString())); 
              if (settings.Site != "") 
                    myCmd.Parameters.Add("@Site", settings.Site); 
              myCmd.Parameters.Add("@PageURL", context.Request.FilePath); 
              myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int); 
              myCmd.Parameters["RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; 
              myConn.Open(); 
              myCmd.ExecuteNonQuery(); 
              myConn.Close(); 
              return (int) myCmd.Parameters["RETURN_VALUE"].Value; 
        } 
        public static void Request(HttpContext context) 
        { 
              SiteStatsSettings settings = (SiteStatsSettings) ConfigurationSettings.GetConfig("siteStats"); 
              int SessionID; 
              switch(settings.SessionIDPlace) 
              { 
                    case PersistIDPlace.Session: 
                    if (context.Session["SessionID"] != null) 
                          SessionID = (int) context.Session["SessionID"]; 
                    else 
                    { 
                          SessionID = SessionStart(context); 
                          context.Session["SessionID"] = SessionID; 
                    } 
                          break; 
                    default: 
                          if (context.Request.Cookies["SessionID"] != null) 
                               SessionID = Int32.Parse(context.Request.Cookies["SessionID"].Value); 
                          else 
                          { 
                               SessionID = SessionStart(context); 
                               context.Response.Cookies.Add(new HttpCookie("SessionID", SessionID.ToString())); 
                          } 
                          break; 
              } 
              SqlConnection myConn = new SqlConnection(settings.ConnectionString); 
              SqlCommand myCmd = new SqlCommand("p_DoRequest", myConn); 
              myCmd.CommandType = CommandType.StoredProcedure; 
              myCmd.Parameters.Add("@SessionID", SessionID); 
              if (context.User.Identity.IsAuthenticated) 
                    myCmd.Parameters.Add("@UserID", context.User.Identity.Name); 
              if (settings.Site != "") 
                    myCmd.Parameters.Add("@Site", settings.Site); 
              myCmd.Parameters.Add("@PageURL", context.Request.RawUrl.IndexOf("?") != -1 ? context.Request.RawUrl.Substring(0, context.Request.RawUrl.IndexOf("?")) : context.Request.RawUrl); 
              myCmd.Parameters.Add("@QueryString", context.Request.QueryString.ToString()); 
              myCmd.Parameters.Add("@IsAuthenticated", context.Request.IsAuthenticated); 
              myCmd.Parameters.Add("@IsPostBack", context.Request.HttpMethod == "POST"); 
              myConn.Open(); 
              myCmd.ExecuteNonQuery(); 
              myConn.Close(); 
        } 
  } 
  

Для логирования запроса пользователя используется метод SiteStatsBLL.Request(), который в свою очередь при необходимости вызывает метод SiteStatsBLL.SessionStart() для старта новой сессии. Оба метода всего лишь вызывают соответствующие хранимые процедуры.

Метод SiteStatsBLL.Request() я вызываю в обработчике события HttpApplication. PostRequestHandlerExecute. В момент срабатывания этого события обработка стнаницы завершена и страница готова к отправке клиенту, но сессия еще сущетвует (у нас же есть опция сохранения SessionID в сессии). И вся задача HTTP модуля состоит в том, чтобы при наступлении этого события вызвать метод логирования запроса:

  public class SiteStatsModule : IHttpModule 
  { 
        void IHttpModule.Init(HttpApplication context) 
        { 
              context.PostRequestHandlerExecute += new EventHandler(context_PostRequestHandlerExecute); 
        } 
        void IHttpModule.Dispose() 
        { 
        } 
        void context_PostRequestHandlerExecute(object sender, EventArgs e) 
        { 
              SiteStatsBLL.Request(HttpContext.Current); 
        } 
  } 
  

Все... процесс создания модуля логирования запросов к сайту завершен. Теперь для того, чтобы начать вести логи для какого-то сайта достаточно переписать получившуюся сборку в подкаталог bin этого сайта и внести в файл web.config следуюшие изменения.

1. Добавить описание секции в раздел <configuration>:
 

  <configSections>
  	<section name="siteStats" type="SiteStats.SiteStatsConfigHandler, SiteStats"/>
  </configSections>
  

2. В тот же раздел добавить саму секцию siteStats:

  <siteStats> 
        <ConnectionString>строка подключения к БД</ConnectionString> 
        <Site>имя сайта</Site> 
        <SessionIDPlace>Место хранения идентификатора сессии (Cookie или Session)</SessionIDPlace> 
  </siteStats> 
  

3. В секцию <system.web> добавить описание модуля:

  <httpModules> 
        <add name="SiteStatsModule" type="SiteStats.SiteStatsModule, SiteStats"/> 
  </httpModules> 
  

Работа сделана и теперь все asp.net запросы к сайту будут логироваться. Но прежде, чем я закончу эту статью, я хотел бы еще рассказать о некоторых дополнительных манипуляциях над сохраняемыми данными.

Поисковые роботы и реферралы

Написанный выше модуль в отличие от бесплатных систем интернет статистики логирует любые заходы на страницу – как заходы обычных пользователей, так и заходы всяких поисковых роботов или систем автоматического сохранения сайтов. И естесственно если не делать дополнительной фильтрации для выявления ненужных заходов, то можно получить цифры, которые будут весьма и весьма далеки от настоящих. Например для сайта aspnetmania.com 1 декабря 2005 года количество залогированных модулем посетителей (сессий) было порядка 26 тысяч, при этом статистика на top.mail.ru показывает 1545 посетителей. Остальные запросы – активная работа поисковых роботов :). И хочешь – не хочешь, но их нужно будет каким-то образом для себя отмечать (или вообще убирать в случае, если статистика по заходам роботов не интересна). Для этого во первых нужно сделать поддержку списка исключаемых юзерагентов ну и, во вторых, добавить статусное поле в таблицу Session.

Дабы не утруждать читателя долгими рассуждениями о том, какие существуют роботы и какой робот кому служит я приведу сразу небольшой список масок учитываемых мной юзерагентов роботов:

Последняя строка в этом перечне конечно довольно таки радикальная, но этих роботов столько человеческая мысль наплодила – благо, что хоть они зачастую признаются честно, что они боты :).

Кроме обработки «непользовательских» заходов нужно также сделать обработку реферралов – с какого сайта/поисковой системы пришел посетитель и какие поисковые слова он использовал в случае прихода с поисковой системы. И тут опять не обойтись без дополнительных таблиц – списка поисковых систем и для списка реферральных сайтов. Ну а кроме того необходима таблица для хранения поисковых фраз. И, естесственно, все эти таблицы будут связаны с таблицей Session.

Таблицы для сайтов и поисковых фраз ничего из себя особенного не представляют – это обычные справочники, в которые по необходимости будут добавляться новые значения.

Более интересна таблица поисковых систем – в ней кроме названия поисковой системы понадобится также сохранять информацию о маске имени сайта, а так же информацию о том, где в строке реферрала хранится поисковая фраза. Обычно поисковая фраза содержится в каком-то конкретном параметре URL, но бывают и особо "продвинутые" индивидуумы, засовывающие ее в структуру каталогов URL. Но и  на их фоне выделается AOL,  в случае поиска с главной страницы передающий поисковую фразу в строке запроса в зашифрованном виде. Благо хоть в другом параметре, что позволяет упростить учет поисковых фраз.

Ниже представлен примерный справочник поисковых сайтов для создаваемой системы статистики.

Ввиду того, что основная масса «неправильных» поисковиков редко используется даже в США (откуда они все родом) и приход на русскоязычный сайт с этого поисковика стремится к нулю, я поленился делать разбор подобных адресов дабы не усложнять логику работы программы.

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

Расширение модуля

Кроме упомянутых мною выше 4-х новых таблиц (Sites, Bots, SearchEngines и Keywords) меняется только таблица Session, в которой добавляются 4 поля, ссылающиеся на добавленные таблицы:

Ну и соответствующим образом меняется процедура старта сессии:

  ALTER procedure p_SessionStart 
     @UserID           int = null, 
     @IPAddress        varchar(15), 
     @BrowserString    varchar(1024), 
     @ReferralURL      varchar(4096) = null, 
     @Site                varchar(100) = null, 
     @PageUrl          varchar(255), 
     @BotID               int = null, 
     @SiteName            varchar(255) = null, 
     @Keyword             varchar(1000) = null, 
     @SearchEngineID      int = null 
  as 
  begin tran 
  declare @PageID int 
  select @PageID = PageID from Page where PageURL = @PageUrl and (@Site is null and Site = @Site or Site is null) 
  if @PageID is null 
  begin 
     insert into Page (PageURL, Site) values (@PageUrl, @Site) 
     select @PageID = @@IDENTITY 
  end 
  declare @SiteID   int 
  if @SiteName is not null 
  begin 
        select @SiteID = SiteID from Sites where Name = @SiteName 
        if @SiteID is null 
        begin 
              insert into Sites (Name) values (@SiteName) 
              select @SiteID = @@IDENTITY 
        end 
  end 
  declare @KeywordID      int 
  if @Keyword is not null 
  begin 
        select @KeywordID = KeywordID from Keywords where Keywords = @Keyword 
        if @KeywordID is null 
        begin 
              insert into Keywords (Keywords) values (@Keyword) 
              select @KeywordID = @@IDENTITY 
        end 
  end 
  insert into Session (UserID, DateStart, FirstPageID, LastPageID, DateEnd, IPAddress, BrowserString, ReferralURL, BotID, SiteID, SearchEngineID, KeywordID) 
     values (@UserID, getDate(), @PageID, @PageID, getDate(), @IPAddress, @BrowserString, @ReferralURL, @BotID, @SiteID, @SearchEngineID, @KeywordID) 
  commit 
  return @@identity 
  

На самом деле заполнять дополнительное данные о роботах, реферралах и поисковых системах можно 2-мя путями – в момент добавления записи в БД или отдельной хранимой процедурой, обрабатывающей уже сохраненные в базу данные. Соответственно во втором случае изменение хранимой процедуры для добавления записи о сессии не нужно. И, так как этот путь немного проще, я сначала покажу его реализацию.

Обработка логов в БД

Как я уже упомянул, эта процедура должна обрабатывать уже введенные данные. У меня это были данные, введенные за какой-то последний отрезок времени (конкретней – за последний час). Соответственно одним из параметров этой процедуры будет дата/время, с которой нужно обрабатывать данные. И, кроме того, также в нее передается параметр каким образом обрабатывать записи роботов – связывать их или же удалять

  ALTER PROCEDURE p_Session_Fill 
        @DateStart  datetime = null, 
        @ClearBots  bit = 0 
  AS 
  if @DateStart is null 
        set @DateStart = dateadd(hh, -1, getDate()) 
  

Обработка записей роботов проста до примитивизма. При сохранении логов заходов роботов делается 2 простых update по связке таблиц Session и Bots (второй запрос нужен для того, чтобы обработать "невыясненных" роботов, милостиво соизволивших сообщить о себе, что они таки роботы). При очистке же базы от записей заходов роботов – две не менее простых команды delete.

  if @ClearBots = 0 
  begin 
        update  
              Session  
        set  
              BotID = Bots.BotID  
        from 
              Bots 
        where  
              Session.BotID is null and DateStart > @DateStart and Mask <> '%bot%' and BrowserString like Mask 
        update  
              Session  
        set  
              BotID = Bots.BotID  
        from 
              Bots 
        where  
              Session.BotID is null and Mask = '%bot%' and DateStart > @DateStart and BrowserString like '%bot%' 
  end 
  else         
  begin 
        delete from Request where SessionID in (select SessionID from Session inner join Bots on BrowserString like Mask) 
        delete from Session where SessionID in (select SessionID from Session inner join Bots on BrowserString like Mask) 
  end 
  

Не бОльшую сложность представляет собой и запрос по определению заходов с поисковых систем

  update  
        Session  
  set  
        SearchEngineID = SearchEngines.SearchEngineID  
  from 
        SearchEngines 
  where  
        ReferralURL is not null and DateStart > @DateStart and ReferralURL like SearchMask 
  

В реферралы попадают все не попавшие в предыдущий запрос записи с непустым полем ReferralURL. При этом нужно получить только адрес сайта, с которого пришел реферрал.

  declare @ReferralUrl varchar(4096) 
  declare @SessionID      int 
  declare @SiteName varchar(255) 
  declare @SiteID int 
  declare cur1 cursor for 
        select SessionID, ReferralURL from Session with (nolock) 
              where ReferralURL is not null and DateStart > @DateStart and SiteID is null 
  open cur1 
  fetch next from cur1 into @SessionID, @ReferralUrl 
  while @@fetch_status = 0 
  begin 
        set @SiteID = null 
        set @SiteName = substring(@ReferralUrl, 8, len(@ReferralUrl) - 7) 
        set @SiteName = substring(@SiteName, 1, charindex('/', @SiteName) - 1) 
        select @SiteID = SiteID from Sites with (nolock) where Name = @SiteName 
        if @SiteID is null 
        begin 
              insert into Sites(Name) 
                    values (@SiteName) 
              set @SiteID = @@identity 
        end 
        update 
              Session 
        set 
              SiteID = @SiteID 
        where 
              SessionID = @SessionID 
        fetch next from cur1 into @SessionID, @ReferralUrl 
  end 
  close cur1 
  deallocate cur1 
  

Ну и последний момент – выделение поисковых запросов из уже найденных приходов с поисковых систем. Для этого как раз таки и нужно поле KeywordMask – все, что будет найдено в строке между подстрокой их этого поля и символом & (или до конца строки, если такого символа больше нет) считается поисковой фразой. При этом, как я уже упомянул ранее, запросы, в которых поисковые фразы не выделяются параметрами URL, не учитываются (для запросов с таких поисковых систем значение KeywordMask равно /).

  declare @SearchEngineID int 
  declare @KeywordLabel varchar(10) 
  declare cur2 cursor for 
        select  
              SessionID,  
              Session.SearchEngineID,  
              ReferralURL,  
              KeywordsMask 
        from 
              Session with (nolock) inner join SearchEngines with (nolock) on Session.SearchEngineID = SearchEngines.SearchEngineID 
        where 
              KeywordID is null and DateStart > @DateStart 
  open cur2 
  fetch next from cur2 into @SessionID, @SearchEngineID, @ReferralUrl, @KeywordLabel 
  while @@fetch_status = 0 
  begin 
        declare @keywords varchar(1000) 
        declare @pos int 
        set @pos = null 
        set @keywords = null 
        set @pos = CHARINDEX(@KeywordLabel, @ReferralUrl) 
        if @pos > 0 
        begin 
              set @keywords = SUBSTRING(@ReferralUrl, @pos + len(@KeywordLabel), len(@ReferralUrl) - len(@KeywordLabel) - @pos + 1) 
              if @KeywordLabel <> '/' 
              begin 
                    set @pos = CHARINDEX('&', @keywords) 
                    if @pos > 0 
                          set @keywords = substring(@keywords, 1, @pos - 1) 
              end 
              else 
              begin 
                    while @pos <> 0 
                    begin 
                          set @keywords = SUBSTRING(@keywords, @pos + 1, len(@keywords) - @pos) 
                          set @pos = CHARINDEX('/', @keywords) 
                    end 
              end 
        end 
        if @keywords is not null and @keywords <> '' 
        begin 
              set @keywords = REPLACE(@keywords, '+', ' ') 
              set @keywords = REPLACE(@keywords, '%20', ' ') 
              set @keywords = ltrim(rtrim(@keywords)) 
              if @keywords <> '' 
              begin 
                    declare @KeywordID int 
                    set @KeywordID = null 
                    select @KeywordID = KeywordID from Keywords with (nolock) where Keywords = @keywords 
                    if @KeywordID is null 
                    begin 
                          insert into Keywords (Keywords) 
                               values (@keywords) 
                          set @KeywordID = @@identity 
                    end 
                    update 
                          Session 
                    set 
                          KeywordID = @KeywordID 
                    where 
                          SessionID = @SessionID 
              end 
        end 
        fetch next from cur2 into @SessionID, @SearchEngineID, @ReferralUrl, @KeywordLabel 
  end 
  close cur2 
  deallocate cur2 
  

Все, обработка логов на стороне сервера завершена. Теперь достаточно вызывать только что написанную процедуру, например, раз в час и логи будут готовы к дальнейшему использованию.

Но не всегда это может быть удобно. Если необходима система статистики реального времени, отчеты по которой должны давать верные данные за любой промежуток, то тут не обойтись без синхронизации записи логов с их обработкой. И в этом случае может пригодиться вынос этой логики в программный код.

Модернизация программного кода

Для этого необходимо в принципе реализовать ту же самую логику выделения запросов поисковых роботов, реферральных приходов и приходов с поисковых систем в уже написанном ранее модуле.

В конфигурацию модуля добавим 2 дополнительных параметра – ParseAddData для задания факта дополнительной программной обработки пользовательских запросов и PersistBots для указания сохранять ли в базе запросы роботов. Для этого  в класс SiteStatsSettings также добавим строки

  public bool ParseAddData;
  public bool PersistBots;
  

Ну а в код метода Create класса SiteStatsConfigHandler соответственно добавим их инициализацию

  ret.ParseAddData = section.SelectSingleNode("ParseAddData") != null ? bool.Parse(section.SelectSingleNode("ParseAddData").InnerText) : false;
  ret.PersistBots = section.SelectSingleNode("PersistBots") != null ? bool.Parse(section.SelectSingleNode("PersistBots").InnerText) : true; 
  

Теперь конфигурационная секция модуля в файле web.config будет выглядеть примерно так.

  <siteStats> 
              <ConnectionString>server=localhost;uid=sa;pwd=;database=SiteStats</ConnectionString> 
              <SessionIDPlace>Cookie</SessionIDPlace> 
              <ParseAddData>true</ParseAddData> 
              <PersistBots>true</PersistBots> 
  </siteStats> 
  

Для поиска и определения идентификаторов роботов и поисковых систем в коде SiteStatsBLL добавляются 2 статических поля типа SortedList для хранения списка шаблонов роботов и поисковых сайтов и статический конструктор для заполнения этих списков. При этом для удобства поиска в этих списках будем хранить не сами маски для поисков, а сразу же создадим соответствующие экземпляры класса Regex. И так как поиск ведется до первого найденного значения не нужно забывать о том, что порядок размещения масок важен (поиск по маске "bot" должен производиться в последнюю очередь)

  private static SortedList bots; 
  private static SortedList searchEngines; 
  private class SearchEngine 
  { 
        public Regex Mask; 
        public string Key; 
        public SearchEngine(Regex mask, string key) 
        { 
              Mask = mask; 
              Key = key; 
        } 
  } 
  static SiteStatsBLL() 
  { 
        bots = new SortedList(); 
        SiteStatsSettings settings = (SiteStatsSettings) ConfigurationSettings.GetConfig("siteStats"); 
        SqlConnection myConn = new SqlConnection(settings.ConnectionString); 
        SqlCommand myCmd = new SqlCommand("select * from Bots", myConn); 
        myConn.Open(); 
        SqlDataReader rdr = myCmd.ExecuteReader(); 
        while(rdr.Read()) 
        { 
              Regex mask = new Regex(rdr["Mask"].ToString().Replace("%", "(.+?)"), RegexOptions.Compiled | RegexOptions.IgnoreCase); 
              bots.Add((int) rdr["BotID"], mask); 
        } 
        rdr.Close(); 
        searchEngines = new SortedList(); 
        myCmd.CommandText = "select * from SearchEngines"; 
        rdr = myCmd.ExecuteReader(); 
        while(rdr.Read()) 
        { 
              Regex mask = new Regex(rdr["SearchMask"].ToString().Replace("%", "(.+?)"), RegexOptions.Compiled | RegexOptions.IgnoreCase); 
              bots.Add((int) rdr["SearchEngineID"], new SearchEngine(mask, rdr["KeywordMask"].ToString())); 
        } 
        rdr.Close(); 
        myConn.Close(); 
  } 
  

В код метода SessionStart добавим блок для определения идентификаторов робота и поисковой системы, а так же поисковой фразы и сайта-реферрала

  int BotID = 0; 
  int SearchEngineID = 0; 
  string SiteName = ""; 
  string Keyword = ""; 
  if(settings.ParseAddData) 
  { 
        foreach(object key in bots.Keys) 
              if(((Regex) bots[key]).IsMatch(context.Request.UserAgent == null ? "" : context.Request.UserAgent)) 
              { 
                    BotID = (int) key; 
                    break; 
              } 
        if(BotID != 0 && !settings.PersistBots) 
              return -1; 
        if(context.Request.UrlReferrer != null && context.Request.UrlReferrer.ToString() != "") 
        { 
              foreach(object key in searchEngines.Keys) 
                    if(((SearchEngine) searchEngines[key]).Mask.IsMatch(context.Request.UrlReferrer.ToString())) 
                    { 
                          SearchEngineID = (int) key; 
                          foreach(string param in context.Request.UrlReferrer.Query.Split('&')) 
                               if(param.StartsWith(((SearchEngine) searchEngines[key]).Key)) 
                               { 
                                     Keyword = param.Replace(((SearchEngine) searchEngines[key]).Key, ""); 
                                     break; 
                               } 
                          break; 
                    } 
              if(SearchEngineID == 0) 
                    SiteName = context.Request.UrlReferrer.Host; 
        } 
  } 
  

Обратите внимание, что в случае, если система настроена на игнорирование запросов поисковых роботов, то этот метод вернет -1. Соответственно в методе Request дополнительно нужно сделать проверку значения SessionID перед записью информации о запросе в базу (сразу после секции определения SessionID).

  if(SessionID == -1)
        return;
  

Ну и, наконец, в том же методе SessionStart нужно добавить код для передачи при необходимости найденных значений в хранимую процедуру

  if(settings.ParseAddData) 
  { 
        if (BotID != 0) 
              myCmd.Parameters.Add("@BotID", BotID); 
        if (SearchEngineID != 0) 
              myCmd.Parameters.Add("@SearchEngineID", SearchEngineID); 
        if (Keyword != "") 
              myCmd.Parameters.Add("@Keyword", Keyword); 
        if (SiteName != "") 
              myCmd.Parameters.Add("@SiteName", SiteName); 
  } 
  

Все, теперь работа по созданию модуля логирования точно завершена :). В прилагающемся к статье архиве можно найти mdf файл базы данных и весь исходный код. Ну а я же в следующей статье постараюсь добавить жизни в этот проект и опишу создание набора отчетов для получения более любимых руководством и/или заказчиками картинок и таблиц с использованием Reporting Services.

Загрузить исходный код данной статьи можно здесь