MSレポートサービスに基づくトラフィックレポートTMGの作成。 継続

パート2：レポート

こんにちは、 最初の部分では、1時間に1回データで定期的に更新されるテーブルを作成しました。

次に、たとえば、いくつかのレポートを作成します（レポートビルダー2.0を使用してレポートを作成します）。



ウォームアップするために、一定期間TMGを通過した総トラフィックに関するレポートを作成します。

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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

レポートを作成する期間を選択するために、変数@FromDateおよび@ToDateを作成しました（ビルダー2.0では、変数はパラメーターセクションで作成され、変数のタイプは日付/時刻であり、デフォルト値は設定されていません）。



これは、ビルダーの外観です。





これは、完成したレポートの外観です。





次に、会社のある部門によるインターネットトラフィックの消費に関するレポートを作成しましょう。 ユーザーを部門にバインドするには、対応する部門のOUですべてのsAMAccountNameを検索する必要がありました（ADに要求を行うために、ドメインコントローラーの1つにリンクサーバーが作成されました）。



リクエストをする：

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ADからのクエリ結果を記録するために一時テーブルを使用すると、レポートのパフォーマンスが大幅に向上する可能性があることに注意してください。 レポートの例：



このレポートでは、ユーザー名は、これらの名前とfromdate、todateがパラメーターとして渡される別のレポートへのリンクとして機能します。このレポートは後で検討します。



ユーザーレポートでは、彼が訪問したサイトと、一定期間にダウンロードしたトラフィックの量に関心があるため、リクエストを作成します。

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このリクエストでは、とりわけ、destinationhostがFQDNであるかどうかを確認し、FQDNの場合はParseurl関数を使用して解析し、それをレポートに挿入します。



Parseurl関数：

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

私の場合、2番目のレベルより下のサブドメインはレポートにとって重要ではなく、上記の関数はそれらを2番目のレベルに切り捨てます。





パフォーマンスを改善するには、クエリの例として、非クラスター化インデックスを作成することをお勧めします。

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結論：

1-「クリーン」テーブルを使用すると、保存されるデータの量を減らし、レポート作成の時間を短縮できます。

2-データをクリーンテーブルに書き込むSQLジョブは、平均30秒で実行されます。

3-レポートは5分以内に作成されます。

4-レポートを生成するためにシステム管理者を関与させる必要はありません



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