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/25502

Text File | 2010-06-15 | 3KB | 101 lines

CREATE PROCEDURE [dbo].[ISA_spMalwareThreatsRDL]
    @FromDate datetime,
    @ToDate datetime,
    @ReportType varchar(10),
    @TopCount int
AS
BEGIN
    DECLARE @SummTableName varchar(100)
    SET @SummTableName = dbo.fnGetSummaryTableName('tblMalwareSummary', @FromDate, @ToDate, @ReportType)
    CREATE TABLE #Table1
    (
        ThreatName varchar(255),
        ThreatLevel int,
        Incidents bigint
    );
    CREATE TABLE #table3
    (
        [ThreatName] varchar(255),
        [ThreatLevel] int,
    )
    IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
    BEGIN
        DECLARE @strQuery varchar(8000)
        SET @strQuery =
        'SELECT
            [ThreatName] AS [ThreatName],
            [ThreatLevel] AS [ThreatLevel],
            MAX([date]) AS [date]
        FROM ' + @SummTableName + '
        WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
        GROUP BY [ThreatName],
                 [ThreatLevel]
        '
        CREATE TABLE #Table2
        (
            ThreatName varchar(255),
            ThreatLevel int,
            date datetime
        );
        INSERT #Table2
        EXEC(@strQuery)
        INSERT INTO #table3
        SELECT
              [ThreatName],
              (SELECT TOP(1) ThreatLevel
               FROM #table2 q
               WHERE q.[ThreatName] = p.[ThreatName]
               ORDER BY [date] DESC, [ThreatLevel] DESC
              )AS [ThreatLevel]
        FROM #table2 p
        GROUP BY [ThreatName]
        SET @strQuery =
        'SELECT o.[ThreatName],
                p.[ThreatLevel],
                o.[Incidents]
         FROM
            (SELECT
                [ThreatName] AS [ThreatName],
                SUM([Incidents]) AS [Incidents]
            FROM ' + @SummTableName + '
            WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
                AND ([ThreatName] != ' + QUOTENAME('-',CHAR(39)) + ')
            GROUP BY [ThreatName]) o
        INNER JOIN #table3 p
           ON o.[ThreatName] = p.[ThreatName]
        ORDER BY [Incidents] DESC
        '
        INSERT #Table1
        EXEC(@strQuery)
    END
    DECLARE @TotalIncidents bigint
    SET @TotalIncidents = (SELECT SUM([Incidents]) FROM #Table1 )
    IF (@TotalIncidents = 0)
        SET @TotalIncidents = 1
    SELECT TOP(@TopCount)
        [ThreatName],
        CASE
            WHEN [ThreatLevel] = 0 THEN N'{[25175]}'
            WHEN [ThreatLevel] = 1 THEN N'{[25176]}'
            WHEN [ThreatLevel] = 2 THEN N'{[25177]}'
            WHEN [ThreatLevel] = 3 THEN N'{[25178]}'
            WHEN [ThreatLevel] = 4 THEN N'{[25179]}'
            ELSE N'{[25180]}'
        END AS [ThreatLevel],
        [Incidents]  AS Incidents,
        (CAST([Incidents] AS decimal) / @TotalIncidents) AS IncidentRatio
    FROM #Table1
    UNION ALL
    SELECT
        N'{[23124]}' As ThreatName,
        N' ' AS [ThreatLevel],
        SUM([Incidents]) AS Incidents,
        (CAST(SUM([Incidents]) AS decimal) / @TotalIncidents) AS IncidentRatio
    FROM #Table1 t1
    WHERE NOT EXISTS
        (SELECT * FROM
           (SELECT TOP(@TopCount) *
            FROM #Table1) t2
            WHERE t1.ThreatName = t2.ThreatName)
END