Создание отчетов по трафику 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.

Автор: greeensnake

Источник