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 / 25051 < prev    next >
Text File  |  2010-06-15  |  3KB  |  87 lines
  1. CREATE PROCEDURE [ISA_spUsageSummary]
  2. AS
  3. BEGIN
  4.     SET NOCOUNT ON;
  5.     IF OBJECT_ID ('tblUsageSummary', 'u') IS NOT NULL
  6.         DROP TABLE [tblUsageSummary]
  7.     DECLARE @FromDate datetime
  8.     SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
  9.     CREATE TABLE #Table1
  10.     (
  11.         iHour int,
  12.         CacheHits bigint,
  13.         ProcessingTime bigint,
  14.         TotalBytes bigint,
  15.         Requests bigint,
  16.         WebRequests bigint
  17.     );
  18.     DECLARE @CurrentDate datetime
  19.     SET @CurrentDate = @FromDate
  20.     SET @CurrentDate = DATEADD(hour, -1 * DATEPART(hour, @CurrentDate), @CurrentDate)
  21.     SET @CurrentDate = DATEADD(minute, -1 * DATEPART(minute, @CurrentDate), @CurrentDate)
  22.     SET @CurrentDate = DATEADD(second, -1 * DATEPART(second, @CurrentDate), @CurrentDate)
  23.     SET @CurrentDate = DATEADD(millisecond, -1 * DATEPART(millisecond, @CurrentDate), @CurrentDate)
  24.     IF OBJECT_ID ('WEB_LOGS', 'view') IS NOT NULL
  25.     BEGIN
  26.         INSERT INTO #Table1
  27.         SELECT
  28.             DATEPART(hour, [logTime]) AS iHour,
  29.             CASE
  30.                 WHEN ([objectsource] IN (1, 2, 3, 4, 8)) THEN COUNT(*)
  31.                 ELSE 0
  32.             END AS CacheHits,
  33.             SUM(CAST([processingtime] AS bigint)) AS ProcessingTime,
  34.             SUM([bytesrecvd]) + SUM([bytessent]) AS TotalBytes,
  35.             COUNT(*) AS Requests,
  36.             COUNT(*) AS WebRequests
  37.         FROM WEB_LOGS
  38.         WHERE
  39.             ([InternalServiceInfo] & 2) = 0 AND
  40.             ([Action] != 8) AND
  41.             ([resultcode] != 13301) AND
  42.             ([Action] != 10) AND
  43.             ([objectsource] != 6)
  44.         GROUP BY
  45.             DATEPART(hour, [logTime]),
  46.             DATEPART(minute, [logTime]),
  47.             [objectsource]
  48.     END
  49.     IF OBJECT_ID ('FWS_LOGS', 'view') IS NOT NULL
  50.     BEGIN
  51.         INSERT INTO #Table1
  52.         SELECT
  53.             DATEPART(hour, [logTime]) AS iHour,
  54.             0 AS CacheHits,
  55.             SUM(CAST([connectiontimeDelta] AS bigint)) AS ProcessingTime,
  56.             SUM([bytesrecvdDelta]) + SUM([bytessentDelta]) AS TotalBytes,
  57.             CASE
  58.                 WHEN ([Action] = 11) THEN 0
  59.                 ELSE COUNT(*)
  60.             END AS Requests,
  61.             0 AS WebRequests
  62.         FROM FWS_LOGS
  63.         WHERE
  64.             ([InternalServiceInfo] & 2) = 0 AND
  65.             ([Action] != 8) AND
  66.             ([Action] != 10)
  67.         GROUP BY
  68.             DATEPART(hour, [logTime]),
  69.             DATEPART(minute, [logTime]),
  70.             [Action];
  71.     END
  72.     SELECT
  73.         DATEADD(hour, [iHour] , @CurrentDate) AS Interval,
  74.         SUM([CacheHits]) AS [CacheHits],
  75.         SUM([processingtime]) AS ProcessingTime,
  76.         SUM([TotalBytes]) AS TotalBytes,
  77.         SUM([Requests]) AS Requests,
  78.         SUM([WebRequests]) AS WebRequests,
  79.         MAX([Requests]) AS MaxConcurrentConnectionsPerMinute,
  80.         @FromDate AS Date
  81.     INTO [tblUsageSummary]
  82.     FROM #Table1
  83.     GROUP BY
  84.         [iHour]
  85.     INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblUsageSummary')
  86. END
  87.