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 / 25131 < prev    next >
Text File  |  2010-06-15  |  2KB  |  78 lines
  1. CREATE PROCEDURE [dbo].[ISA_spMalwareSummary]
  2. AS
  3. BEGIN
  4.     SET NOCOUNT ON;
  5.     IF OBJECT_ID ('tblMalwareSummary', 'u') IS NOT NULL
  6.         DROP TABLE [tblMalwareSummary]
  7.     DECLARE @FromDate datetime
  8.     SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
  9.     DECLARE @LogServerName nvarchar(32)
  10.     SET @LogServerName = dbo.fnGetLogsServerName();
  11.     CREATE TABLE #table1
  12.     (
  13.         [ThreatName] varchar(255),
  14.         [ThreatLevel] int,
  15.         [LogTime] datetime
  16.     )
  17.     CREATE TABLE #table2
  18.     (
  19.         [ThreatName] varchar(255),
  20.         [ThreatLevel] int,
  21.     )
  22.     CREATE TABLE #table3
  23.     (
  24.         [ThreatName] varchar(255),
  25.         Incidents bigint,
  26.         [MalwareInspectionAction] int
  27.     )
  28.     IF OBJECT_ID ('WEB_LOGS', 'view') IS NOT NULL
  29.     BEGIN
  30.         INSERT INTO #table1
  31.         SELECT
  32.             [ThreatName] AS ThreatName,
  33.             [MalwareInspectionThreatLevel] AS ThreatLevel,
  34.             MAX([logTime]) AS LogTime
  35.         FROM WEB_LOGS
  36.         WHERE
  37.             ([InternalServiceInfo] & 2) = 0 AND
  38.             [ThreatName] <> '-'
  39.         GROUP BY
  40.             [ThreatName],
  41.             [MalwareInspectionThreatLevel]
  42.         INSERT INTO #table2
  43.         SELECT
  44.               [ThreatName],
  45.               (SELECT TOP(1) ThreatLevel
  46.                FROM #table1 q
  47.                WHERE q.[ThreatName] = p.[ThreatName]
  48.                ORDER BY [logTime] DESC, [ThreatLevel] DESC
  49.               )AS [ThreatLevel]
  50.         FROM #table1 p
  51.         GROUP BY [ThreatName]
  52.         INSERT INTO #table3
  53.         SELECT
  54.             [ThreatName] AS ThreatName,
  55.             COUNT(*) as Incidents,
  56.             [MalwareInspectionAction]
  57.         FROM WEB_LOGS
  58.         WHERE
  59.             ([InternalServiceInfo] & 2) = 0 AND
  60.             [ThreatName] <> '-'
  61.         GROUP BY
  62.             [ThreatName],
  63.             [MalwareInspectionAction]
  64.     END
  65.     SELECT
  66.         o.[ThreatName] AS ThreatName,
  67.         r.[ThreatLevel] AS ThreatLevel,
  68.         o.[Incidents] AS [Incidents],
  69.         o.[MalwareInspectionAction] AS [MalwareInspectionAction],
  70.         @FromDate AS Date,
  71.         @LogServerName AS ServerName
  72.     INTO [tblMalwareSummary]
  73.     FROM #table3 o
  74.     INNER JOIN #table2 r
  75.        ON o.[ThreatName] = r.[ThreatName]
  76.     INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblMalwareSummary')
  77. END
  78.