Pull to refresh

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

Reading time 4 min
Views 11K

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.
Tags:
Hubs:
+8
Comments 1
Comments Comments 1

Articles