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 / 25206 < prev    next >
Text File  |  2010-06-15  |  3KB  |  92 lines
  1. CREATE PROCEDURE [dbo].[ISA_spSoftBlockSummary]
  2. AS
  3. BEGIN
  4.     SET NOCOUNT ON;
  5.     IF OBJECT_ID ('tblSoftBlockSummary', 'u') IS NOT NULL
  6.         DROP TABLE [tblSoftBlockSummary]
  7.     DECLARE @FromDate datetime
  8.     SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
  9.     CREATE TABLE #RowSummaryData
  10.     (
  11.         [Site] varchar(255),
  12.         [URLCategoryId] int,
  13.         [UserName] nvarchar(514)
  14.     )
  15.     IF OBJECT_ID ('WEB_LOGS', 'view') IS NOT NULL
  16.     BEGIN
  17.         INSERT INTO #RowSummaryData
  18.         SELECT
  19.             [DestHost] AS Site,
  20.             [UrlCategory] AS UrlCategoryId,
  21.             CASE
  22.                 --TODO: should factor the following code out to a function (it is used in yet another place)
  23.                 WHEN ([ClientIP] = '00000000-0000-0000-0000-FFFF7F000001') THEN N'-'
  24.                 WHEN [ClientUserName] = N'anonymous' THEN '@\@' + CAST([ClientIP] AS nvarchar(500))
  25.                 WHEN [ClientUserName] = N'-' AND [ClientIP] = '00000000-0000-0000-0000-000000000000' THEN
  26.                     CASE
  27.                         WHEN CHARINDEX('Check Upstream',[ClientAgent])<>0 THEN N'{[23140]}'
  28.                         WHEN CHARINDEX('Member Ping',[ClientAgent])<>0 THEN N'{[23141]}'
  29.                         WHEN CHARINDEX('Fetch API',[ClientAgent])<>0 THEN N'{[23142]}'
  30.                         WHEN CHARINDEX('Delete API',[ClientAgent])<>0 THEN N'{[23143]}'
  31.                         ELSE N'-'
  32.                     END
  33.                 ELSE [ClientUserName]
  34.             END AS UserName
  35.         FROM [WEB_LOGS]
  36.         WHERE [SoftBlockAction] <> '-'
  37.     END
  38.     -- Prepare to trimming
  39.     CREATE TABLE #RequestsCountedData
  40.     (
  41.         [Site] varchar(255),
  42.         [URLCategoryId] int,
  43.         [UserName] nvarchar(514),
  44.         [Requests] bigint
  45.     )
  46.     INSERT INTO #RequestsCountedData
  47.     SELECT DISTINCT
  48.         [Site],
  49.         [URLCategoryId],
  50.         [UserName],
  51.         COUNT(*) AS [Requests]
  52.     FROM #RowSummaryData
  53.     GROUP BY [Site], [URLCategoryId], [UserName]
  54.     -- Trim the result table to the maximum of 10000 records
  55.     SELECT
  56.         [Site],
  57.         [URLCategoryId],
  58.         [UserName],
  59.         [Requests],
  60.         @FromDate AS [Date]
  61.     INTO [tblSoftBlockSummary]
  62.     FROM (
  63.         SELECT
  64.             [Site], [URLCategoryId], [UserName], [Requests]
  65.         FROM
  66.         (
  67.             SELECT
  68.                 [Site], [URLCategoryId], [UserName], [Requests],
  69.                 ROW_NUMBER() OVER (ORDER BY [Requests] DESC) AS rownum
  70.             FROM #RequestsCountedData
  71.         ) TopSites
  72.         WHERE rownum <= 10000
  73.     UNION ALL
  74.         SELECT
  75.             N'{[23124]}' as [Site],
  76.             0 as [URLCategoryId],
  77.             N' ' as [UserName],
  78.             SUM([Requests]) as [Requests]
  79.         FROM
  80.         (
  81.             SELECT
  82.                 [Site], [URLCategoryId], [UserName], [Requests],
  83.                 ROW_NUMBER() OVER (ORDER BY [Requests] DESC) AS rownum
  84.             FROM #RequestsCountedData
  85.         ) OtherSites
  86.         WHERE rownum > 10000
  87.     ) AllSites
  88.     WHERE [Requests] IS NOT NULL
  89.     -- register this summary to be committed to the RS
  90.     INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblSoftBlockSummary')
  91. END
  92.