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 / 25578 < prev    next >
Text File  |  2010-06-15  |  3KB  |  88 lines
  1. CREATE PROCEDURE [dbo].[ISA_spIpsTopIntrusionsRDL]
  2.     @FromDate datetime,
  3.     @ToDate datetime,
  4.     @ReportType varchar(10),
  5.     @TopCount int
  6. AS
  7. BEGIN
  8.     DECLARE @SummTableName varchar(100)
  9.     SET @SummTableName = dbo.fnGetSummaryTableName('tblIpsSummary', @FromDate, @ToDate, @ReportType)
  10.     CREATE TABLE #Table1
  11.     (
  12.         [SignatureName] nvarchar(4000),
  13.         [Incidents] bigint,
  14.         [BlockedIncidents] bigint,
  15.         [AllowedIncidents] bigint
  16.     )
  17.     IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
  18.     BEGIN
  19.         DECLARE @strQuery varchar(8000)
  20.         SET @strQuery =
  21.         'SELECT
  22.             [SignatureName],
  23.             SUM([Incidents]) AS [Incidents],
  24.             CASE
  25.                  WHEN ([Action] = 2) Then SUM([Incidents])
  26.                  ELSE 0
  27.             END AS BlockedIncidents,
  28.             CASE
  29.                  WHEN ([Action] = 3) Then SUM([Incidents])
  30.                  ELSE 0
  31.             END AS AllowedIncidents
  32.         FROM ' + @SummTableName + '
  33.         WHERE
  34.             ([Date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [Date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
  35.         GROUP BY
  36.             [SignatureName],
  37.             [Action]
  38.         '
  39.         INSERT #Table1
  40.         EXEC(@strQuery)
  41.     END
  42.     CREATE TABLE #Table2
  43.     (
  44.         [SignatureName] nvarchar(4000),
  45.         [Incidents] bigint,
  46.         [BlockedIncidents] bigint,
  47.         [AllowedIncidents] bigint
  48.     )
  49.     INSERT INTO #Table2
  50.     SELECT
  51.         [SignatureName] AS [SignatureName],
  52.         SUM([Incidents]) AS [Incidents],
  53.         SUM([BlockedIncidents]) AS [BlockedIncidents],
  54.         SUM([AllowedIncidents]) AS [AllowedIncidents]
  55.     FROM #Table1
  56.     WHERE ([Incidents] > 0)
  57.     GROUP BY [SignatureName]
  58.     ORDER BY SUM([Incidents]) DESC
  59.     DECLARE @TotalIncidents bigint
  60.     SET @TotalIncidents = (SELECT SUM([Incidents]) FROM #Table2)
  61.     IF (@TotalIncidents = 0)
  62.         SET @TotalIncidents = 1
  63.     SELECT TOP(@TopCount)
  64.         o.[SignatureName] AS [SignatureName],
  65.         oo.[SignatureDescription] AS [SignatureDescription],
  66.         o.[Incidents] AS [Incidents],
  67.         (CAST(o.[Incidents] AS decimal) / @TotalIncidents) AS [IncidentRatio],
  68.         o.[BlockedIncidents] AS [BlockedIncidents],
  69.         o.[AllowedIncidents] AS [AllowedIncidents]
  70.     FROM #Table2 AS o
  71.     LEFT JOIN [tblIpsMetadata] AS oo
  72.        ON o.[SignatureName] = oo.[SignatureName]
  73.     UNION ALL
  74.     SELECT
  75.         N'{[23124]}' As [SignatureName],
  76.         N' ' As [SignatureDescription],
  77.         SUM([Incidents]) AS [Incidents],
  78.         (CAST(SUM([Incidents]) AS decimal) / @TotalIncidents) AS [IncidentRatio],
  79.         SUM([BlockedIncidents]) AS [BlockedIncidents],
  80.         SUM([AllowedIncidents]) AS [AllowedIncidents]
  81.     FROM #Table2 t1
  82.     WHERE NOT EXISTS
  83.         (SELECT * FROM
  84.            (SELECT TOP(@TopCount) *
  85.             FROM #Table2) t2
  86.             WHERE t1.[SignatureName] = t2.[SignatureName])
  87. END
  88.