home *** CD-ROM | disk | FTP | other *** search
/ moodle.waes.ac.uk / moodle.waes.ac.uk.zip / moodle.waes.ac.uk / TMG / SP1-TMG-KB981324-AMD64-ENU.msp / PCW_CAB_SHFx2 / F2143_msfpcui.dll / BINARY / 25063 < prev    next >
Text File  |  2010-06-15  |  5KB  |  128 lines
  1. CREATE PROCEDURE [dbo].[ISA_spSiteSummaryMonthly]
  2.     @CurrentMonthFirstDay datetime,
  3.     @CurrentMonthLastDay datetime,
  4.     @LastDailyToKeep datetime,
  5.     @LastMonthlyToKeep datetime
  6. AS
  7. BEGIN
  8.     SET NOCOUNT ON;
  9. -- move dailies to monthlies
  10.     SELECT
  11.             [Site] AS [Site],
  12.             SUM([BytesIn]) AS [BytesIn],
  13.             SUM([BytesOut]) AS [BytesOut],
  14.             SUM([TotalBytes]) AS [TotalBytes],
  15.             SUM([Requests]) AS [Requests],
  16.             [URLCategoryId] AS [URLCategoryId],
  17.             SUM([MalwareIncidents]) AS [MalwareIncidents],
  18.             SUM([MalwareInspectionDuration]) AS [MalwareInspectionDuration],
  19.             [Action] AS [Action],
  20.             @CurrentMonthFirstDay AS [Date]
  21.     INTO #TableOrig
  22.     FROM dbo.tblSiteSummary_Daily
  23.     WHERE (Date >= @CurrentMonthFirstDay) AND
  24.           (Date <= @CurrentMonthLastDay)
  25.     GROUP BY
  26.         [Site],
  27.         [UrlCategoryId],
  28.         [Action],
  29.         CASE
  30.             WHEN [MalwareInspectionDuration] = 0 THEN 0
  31.             ELSE 1
  32.         END
  33.     -- create #table1 empty table copy
  34.     SELECT TOP(0) * INTO #Table1 FROM #TableOrig
  35.     INSERT INTO #Table1
  36.     SELECT * FROM #TableOrig WHERE [MalwareIncidents] > 0
  37.     DECLARE @SQLString1 nvarchar(2000) =
  38.     'SELECT *
  39.     FROM (
  40.         SELECT
  41.             [Site], [BytesIn], [BytesOut], [TotalBytes], [Requests], [URLCategoryId], [MalwareIncidents], [MalwareInspectionDuration], [Action], [Date]
  42.         FROM (
  43.             SELECT
  44.                 [Site], [BytesIn], [BytesOut], [TotalBytes], [Requests], [URLCategoryId], [MalwareIncidents], [MalwareInspectionDuration], [Action], [Date],
  45.                 ROW_NUMBER() OVER (PARTITION BY [Action] ORDER BY '
  46.     DECLARE @SQLString2 nvarchar(2000) =
  47.                     ' DESC) AS rownum
  48.             FROM #TableOrig
  49.             WHERE [Site] != N''{[23124]}'' AND '
  50.     DECLARE @SQLString3 nvarchar(2000) =
  51.             ' > 0 ) t
  52.         WHERE rownum <= 5000
  53.     ) ttt
  54.     WHERE [Site] IS NOT NULL
  55.           AND [MalwareIncidents] = 0
  56.           AND NOT EXISTS (SELECT * FROM #Table1
  57.                           WHERE ttt.[Site] = [Site] AND
  58.                                 ttt.[BytesIn] = [BytesIn] AND
  59.                                 ttt.[BytesOut] = [BytesOut] AND
  60.                                 ttt.[TotalBytes] = [TotalBytes] AND
  61.                                 ttt.[Requests] = [Requests] AND
  62.                                 ttt.[URLCategoryId] = [URLCategoryId] AND
  63.                                 ttt.[MalwareIncidents] = [MalwareIncidents] AND
  64.                                 ttt.[MalwareInspectionDuration] = [MalwareInspectionDuration] AND
  65.                                 ttt.[Action] = [Action]
  66.                                 )'
  67.     DECLARE @SQLString nvarchar(4000)
  68.     DECLARE @OrderString nvarchar(200)
  69.     SET @OrderString = '[Requests]'
  70.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  71.     INSERT INTO #Table1
  72.     EXECUTE(@SQLString)
  73.     SET @OrderString = '[TotalBytes]';
  74.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  75.     INSERT INTO #Table1
  76.     EXECUTE(@SQLString)
  77.     SET @OrderString = '[BytesIn]';
  78.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  79.     INSERT INTO #Table1
  80.     EXECUTE(@SQLString)
  81.     SET @OrderString = '[BytesOut]';
  82.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  83.     INSERT INTO #Table1
  84.     EXECUTE(@SQLString)
  85.     ---- now add tail
  86.     INSERT INTO #TABLE1
  87.     SELECT
  88.         N'{[23124]}' AS [Site],
  89.         SUM([BytesIn]),
  90.         SUM([BytesOut]),
  91.         SUM([TotalBytes]),
  92.         SUM([Requests]),
  93.         ' ' AS [URLCategoryId],
  94.         0 AS [MalwareIncidents],
  95.         SUM([MalwareInspectionDuration]),
  96.         [Action],
  97.         @CurrentMonthFirstDay AS [Date]
  98.     FROM #TableOrig ttt
  99.     WHERE
  100.         [MalwareIncidents] = 0 AND
  101.         NOT EXISTS (SELECT *
  102.                     FROM #Table1
  103.                     WHERE ttt.[Site] = [Site] AND
  104.                         ttt.[BytesIn] = [BytesIn] AND
  105.                         ttt.[BytesOut] = [BytesOut] AND
  106.                         ttt.[TotalBytes] = [TotalBytes] AND
  107.                         ttt.[Requests] = [Requests] AND
  108.                         ttt.[URLCategoryId] = [URLCategoryId] AND
  109.                         ttt.[MalwareIncidents] = [MalwareIncidents] AND
  110.                         ttt.[MalwareInspectionDuration] = [MalwareInspectionDuration] AND
  111.                         ttt.[Action] = [Action])
  112.     GROUP BY
  113.         [Action],
  114.         CASE
  115.             WHEN [MalwareInspectionDuration] = 0 THEN 0
  116.             ELSE 1
  117.         END
  118.     INSERT INTO [tblSiteSummary_Monthly]
  119.     SELECT *
  120.     FROM #TABLE1
  121. -- cleanup dailies
  122.     DELETE FROM [ISA_RS_Db].[dbo].[tblSiteSummary_Daily]
  123.     WHERE (Date < @LastDailyToKeep)
  124. -- cleanup monthlies
  125.     DELETE FROM [ISA_RS_Db].[dbo].[tblSiteSummary_Monthly]
  126.     WHERE (Date < @LastMonthlyToKeep)
  127. END
  128.