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 / 25133 < prev    next >
Text File  |  2010-06-15  |  2KB  |  75 lines
  1. CREATE PROCEDURE [dbo].[ISA_spMalwareSummaryMonthly] 
  2.     @CurrentMonthFirstDay datetime,
  3.     @CurrentMonthLastDay datetime,
  4.     @LastDailyToKeep datetime,
  5.     @LastMonthlyToKeep datetime
  6. AS
  7. BEGIN
  8.     SET NOCOUNT ON;
  9.  
  10.     CREATE TABLE #table1
  11.     (
  12.         [ThreatName] varchar(255),
  13.         [ThreatLevel] int,
  14.         [LogTime] datetime
  15.     ) 
  16.  
  17.     CREATE TABLE #table2
  18.     (
  19.         [ThreatName] varchar(255),
  20.         [ThreatLevel] int,
  21.     ) 
  22.  
  23.     INSERT INTO #table1
  24.     SELECT 
  25.         [ThreatName] AS ThreatName,
  26.         [ThreatLevel] AS ThreatLevel,
  27.         MAX([Date]) AS LogTime
  28.     FROM dbo.tblMalwareSummary_Daily
  29.     WHERE (Date >= @CurrentMonthFirstDay) AND
  30.           (Date <= @CurrentMonthLastDay)
  31.     GROUP BY 
  32.         [ThreatName],
  33.         [ThreatLevel]
  34.  
  35.     INSERT INTO #table2
  36.     SELECT 
  37.           [ThreatName], 
  38.           (SELECT TOP(1) ThreatLevel
  39.            FROM #table1 q
  40.            WHERE q.[ThreatName] = p.[ThreatName]
  41.            ORDER BY [logTime] DESC, [ThreatLevel] DESC
  42.           ) AS [ThreatLevel]
  43.     FROM #table1 p
  44.     GROUP BY [ThreatName]
  45.  
  46. -- move dailies to monthlies
  47.     INSERT INTO dbo.tblMalwareSummary_Monthly
  48.     SELECT o.[ThreatName],
  49.            p.[ThreatLevel],
  50.            o.[Incidents],
  51.            o.[MalwareInspectionAction],
  52.            @CurrentMonthFirstDay AS Date,
  53.            '-' As [ServerName]
  54.     FROM
  55.         (SELECT [ThreatName] AS [ThreatName]
  56.               ,SUM([Incidents]) AS [Incidents]
  57.               ,[MalwareInspectionAction] AS [MalwareInspectionAction]
  58.         FROM dbo.tblMalwareSummary_Daily
  59.         WHERE (Date >= @CurrentMonthFirstDay) AND
  60.               (Date <= @CurrentMonthLastDay)
  61.         GROUP BY
  62.                 [ThreatName],
  63.                 [MalwareInspectionAction]) AS o
  64.     INNER JOIN #table2 p
  65.         ON p.[ThreatName] = o.[ThreatName]
  66. -- cleanup dailies
  67.     DELETE FROM [ISA_RS_Db].[dbo].[tblMalwareSummary_Daily]
  68.     WHERE (Date < @LastDailyToKeep)
  69.  
  70. -- cleanup monthlies
  71.     DELETE FROM [ISA_RS_Db].[dbo].[tblMalwareSummary_Monthly]
  72.     WHERE (Date < @LastMonthlyToKeep)
  73.  
  74. END
  75.