Создание отчетов по трафику TMG на основе MS Reporting services. Продолжение

2 Часть: отчеты

Здравствуйте, в первой части мы создали таблицу, которая исправно, раз в час, наполняется данными.
Теперь, для примера, мы построим несколько отчетов (для построения отчетов я использовал report builder 2.0).

Для разминки построим отчет по суммарному трафику, прошедшему через TMG за определенный отрезок времени:

use tmg select (SUM(bytesrecvd)/1024)/1024 as 'МБайт принято', (sum(bytessent)/1024)/1024 as 'МБайт отпралено', ((SUM(bytesrecvd) +  sum(bytessent))/1024)/1024 as 'Всего МБайт' from dbo.report where logTime >= @FromDate AND logTime <= @ToDate 

Для того чтобы выбрать отрезок времени за который требуется построить отчет я создал переменные @FromDate и @ToDate (в builder 2.0 переменные создаются в разделе parameters, тип переменной date/time, дефолтные значения не заданы).

Так это выглядит в builder-е:

Так выглядит готовый отчет:

Теперь построим отчет по потреблению интернет трафика каким нибудь департаментом компании. Для привязки пользователей к департаменту, пришлось выполнить поиск всех sAMAccountName в OU соответсвующего департамента (для того чтобы сделать запрос к AD, был создан Linked Server на один из контроллеров домена).

Составим запрос:

 use tmg  declare @tbl table(name varchar(256)) insert @tbl select '<имя домена>\' + sAMAccountName  from openquery ( ADSII,'SELECT sAMAccountName  FROM ''LDAP://<где искать>'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' ')  select clientusername, (SUM(bytessent)/1024)/1024 as 'отправлено MБайт', (sum(bytesrecvd)/1024)/1024 as 'скачано МБайт', ((SUM(bytesrecvd) +  sum(bytessent))/1024)/1024 as ' Всего МБайт ' from dbo.report  where ClientUserName in  ( SELECT name from @tbl) and (logTime >= @FromDate AND logTime <= @ToDate) group by clientusername    

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

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

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

use tmg select top (30) percent  ClientUserName, destinationhost,(SUM(bytesrecvd)/1024)/1024 as 'скачано МБайт', (sum(bytessent)/1024)/1024 as 'отправлено MБайт', ((SUM(bytesrecvd) +  sum(bytessent))/1024)/1024 as 'total' from  (select  CASE WHEN ISNUMERIC(replace(destinationhost, '.', '') )=1 THEN destinationhost        ELSE dbo.ParseUrl(destinationhost)       END destinationhost, bytesrecvd, bytessent, clientusername, logtime        from dbo.report )report2 where (clientusername like @Name) and (logTime >= @FromDate AND logTime <= @ToDate) group by clientusername, destinationhost order by total desc 

В данном запросе, в числе прочего, мы проверяем, является ли destinationhost FQDN-ом, если является то мы его парсим с помощью функции Parseurl, и только после этого вставляем в отчет.

Функция Parseurl:

USE [TMG] GO  SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE FUNCTION [dbo].[parseURL] (        @url varchar(128) ) RETURNS varchar(128) AS BEGIN declare @s varchar(128), @i int  IF (@url is null) RETURN @url  SET    @s = REVERSE(@url) SET    @i = CHARINDEX('.', @s) IF (0 = @i) RETURN @url  SET    @i = CHARINDEX('.', @s, @i + 1) IF (0 = @i) RETURN @url  RETURN REVERSE(SUBSTRING(@s, 1, @i - 1)) END GO  

В моем случае, поддомены ниже второго уровня для отчета неинтересны, и приведенная выше функция их усекает до 2-го уровня:

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

USE [TMG] GO CREATE NONCLUSTERED INDEX [dateindex2] ON [dbo].[REPORT]  ( 	[logTime] ASC ) INCLUDE ( [ClientUserName], [bytesrecvd], [bytessent]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] GO  

Выводы:
1 – Использование «чистой» таблицы позволяет уменьшить объём хранимых данных и сократить время построения отчета.
2 – SQL job, который заносит данные в чистую таблицу, выполняется в среднем 30 секунд.
3 – Отчеты строятся не более 5 минут.
4 – Для формирования отчетов не требуется привлекать системных администраторов

PS: Спасибо моим коллегам за ответы на вопросы по SQL, и за написание процедуры parseurl.