Creating TMG Internet traffic reports based on MS Reporting Services

Part 1: clean table

At some point in time, I needed to create a flexible end-user traffic reporting system. This system had to solve two tasks: reduce the burden on system administrators, provide end users with a convenient system of reports in real time.

Initial conditions of the problem: there is a TMG server that writes logs to a remote MS SQL server.

So, if you open the database in which the TMG logs are written, we will see two tables - Firewalllog and Webproxylog. To create reports and optimize the amount of stored data, I created a third, “clean” table: 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 

Data from the Firewalllog and Webproxylog tables will be transferred to this table. To transfer data, I used a SQL job, which runs once an hour and, summing up the data, inserts it into a clean table, then this data is deleted from the default tables, and the data from the “clean” table older than 180 days is also deleted:

 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) 

Let us analyze the request in more detail, the first thing we had to deal with was the data format containing the IP address, namely the sourceip and destinationip fields. In these fields, the data type is uniqueidentifier, and the IPv4 address is C0A89E4B-FFFF-0000-0000-000000000000. In order to convert this string to the IPv4 address we are used to, you need to take the left side of this string to FFFF, divide it into 4 blocks of two digits each and convert from hexadecimal to decimal - C0.A8.9E.4B = 192.168.158.75. For this purpose, the query uses the dbo.parseip function:

 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 

')
Next, the dbo.GetUserName function:

 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 

There is nothing particularly interesting here, the function was used in ISA 2006, the only thing that had to be changed was the data type for @IpAddress.

By functions in this query all, now let's look at some of the nuances of data recording.

In the Webproxylog table, the columns bytessent and bytesrecvd mean exactly the opposite of their names, so they are inverted in the query - SUM (bytesrecvd) as bytessent, sum (bytessent) as bytesrecvd.
The last thing I would like to note is the filtering of the intermediate state of the connection and zero records. The fact is that with “long-lasting” connections, TMG records the intermediate amount of traffic that the connection received / transmitted, such intermediate data and are filtered using Action <> 11. As far as zero records are concerned, everything is quite obvious, they do not represent values ​​for past traffic.
After the execution of this request, data appears in the “clean” table, on the basis of which reports are built in the reporting services, but about this in the second part.

PS: Thanks to my colleagues for answering questions about SQL, and for writing the parseip procedure.