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 / 25066 < prev    next >
Text File  |  2010-06-15  |  4KB  |  110 lines
  1. CREATE PROCEDURE [dbo].[ISA_spSiteSummaryTrim]
  2. AS
  3. BEGIN
  4.     SET NOCOUNT ON;
  5.     IF OBJECT_ID ('tblSiteSummary', 'u') IS NULL
  6.         RETURN
  7.     DECLARE @FromDate datetime
  8.     SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
  9.     CREATE TABLE #Table1
  10.     (
  11.         [Site] varchar(255),
  12.         [BytesIn] bigint,
  13.         [BytesOut] bigint,
  14.         [TotalBytes] bigint,
  15.         [Requests] bigint,
  16.         [URLCategoryId] int,
  17.         [MalwareIncidents] bigint,
  18.         [MalwareInspectionDuration] bigint,
  19.         [Action] int,
  20.         [Date] datetime
  21.     )
  22.     INSERT INTO #Table1
  23.     SELECT * FROM [tblSiteSummary] WHERE [MalwareIncidents] > 0
  24.     DECLARE @SQLString1 nvarchar(2000) =
  25.     'SELECT *
  26.     FROM (
  27.         SELECT
  28.             [Site], [BytesIn], [BytesOut], [TotalBytes], [Requests], [URLCategoryId], [MalwareIncidents], [MalwareInspectionDuration], [Action], [Date]
  29.         FROM (
  30.             SELECT
  31.                 [Site], [BytesIn], [BytesOut], [TotalBytes], [Requests], [URLCategoryId], [MalwareIncidents], [MalwareInspectionDuration], [Action], [Date],
  32.                 ROW_NUMBER() OVER (PARTITION BY [Action] ORDER BY '
  33.     DECLARE @SQLString2 nvarchar(2000) =
  34.                     ' DESC) AS rownum
  35.             FROM dbo.[tblSiteSummary]
  36.             WHERE '
  37.     DECLARE @SQLString3 nvarchar(2000) =
  38.             ' > 0 ) t
  39.         WHERE rownum <= 5000
  40.     ) ttt
  41.     WHERE [Site] IS NOT NULL
  42.           AND [MalwareIncidents] = 0
  43.           AND NOT EXISTS (SELECT * FROM #Table1
  44.                           WHERE ttt.[Site] = [Site] AND
  45.                                 ttt.[BytesIn] = [BytesIn] AND
  46.                                 ttt.[BytesOut] = [BytesOut] AND
  47.                                 ttt.[TotalBytes] = [TotalBytes] AND
  48.                                 ttt.[Requests] = [Requests] AND
  49.                                 ttt.[URLCategoryId] = [URLCategoryId] AND
  50.                                 ttt.[MalwareIncidents] = [MalwareIncidents] AND
  51.                                 ttt.[MalwareInspectionDuration] = [MalwareInspectionDuration] AND
  52.                                 ttt.[Action] = [Action]
  53.                                 )'
  54.     DECLARE @SQLString nvarchar(4000)
  55.     DECLARE @OrderString nvarchar(200)
  56.     SET @OrderString = '[Requests]'
  57.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  58.     INSERT INTO #Table1
  59.     EXECUTE(@SQLString)
  60.     SET @OrderString = '[TotalBytes]';
  61.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  62.     INSERT INTO #Table1
  63.     EXECUTE(@SQLString)
  64.     SET @OrderString = '[BytesIn]';
  65.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  66.     INSERT INTO #Table1
  67.     EXECUTE(@SQLString)
  68.     SET @OrderString = '[BytesOut]';
  69.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  70.     INSERT INTO #Table1
  71.     EXECUTE(@SQLString)
  72.     ---- now add tail
  73.     INSERT INTO #TABLE1
  74.     SELECT
  75.         N'{[23124]}' AS [Site],
  76.         SUM([BytesIn]),
  77.         SUM([BytesOut]),
  78.         SUM([TotalBytes]),
  79.         SUM([Requests]),
  80.         ' ' AS [URLCategoryId],
  81.         0 AS [MalwareIncidents],
  82.         SUM([MalwareInspectionDuration]),
  83.         [Action],
  84.         @FromDate
  85.     FROM dbo.[tblSiteSummary] ttt
  86.     WHERE
  87.         [MalwareIncidents] = 0 AND
  88.         NOT EXISTS (SELECT *
  89.                     FROM #Table1
  90.                     WHERE ttt.[Site] = [Site] AND
  91.                         ttt.[BytesIn] = [BytesIn] AND
  92.                         ttt.[BytesOut] = [BytesOut] AND
  93.                         ttt.[TotalBytes] = [TotalBytes] AND
  94.                         ttt.[Requests] = [Requests] AND
  95.                         ttt.[URLCategoryId] = [URLCategoryId] AND
  96.                         ttt.[MalwareIncidents] = [MalwareIncidents] AND
  97.                         ttt.[MalwareInspectionDuration] = [MalwareInspectionDuration] AND
  98.                         ttt.[Action] = [Action])
  99.     GROUP BY
  100.         [Action],
  101.         CASE
  102.             WHEN [MalwareInspectionDuration] = 0 THEN 0
  103.             ELSE 1
  104.         END
  105.     TRUNCATE TABLE [tblSiteSummary]
  106.     INSERT INTO [tblSiteSummary]
  107.     SELECT *
  108.     FROM #TABLE1
  109. END
  110.