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.
Автор: greeensnake
