Log Parser: нужно разобрать логи? собрать статистику по ним? - нет ничего проще

April 23, 2017

Недавно на работе 2 поставили задачу: нужно собрать статистику по логам с прокси-сервера (формат W3C) за месяц и отобразить все это в особом виде в Excel, с последующей автоматизацией этого процесса.

Задача ясна. Но как разбирать логи за месяц? когда средний размер данных за один день составляет примерно 1.7 Gb? Недолгие поиски в интернете вывели нас на замечательный инструмент от Microsoft Log Parser 2.2. На странице проекта очень подробно описаны все возможности данного инструмента.

А если кратко, то:

  1. Указываем формат входных данных (как предопределенный, так и заданный вручную заголовок).
  2. Указываем путь и маску или конкретный файл в качестве источника данных для анализа.
  3. Составляем SQL запрос, для выборки данных из источника.
  4. Указываем строку соединения для базы данных приемника.
  5. Profit!

Log Parser позволил нам сформировать два набора данных: частота переходов и количество скаченной информации с сайта каждым пользователем с детализацией до суток; количество скаченной информации в целом каждым пользователем с детализацией до часа. Самое поразительно то, что обработка одного файла (без загрузки в БД) размером около 1.7 Gb составлялет около 3 минут.

Как говорится, нет предела совершенству: используя Windows Batch Scripting и sqlcmd.exe добились следующего поведения:

  1. Анализируется список файлов в каталоге по маске.
  2. Строится запрос в БД, возвращающий файлов, которые еще не загружены.
  3. Парсится ответ БД и выбираются файлы для загрузки.
  4. Запускается Log Parser, анализирует данные и вгружает их в БД.

Ниже приведу несколько примеров нашей работы.


 Запуск и загрузка статистики по трафику с детализацией до часа

upload_traffic_sql.bat

echo start upload_traffic_sql.bat

LogParser.exe -i:W3C -oConnString:%PROXYCONSTR% file:insert_traffic.sql?source=%1 -createTable:ON

echo end upload_traffic_sql.bat

insert_traffic.sql

SELECT
  TO_UPPERCASE([cs-username]) AS username,
  TO_DATE(QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP([date], [time])), 3600)) as cdate,
  case cdate when [date] then 0 else 1 end as part,
  TO_LOWERCASE([r-host]) AS site,
  Sum([cs-bytes]) AS download,
  count(*) AS hits
INTO tmp_sitetraffic
FROM %source%
WHERE
  [action]='Allowed' and
  [s-object-source] <> 'Cache'
GROUP BY
  username,
  cdate,
  part,
  site
ORDER BY
  cdate,
  username

 Запуск и загрузка статистики по сайтам с детализацией до одного дня

upload_sitetraffic_sql.bat

echo start upload_sitetraffic_sql.bat

LogParser.exe -i:W3C -oConnString:%PROXYCONSTR% file:insert_sites.sql?source=%1 -createTable:ON

echo end upload_sitetraffic_sql.bat

insert_sites.sql

SELECT
  TO_UPPERCASE([cs-username]) AS username,
  TO_DATE(QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP([date], [time])), 3600)) as cdate,
  case cdate when [date] then 0 else 1 end as part,
  TO_LOWERCASE([r-host]) AS site,
  Sum([cs-bytes]) AS download,
  count(*) AS hits
INTO tmp_sitetraffic
FROM %source%
WHERE
  [action]='Allowed' and
  [s-object-source] <> 'Cache'
GROUP BY
  username,
  cdate,
  part,
  site
ORDER BY
  cdate,
  username

 где %1 - это путь к конкретному файлу или каталогу с маской, например

c:\Windows\Log\*.log

%PROXYCONSTR%

set PROXYCONSTR="Driver=SQL Server;SERVER=SERVER_NAME;DATABASE=DB_NAME;Trusted_Connection=no;User Id=USER;password=PASSWORD;username=USER"

Примечание: нужно правильно указывать имя драйвера, от этого зависит формат строки подключения

Очень много примеров можно найти по ссылке: Log Parser Rocks! More than 50 Examples!