Windows и логи squid 2: Анализ логов squid

Следующим шагом будет небольшая модернизация нашей таблицы в sql server и самого скрипта, а так же простое и быстрое получение отчета. Наша задача быстро и просто, без лишней головной боли получить отчет по использованию трафика пользователями. Для этого мы воспользуемся таким супернавернутым средством как microsoft excel. Как оказалось, круче него только горы.

Однако, для начала нам стоит изменить таблицу sql server, в которой хранятся наши логи. Мы добавим вычисляемое поле, которое будет преобразовывать данные объемов трафика из байтов в мегабайты. Для этого можно сделать примерно следующее:

alter table accesslog add mb as ((cast ([size] as decimal (20,3))/1024)/1024)

Проблема в том, что поле size у нас типа bigint, что обусловлено типом в файле лога. Верней особенностями logparser при работе с типами. При создании вычисляемого поля, сервер использует тип данных для поля, основываясь на исходных полях. В итоге, если не использовать преобразование cast ([size] as decimal (20,3) тип данных нового поля будет целым, что приведет к потере дробной части.

После добавления поля, количество полей, понятное дело, изменится. Это требует небольшой коррекции скрипта:

LogParser.exe" -o:sql -server:SQLSERVER -database:weblog -ignoreIdCols:on -createTable:on -i:TSV -iSeparator:space -nSep:1 -headerRow:off "select TO_TIMESTAMP( ADD( TO_INT(field1), TO_INT(TIMESTAMP('1970','yyyy')) ) ) AS field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, 0.0 into accesslog from \\gateway\squid\access.log.2"

Нужно добавить в конце инструкции select еще одно поле подходящего типа. Это, опять же, требования самого logparser.

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

Прежде всего создаем источник данных, путем создания запроса к внешним данным:

 

Указываем параметры нового запроса. А именно, откуда берем данные: сервер, таблица, язык. Язык влияет на формат дат. Кроме того выбираем тип аутентификации на сервере. В моем случае это доменная аутентификация. Однако можно использовать и аутентификацию sql server.

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

Тут нам нужно выбрать все поля таблицы, в которой хранятся данные squid, которые мы импортировали нашим скриптом. Для этого вам будет предложено окошко визарда. Прежде всего там нужно выбрать поля фактов. То есть поля, по которым будет происходить агрегирование. В нашем случае мы выбираем ранее созданное поле mb – объем данных, преобразованых к мегабайтам.

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

Затем визард предложит вам выбрать тип создаваемого куба. Я выбрал тут первый тип, чтобы не ждать долго его создания.

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

Готовый файл вы можете спокойно сохранить себе на диск. После того как новые логи будут втянуты в базу, достаточно нажать на кнопку “обновить данные” на панели инструментов, немного подождать – и вы получите готовый отчет с кучей всяких плюшек и фишек.

В общем и целом – задача довольно простая. Результат выглядит удобно и достаточно интерактивен, поскольку можно строить всякие графички, плюшечки и всякие разные красивости при помощи того же excel.

Windows и логи squid

  Некоторое время назад потребовалось предоставлять информацию, на предмет использования интернет на рабочем месте. Вообще, я считаю, что это лишнее. Если люди делают свою работу хорошо, зачем ограничивать им интернет. Тем более что в наше время он совсем не дорог. Однако руководство имеет совсем другое мнение. Заковыка состояла в том, что вся инфраструктура построена на windows, а прокси, соответственно, на linux. Файлы логов опубликованы на этом linux при помощи общей папки. Однако, имеющиеся под linux средства анализа и генерации отчетов никого не устроили по непонятным мне причинам. Вероятно потому, что внешний вид отчетов оставляет желать лучшего. Да и работать c html отчетами не удобно. Другое средство - internet access monitor for squid почему-то вело себя совсем неадекватно. Что, учитывая его стоимость и ограничения, совсем не хорошо. Например, поставить его можно только на одно рабочее место. То есть, если несколько человек хотят посмотреть отчеты и при этом, при необходимости, детализировать их там где надо, возникает проблема. Отчеты можно генерировать по расписанию, и рассылать почтой, но вот нужного функционала нет. Мало того, при быстром разрастании базы отчеты строятся “тыщщу лет”. В общем – никакой стабильности.

    Поколебамшись, я решил накрутить что-нибудь сам. Идея была в том, чтобы втянуть логи, как есть, в MS SQL Server, и потом обрабатывать их. И вот тут то и появилась первая проблема. Логи формата по-умолчанию, никак не хотели втягиваться стандартным визардом импорта. Верней, втягиваться то они втягивались. Но визард, разбирал их не совсем корректно. А все потому, что отформатированы они, мягко говоря, через одно место. То есть, человеку их, конечно, удобно читать. А вот разобрать их на столбцы простыми кликами мышки оказалось сложнее. В итоге, я решил использовать для этой цели powershell. Дай, думаю, я удалю лишние пробелы, авось втянется. Однако и тут, облом подкрался незаметно… Время обработки одного 300мб файла переваливало за 11 мин. Меня такая ситуация не устраивала в корне.

    В общем, поборовшись, какое-то время за скорость я решил вернуться к более традиционным средствам LogParser. Оказалось, что эта утилита умеет не только исполнять простые запросы на sql подобном языке к файлам различных форматов. Она умеет еще кучу всего всякого разного, включая преобразование форматов и запись в таблицы SQL Server. Последнее его свойство я и решил попробовать использовать. Оставалась только одна задача. Поле даты в этих журналах хранится в формате unix timestamp. Соответственно его нужно преобразовать к подходящему формату. И тут на помощь снова приходит logparser. Он подерживает некий набор функций, по работе с данными входных файлов. В общем и целом, без долгих разговоров – вот такой вот простой скрипт:

LogParser.exe" -o:sql -server:SQLSERVER -database:weblog -ignoreIdCols:on -createTable:on -i:TSV -iSeparator:space -nSep:1 -headerRow:off "select TO_TIMESTAMP( ADD( TO_INT(field1), TO_INT(TIMESTAMP('1970','yyyy')) ) ) AS field1, field2, field3, field4, field5, field6, field7, field8, field9, field10 into accesslog from \\gateway\squid\access.log.2"

Этот запрос выбирает данные прямо из логов squid и пишет их в базу ms sql. При этом, в случае отсутствия нужной таблицы первый раз, она создается. Единственный эпизод, после создания имеет смысл изменить имена полей таблицы, а так же типы данных полей, хранящих количество переданых байт и затраченое время на bigint. Это стоит сделать для того, чтобы в дальнейшем упростить запросы к базе, поскольку при агрегировании этих данных может случиться переполнение. Этот скрипт можно запускать по расписанию, допустим, каждый день. Запрос на моих объемах и условиях выполняется 5-6 минут. Думаю, процесс можно значительно ускорить, если разбить процедуру на два этапа. Сначала преобразовывать файл лога в более привычный формат, например csv, а затем втягивать его в базу при помощи bcp. Скорее всего можно достичь скорости, где-то в районе от 30 сек до минуты-двух.