Создание отчетов по интернет трафику TMG на основе MS Reporting Services

1 часть: чистая таблица

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

Первоначальные условия задачи: существует TMG сервер который пишет логи на удалённый MS SQL сервер.

Итак, если открыть БД, в которую пишет логи TMG, то мы увидим две таблицы – Firewalllog и Webproxylog. Для построения отчетов и оптимизации объёма хранимых данных я создал третью, “чистую”, таблицу – report:

CREATE TABLE [dbo].[report]( 	[ID] [bigint] IDENTITY(1,1) NOT NULL, 	[ClientUserName] [nvarchar](514) NULL, 	[DestinationHost] [nvarchar](514) NULL, 	[bytesrecvd] [bigint] NULL, 	[bytessent] [bigint] NULL, 	[logTime] [date] NULL, 	[ClientAgent] [varchar](255) NULL, 	[ClientIP] [nvarchar](514) NULL,  CONSTRAINT [PK_report] PRIMARY KEY CLUSTERED 

В данную таблицу будут переноситься данные из таблиц Firewalllog и Webproxylog. Для переноса данных я использовал SQL job, который запускается раз в час и, суммируя данные, вставляет их в чистую таблицу, затем эти данные из дефолтных таблиц удаляются, а так же удаляются данные из “чистой” таблицы старше 180 дней:

insert into dbo.REPORT (ClientUserName, ClientAgent, clientip, logtime, destinationhost, bytesrecvd, bytessent)  select  dbo.GetUserName (ClientUserName, SourceIP) as ClientUserName, ClientAgent, dbo.parseip (sourceip) as clientip, cast (logtime as date) as logtime,  dbo.parseip (destinationip) as destinationhost, SUM(bytessent) as bytessent, sum(bytesrecvd) as bytesrecvd from dbo.FirewallLog  where (DestinationIP not like 'адрес локальной сети' and DestinationIP not like 'внешний адрес TMG' and SourceIP not like 'внешний адрес TMG' ) and bytessent + bytesrecvd <> 0 and (cast (logtime as date) >= cast (dateadd (day, -5, GETDATE()) as DATE) and cast (logtime as date) <= cast (GETDATE() as DATE)) and Action <> 11  group by ClientUserName, SourceIP, DestinationIP, cast (logtime as date), ClientAgent  union  select  dbo.GetUserName (ClientUserName, ClientIP) as ClientUserName, ClientAgent, dbo.parseip (clientip) as clientip, cast (logtime as date) as logtime, urldesthost as destinationhost, SUM(bytesrecvd) as bytessent, sum(bytessent) as bytesrecvd  from dbo.WebProxyLog  where (DestHostIP not like 'адрес локальной сети' and DestHostIP not like 'внешний адрес TMG' and ClientIP not like 'внешний адрес TMG') and bytessent + bytesrecvd <> 0 and (cast (logtime as date) >= cast (dateadd (day, -5, GETDATE()) as DATE) and cast (logtime as date) <= cast (GETDATE() as DATE)) and Action <> 11 group by ClientUserName, ClientIP, UrlDestHost, cast (logtime as date), ClientAgent  delete from dbo.FirewallLog where cast (logtime as date) >= cast (dateadd (day, -5, GETDATE()) as DATE) and cast (logtime as date) <= cast (GETDATE() as DATE)  delete from dbo.WebProxyLog where cast (logtime as date) >= cast (dateadd (day, -5, GETDATE()) as DATE) and cast (logtime as date) <= cast (GETDATE() as DATE)  delete from dbo.REPORT where logtime <= cast (dateadd (day, -180, GETDATE()) as DATE)  

Разберём запрос более подробно, первое, с чем пришлось столкнуться, это формат данных, содержащий IP адрес, а именно поля sourceip и destinationip. У данных полей тип данных uniqueidentifier, и IPv4 адрес имеет вид С0A89E4B-FFFF-0000-0000-000000000000. Для того чтобы преобразовать данную строку в привычный нам IPv4 адрес, нужно взять левую часть данной строки до FFFF, разбить её на 4 блока по две цифры и преобразовать из шестнадцатеричной системы в десятичную — С0.A8.9E.4B = 192.168.158.75. Для этой цели в запросе используется функция dbo.parseip:

USE [TMG] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[parseIP] (        @ui uniqueidentifier ) RETURNS varchar(128) AS BEGIN declare @hex varchar(8),  @t varchar (8), @number int = 1, @n INT = 0, @IP varchar(128) = '', @i int = 1  SET    @hex = SUBSTRING(CONVERT(VARCHAR(128), @ui), 1, 8)  WHILE @i <8 BEGIN        set @t =  REVERSE(SUBSTRING(@hex, @i, 2))        --SELECT @t         WHILE @number < = LEN(@t)        BEGIN              SET @n = @n +              case lower(SUBSTRING(@t, @number, 1))                     when '0' then 0                     when '1' then 1                     when '2' then 2                     when '3' then 3                     when '4' then 4                     when '5' then 5                     when '6' then 6                     when '7' then 7                     when '8' then 8                     when '9' then 9                     when 'a' then 10                     when 'b' then 11                     when 'c' then 12                     when 'd' then 13                     when 'e' then 14                     when 'f' then 15              end * convert( decimal( 28 , 0 ) , power( 16 , @number - 1 ) )              SET @number = @number + 1        END --     SELECT @n         SET @IP = @IP + CASE WHEN LEN(@IP) >0 THEN '.' ELSE '' END + CONVERT(VARCHAR(3), @n)        SELECT @n = 0, @number = 1        SET @i = @i +2 END --SELECT @IP         -- Return the result of the function        RETURN @IP  END  

Далее функция dbo.GetUserName:

USE [TMG] GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO  CREATE FUNCTION [dbo].[GetUserName] (@Username nvarchar(128), @IpAddress uniqueidentifier)   RETURNS nvarchar(128) AS   BEGIN 	IF (@Username = NULL OR @Username = '-' OR @Username = 'anonymous') RETURN dbo.parseip (@IpAddress); 	RETURN @Username; END  

Здесь особо ничего интересного нет, функция использовалась еще в ISA 2006, единственное, что пришлось поменять, это тип данных для @IpAddress.

По функциям в данном запросе все, теперь посмотрим на некоторые нюансы записи данных.

В таблице Webproxylog колонки bytessent и bytesrecvd значат ровно противоположное их названиям, поэтому в запросе они перевернуты — SUM(bytesrecvd) as bytessent, sum(bytessent) as bytesrecvd.
Последнее что хотелось бы отметить, это фильтрация промежуточного состояния соединения и нулевых записей. Дело в том что при “длительных” соединениях TMG записывает промежуточное количество трафика которое получило/передало соединение, подобные промежуточные данные и фильтруются с помощью Action <> 11. По поводу нулевых записей все достаточно очевидно, для подсчета прошедшего трафика они ценности не представляют.
После выполнения данного запроса в “чистой” таблице появляются данные, на основании которых строятся отчеты в reporting services, но об этом во второй части.

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