Microsoft Way

Анализ логов Exchange 2007

Я несколько раз встречал вопрос, какое средство лучше использовать для составления отчетов по Exchage 2007. Да, собственно говоря, ничего более того, что предоставляет сам вендор.
Существует свободно распространяемый инструмент под названием LogParser, который можно скачать на сайте Microsoft. Но, правда, конечно, он требует некоторого приложения усилий для своего использования, пятью кликами мышкой здесь действительно не обойтись. Зато, должен заметить, не требуется ни дополнительных капиталовложений, ни нарушения лицензии на использования средств сторонних разработчиков. Всего лишь нужен не слишком ленивый системный администратор и четкое понимание, какой именно отчет необходимо сформировать.

Logparser крайне прост для освоения любым человеком, который хоть раз формировал SQL запрос.
Не важно, на каком конкретно диалекте: как говорится, select он и есть select, а по конкретному синтаксису есть вполне адекватная справка. Но самое приятная деталь состоит в том, что среди форматов вывода отчета LogParser поддерживает графические построения, используя для этой цели средства office 2003 web components. Разумеется данные компоненты необходимо предварительно инсталлировать в систему. Но не забудьте, что право на их использование предполагает наличие действующей лицензии на Office 2003. Несмотря на то, что набор компонентов может скачать с сайта Microsoft любой желающий, не стоит скачивать и устанавливать компоненты, если у вас нет соответствующей лицензии — этим вы нарушаете закон.
И, разумеется, удобной функцией является исполнение SQL запроса из внешнего файла. Лично я бы прекрасно прожил и без этого, но так запросы читаются несравненно лучше. Поскольку я здесь намерен приводить запросы с их разбором, остановимся на этом варианте работы.

Хотя, нет, давайте попробуем сразу сделать простейший запрос:
logparser «select sender-address,message-subject,count(*) from MSGTRK20090101-1.LOG group by Sender-address,message-subject order by count(*) desc» -i:w3c
В ответ получаем:
Error: SELECT clause: Syntax Error: unknown field ‘sender-address’.

The closest match for input format ‘W3C’ is ‘date-time,client-ip,client-hostname,server-ip,server-hostname,source-context,connector-id,source,event-id,internal-message-id,message-id,recipient-address,recipient-status,total-bytes,recipient-count,related-recipient-address,reference,message-subject, sender-address,return-path,message-info’.

To see valid fields for the W3C input format type:

LogParser -h -i:W3C

Вот и вопрос, где именно мы ошиблись. Очевидно, так дело не пойдет. Будем использовать системный подход и вынесем запрос в отдельный файл. Заодно еще раз перечитаем документацию на LogParser 😉

Итак, предположим, мы хотим вывести не ту белиберду, что написана в запросе выше, а всех отправителей во внешний мир за вчерашний день, с подсчетом количеством отправленных сообщений. Что необходимо написать в командной строке:

1. logparser.exe — без коментариев
2. file:yesterday_all_domain_senders.sql — подключаем внешний файл с SQL запросом
3. —i:csv — LogParser умеет работать с несколькими входными форматами, в нашем случае (exchange 2007) это CSV файл
4. -nSkipLines:4 — параметр, указывающий на то, что разбор каждого нового файла необходимо начинать, пропустив первые 4 строки. Именно его мы забыли написать в нашем начальном запросе.

Теперь напишем файл yesterday_all_domain_senders.sql
select
all sender-address as sender_address, — выберем все содержимое полей отправителя в переменную
count(sender-address) as hits — посчитаем количество вхождений каждого отправителя
from MSGTRK20090101-1.LOG — задаем файл, к которому будет производиться запрос
where
source like ‘SMTP’ — отбираем только записи, в которых явно указано
and event-id like ‘SEND’ — что это отсылка сообщения по SMTP
group by sender_address —— произведем группировку одинаковых записей отправителя
order by hits asc — отсортируем наш полученный список по возрастанию количества вхождений

Итак, исполним наш запрос:
logparser file:yesterday_all_domain_senders.sql -i:csv -nSkipLines:4
Что же мы получим на выходе? Результат. Нас что-то устраивает? Разумеется:

1. Очень похоже на то, что письма внутри организации попадают в нашу выборку. Мы же хотим получить только список отправителей во внешний мир.
2. SMTP и SEND вовсе не означает, что письмо не было переслано с внешнего мира в сторону сервера почтового хранилища
3. Мы очень рады, что postmaster шлет отбивки о доставке и прочтении сообщений, полученных извне, но нас это не очень интересует в данный момент.
4. У нас есть технический адрес на виртуальном поддомене, используемый для подачи заявок через почту. Нам бы хотелось исключить из выборки и его.
5. Хотелось бы не задумываться об имени файла, а поручить отбор событий самому LogParser
6. Нас также посещает озарение о тщетности бытия и том факте, что логи Exchange пишутся в UTC времени и понятие «вчера» необходимо правильно определить.

Ок, давайте напишем этот запрос в файле yesterday_all_domain_senders.sql:
select
all sender-address as sender_address,
count(sender-address) as hits
from msgtrk*.log — да, подстановочные символы работают!
where
TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’.’),’yyyy-MM-ddThh:mm:ss’)),’yyyy-MM-dd’) = TO_STRING(TO_LOCALTIME(SUB(SYSTEM_TIMESTAMP(), TIMESTAMP(‘2’, ‘d’))),’yyyy-MM-dd’)
/*доктор, заберите меня отсюда, я пишу безумные запросы*/
and recipient-address not like ‘%@libertine.su%’ — адрес хотя бы одного получателя не принадлежит нашему домену
and Sender-address like ‘%@libertine.su%’ — адрес отправителя принадлежит нашему домену
and Sender-address not like ‘postmaster@libertine.su%’ — пожелание номер два выше
and Sender-address not like ‘extsuport@libertine%’ — пожелание номер три выше
and source like ‘SMTP’
and event-id like ‘SEND’
group by sender_address
order by hits asc
Что на выходе? Теперь уже лучше. Вот только что делать с этой длинной простыней в командной строке?
Да просто не нужно выводить ее в командную строку.

Как я уже упоминал, LogParser поддерживает графические выходные форматы.
Необходимо дополнить строку вызова logparser следующими параметрами:

1. -o:CHART — задаем выходной формат, в нашам случае это будет диаграмма
2. -chartType:barstacked — задаем тип диаграммы согласно нашим представления об удобной визуализации
3. -chartTitle:»Yesterday_Senders» — добавляем заголовок к нашей диаграмме
4. -groupSize:1024×1600 — задаем выходной размер картинки, в каждом конкретном случае необходимо подбирать параметры

Также необходимо дополнить первые строки файла yesterday_all_domain_senders.sql:
select
all sender-address as sender_address,
count(sender-address) as hits
into yestarday_all_domain_senders.gif
from msgtrk*.log

Давайте выполним наш запрос:
logparser file:yesterday_all_domain_senders.sql -i:csv -nSkipLines:4 -o:CHART -chartType:barstacked -chartTitle:»Yesterday_Senders» -groupSize:1024×1600

У нас получится примерно следующая диаграмма:

Замечательно. Но этого одного, разумеется, не хватает для полного понимания происходящих процессов.

Предлагаю вывести список из 30 отправителей, пославших вчера максимальный объем информации во внешний мир.
Файл SQL запроса yesterday_top30_domain_senders_by_size.sql будет выглядеть примерно следующим образом:
select
top 30 sender-address as sender_address,
div(sum(total-bytes),1048576) as total_sent_Mb,
/* выбираем только top30 адресов отправителей с учетом общего размера отосланных сообщений, для наглядности делим для каждой встреченной записи поле размера сообщения на 1Mb */
count(sender-address) as total_messages — посчитаем количество вхождений каждого отправителя (число сообщений)
into yesterday_top30_domain_senders_by_size.gif
from msgtrk*.log
where
TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’.’),’yyyy-MM-ddThh:mm:ss’)),’yyyy-MM-dd’) = TO_STRING(TO_LOCALTIME(SUB(SYSTEM_TIMESTAMP(), TIMESTAMP(‘2’, ‘d’))),’yyyy-MM-dd’)
and recipient-address not like ‘%@libertine%’
and Sender-address like ‘%@libertine%’
and Sender-address not like ‘postmaster@libertine%’
and Sender-address not like ‘extsuport@libertine%’
and source like ‘SMTP’
and event-id like ‘SEND’
group by sender_address — сгруппируем по адресу отправителя
order by total_sent_Mb desc — отсортируем наш полученный список по убыванию общего размера отправленного

А вот таким образом мы напишем строку запуска LogParser
logparser file:yesterday_top30_domain_senders_by_size.sql -i:csv -nSkipLines:4 -o:CHART -chartType:Column3D -chartTitle:»Senders by size/messages» -groupSize:1024×768 -values:on

Здесь мы использовали другой тип выходной диаграммы. И вот что у нас получилось:

Ок, а теперь предлагаю вывести top50 получателей внутри домена по количеству сообщений за вчерашний день.
Файл SQL запроса yesterday_top50_domain_recipients.sql будет выглядеть примерно следующим образом:
select
top 50 recipient-address as recipient_address,
count(recipient-address) as total_items
into yesterday_top50_domain_recipients.gif
from msgtrk*.log
where
TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’.’),’yyyy-MM-ddThh:mm:ss’)),’yyyy-MM-dd’) = TO_STRING(TO_LOCALTIME(SUB(SYSTEM_TIMESTAMP(), TIMESTAMP(‘2’, ‘d’))),’yyyy-MM-dd’)
and recipient-address like ‘%@libertine%’
and Sender-address not like ‘%@libertine%’
and source like ‘SMTP’
and event-id like ‘RECEIVE’
group by recipient_address
order by total_items desc
Соответственно, строка запуска LogParser может быть следующей:
logparser file:yesterday_top50_domain_recipients.sql -i:csv -nSkipLines:4 -o:CHART -chartType:barstacked -chartTitle:»Recipients (top 50)» -groupSize:1024×768
Конечно, ничего особо интересного и полезного в этом нет, но почему бы и не оставить эту информацию.

А вот вывод top10 отправителей во внешний мир, с учетом среднего размера сообщений может помочь выявить, как минимум, любителей рассылать видеоролики друзьям.
Итак, напишем SQL запрос yesterday_top10_senders_avg_size.sql
select
top 10 sender-address as sender_address,
div(avg(total-bytes),512000) as average_size_x500kb,
div(sum(total-bytes),1048576) as total_sent_Mb
/* отбираем top10 отправителей по показателям среднего размера сообщений и суммарного объема отосланной информации. Для наглядности средний размер вычисляем в полумегабайтах, а суммарный объем в мегабайтах (доктор, зачем Вы меня отпустили?).*/
into yesterday_top10_senders_avg_size.gif
from msgtrk*.log
where
TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’.’),’yyyy-MM-ddThh:mm:ss’)),’yyyy-MM-dd’) = TO_STRING(TO_LOCALTIME(SUB(SYSTEM_TIMESTAMP(), TIMESTAMP(‘2’, ‘d’))),’yyyy-MM-dd’)
and recipient-address not like ‘%@libertine%’
and Sender-address like ‘%@libertine%’
and Sender-address not like ‘postmaster@libertine%’
and Sender-address not like ‘extsuport@libertine%’
and source like ‘SMTP’
and event-id like ‘SEND’
group by sender_address
order by total_sent_Mb desc

Соответственно, строка запуска LogParser может быть следующей:
logparser file:yesterday_top10_senders_avg_size.sql -i:csv -nSkipLines:4 -o:CHART -chartType:Column3D -chartTitle:»Senders_avg_size (top 10)»

У нас получится примерно такая диаграмма:

Ок, замечательно. Но остаются еще нерешенные задачи:

1. Хотелось бы автоматизировать анализ логов
2. Анализ хотелось бы проводить не только за предыдущий день, а за, например, две недели.

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

1. Создал файл yesterday.cmd следующего содержания:
   md \\exchange_server\reports$\%date%
   net use v: \\exchange_server\reports$\%date% /yes
   logparser …
   net use v: /delete /yes
2. Сделал общей папку с необходимыми логами
3. Внутри каждого из файлов .sql записал пути в соответствующих строках:
   into v:\[файл вывода]
   from \\exchange_server\MessageTracking$\msgtrk*.log
4. На рабочем столе рабочей станции администратора сделал ярлык в общую папку \\exchange_server\reports$
5. Настроил права таким образом, чтобы никто, кроме сервера и конкретного администратора не имел доступ к папкам reports$ и MessageTracking$

Что же касается изменения интервала для запроса, то необходимо лишь изменить условие отбора внутри sql файлов. Ну и, разумеется, изменить все имена файлов для вывода результата 😉
Вот пример изменения файла yesterday_all_domain_senders.sql на файл two_weeks_all_domain_senders.sql для условия, что logparser снова находится в папке с логами сервера:
select
all sender-address as sender_address,
count(sender-address) as hits
into two_weeks_all_domain_senders.gif
from msgtrk*.log
where
TO_TIMESTAMP(TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’.’),’yyyy-MM-ddThh:mm:ss’)),’yyyy-MM-dd’),’yyyy-MM-dd’) > TO_TIMESTAMP(TO_STRING(TO_LOCALTIME(SUB(SYSTEM_TIMESTAMP(), TIMESTAMP(’15’, ‘d’))),’yyyy-MM-dd’),’yyyy-MM-dd’)
and TO_TIMESTAMP(TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’.’),’yyyy-MM-ddThh:mm:ss’)),’yyyy-MM-dd’),’yyyy-MM-dd’) < TO_TIMESTAMP(TO_STRING(TO_LOCALTIME(SUB(SYSTEM_TIMESTAMP(), TIMESTAMP(‘1’, ‘d’))),’yyyy-MM-dd’),’yyyy-MM-dd’)
and recipient-address not like ‘%@libertine%’
and Sender-address like ‘%@libertine%’
and Sender-address not like ‘postmaster@libertine%’
and Sender-address not like ‘extsuport@libertine%’
and source like ‘SMTP’
and event-id like ‘SEND’
group by sender_address
order by hits asc

Разумеется, в рамках этой статьи я не рассмотрел и толики того, что может предоставить LogParser. Например, можно сделать шаблон для вывода в виде html файла, где совместить текстовое представление отчета в виде таблиц и графическое в виде диаграмм. Собственно, это тоже не представляет сложной задачи, если, конечно, есть реальная необходимость в ее решении.

Данное решение затрагивает только отчеты, основанные на логах транспортной роли Exchange, а не статистике использования хранилищ. Но как правило, ничего более и не требуется. Впрочем, статистику по использованию хранилища получить еще легче. Но об этом я напишу в рамках какой-нибудь другой статьи.