Creating TMG traffic reports based on MS Reporting services. Continuation

Part 2: Reports

Hello, in the first part we created a table that is regularly, once an hour, filled with data.
Now, for example, we will build several reports (for report generation, I used report builder 2.0).

To warm up, let's build a report on the total traffic that has passed through TMG for a certain period of time:

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 

In order to choose the length of time for which to build a report, I created the @FromDate and @ToDate variables (in builder 2.0, variables are created in the parameters section, the type of the date / time variable, default values ​​are not set).
')
So it looks like in the builder:


It looks like a finished report:


Now we will build a report on the consumption of Internet traffic by some department of the company. To bind users to a department, we had to search for all sAMAccountName in the OU of the corresponding department (in order to make a request to AD, a Linked Server was created for one of the domain controllers).

Make a request:

 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 

It is worth noting here that using a temporary table to record the results of a query from AD, it allows you to significantly increase the performance of the report. Report example:

In this report, user names act as links to another report, in which these names, and fromdate, todate, are passed as parameters, this report will be discussed later.

In the user report, we are interested in which sites he visited and how much traffic from them downloaded over a certain period of time, we make a request:

 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 

In this request, among other things, we check whether the destinationhost is FQDN, if it is, we parse it using the Parseurl function, and only after that we insert it into the report.

Parseurl function:

 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 

In my case, the subdomains below the second level are uninteresting for the report, and the above function truncates them to the 2nd level:


To improve performance, I recommend creating a nonclustered index, an example for my queries:

 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 

Findings:
1 - Using a "clean" table allows you to reduce the amount of stored data and reduce the time to build a report.
2 - SQL job, which puts the data in a clean table, runs an average of 30 seconds.
3 - Reports are built no more than 5 minutes.
4 - Reporting is not required to involve system administrators.

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