MS Reporting Servicesに基づいたインターネットトラフィックTMGに関するレポートの作成

1部：きれいなテーブル

ある時点で、柔軟なエンドユーザートラフィックレポートシステムを作成する必要がありました。 このシステムは、システム管理者の負荷を軽減し、エンドユーザーに便利なリアルタイムレポートシステムを提供するという2つの問題を解決することになっています。



タスクの初期条件：リモートMS SQLサーバーにログを書き込むTMGサーバーがあります。



そのため、TMGログが書き込まれるデータベースを開くと、FirewalllogとWebproxylogの2つのテーブルが表示されます。 レポートを作成し、保存されているデータの量を最適化するために、3つ目の「クリーン」なテーブル-レポートを作成しました。

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テーブルのデータは、このテーブルに転送されます。 データを転送するために、1時間に1回実行されるSQLジョブを使用し、データを集計してクリーンテーブルに挿入します。次に、このデータはデフォルトテーブルから削除され、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アドレスの形式はC0A89E4B-FFFF-0000-0000-000000000000です。 この文字列を通常のIPv4アドレスに変換するには、FFFFの前にこの文字列の左側を取得し、2桁の4ブロックに分割し、16進数から10進数に変換する必要があります-C0.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）がbytessent、sum（bytessent）がbytesrecvdになります。

最後に注意したいのは、接続とnullレコードの中間状態のフィルタリングです。 実際には、「長期」接続では、TMGは接続が受信/送信したトラフィックの中間量を記録します。このような中間データはアクション<> 11を使用してフィルタリングされます。ゼロレコードに関しては、すべてが十分に明らかであり、過去のトラフィックをカウントする価値はありません。

このリクエストを実行すると、データが「クリーン」なテーブルに表示されます。これに基づいて、レポートサービスでレポートが作成されますが、第2部ではさらに詳細になります。



PS：SQLの質問に答えてくれて、parseipプロシージャを書いてくれた同僚に感謝します。