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 / 25061 < prev    next >
Text File  |  2010-06-15  |  3KB  |  90 lines
  1. CREATE PROCEDURE [dbo].[ISA_spSiteSummary]
  2. AS
  3. BEGIN
  4.     SET NOCOUNT ON;
  5.     IF OBJECT_ID ('tblSiteSummary', 'u') IS NOT NULL
  6.         DROP TABLE [tblSiteSummary]
  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.     )
  21.     IF OBJECT_ID ('WEB_LOGS', 'view') IS NOT NULL
  22.     BEGIN
  23.         INSERT INTO #table1
  24.         SELECT
  25.             [DestHost] AS Site,
  26.             SUM([bytesrecvd]) AS BytesIn,
  27.             SUM([bytessent]) AS BytesOut,
  28.             SUM([bytesrecvd]) + SUM([bytessent]) AS TotalBytes,
  29.             COUNT(*) AS Requests,
  30.             [UrlCategory] AS [URLCategoryId],
  31.             CASE
  32.               WHEN ([ThreatName] = '-') THEN 0
  33.               ELSE COUNT(*)
  34.             END AS MalwareIncidents,
  35.             SUM(CAST([MalwareInspectionDuration] AS bigint)) AS [MalwareInspectionDuration],
  36.             CASE
  37.                 WHEN (([Action] = 8) OR ([resultcode] = 13301)) AND
  38.                      ([InternalServiceInfo] & 1 <> 0)
  39.                                                                                     THEN 4
  40.                 WHEN (([Action] = 8) OR ([resultcode] = 13301))
  41.                                                                                     THEN 1
  42.                 WHEN ([Action] = 10)                   THEN 2
  43.                 WHEN ([objectsource] = 6)   THEN 3
  44.                 ELSE 0
  45.             END AS Action
  46.         FROM WEB_LOGS
  47.         WHERE
  48.             ([InternalServiceInfo] & 2) = 0
  49.         GROUP BY
  50.             [DestHost],
  51.             [ThreatName],
  52.             [UrlCategory],
  53.             CASE
  54.                 WHEN (([Action] = 8) OR ([resultcode] = 13301)) AND
  55.                      ([InternalServiceInfo] & 1 <> 0)
  56.                                                                                     THEN 4
  57.                 WHEN (([Action] = 8) OR ([resultcode] = 13301))
  58.                                                                                     THEN 1
  59.                 WHEN ([Action] = 10)                   THEN 2
  60.                 WHEN ([objectsource] = 6)   THEN 3
  61.                 ELSE 0
  62.             END,
  63.             CASE
  64.                 WHEN [MalwareInspectionDuration] = 0 THEN 0
  65.                 ELSE 1
  66.             END
  67.     END
  68.     SELECT [Site],
  69.         SUM([BytesIn]) AS BytesIn,
  70.         SUM([BytesOut]) AS BytesOut,
  71.         SUM([TotalBytes]) AS TotalBytes,
  72.         SUM([Requests]) AS Requests,
  73.         [UrlCategoryId] AS [UrlCategoryId],
  74.         SUM([MalwareIncidents]) AS MalwareIncidents,
  75.         SUM([MalwareInspectionDuration]) AS [MalwareInspectionDuration],
  76.         [Action],
  77.         @FromDate AS Date
  78.     INTO [tblSiteSummary]
  79.     FROM #Table1
  80.     GROUP BY
  81.         [Site],
  82.         [UrlCategoryId],
  83.         [Action],
  84.         CASE
  85.             WHEN [MalwareInspectionDuration] = 0 THEN 0
  86.             ELSE 1
  87.         END
  88.     INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblSiteSummary')
  89. END
  90.