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 / 25622 < prev    next >
Text File  |  2010-06-15  |  9KB  |  196 lines
  1. CREATE PROCEDURE [dbo].[ISA_spUserActivitySitesRDL]
  2.     @ReportPeriod varchar(16),
  3.     @UserList nvarchar(4000)
  4. AS
  5. BEGIN
  6.     SET NOCOUNT ON;
  7.     BEGIN TRY -- we want to disable OA_procs on exit
  8.     EXEC sp_configure 'show advanced options', 1;
  9.     RECONFIGURE WITH OVERRIDE
  10.     EXEC sp_configure 'Ole Automation Procedures', 1;
  11.     RECONFIGURE WITH OVERRIDE
  12.     DECLARE @Object int = 0;
  13.     DECLARE @HR int = 0;
  14.     DECLARE @Count int = 0;
  15.     DECLARE @Array int, @Log int;
  16.     EXEC @HR = sp_OACreate N'FPC.Root', @Object OUT;
  17.     IF @HR <> 0
  18.         GOTO CLEANUP
  19.     EXEC @HR = sp_OAMethod @Object, N'ConnectToLocalStorage';
  20.     -- get array
  21.     EXEC @HR = sp_OAMethod @Object, N'GetContainingArray', @Array OUT
  22.     IF @HR <> 0
  23.         GOTO CLEANUP
  24.     -- the MSDE log content collection
  25.     EXEC @HR = sp_OAGetProperty @Array, N'LogViewer.LogContentMSDE', @Log OUT;
  26.     IF @HR <> 0
  27.         GOTO CLEANUP
  28.     CREATE TABLE #tblUserActivitySites
  29.     (
  30.         [User] [nvarchar](2048) NULL,
  31.         [UrlCategory] int NULL,
  32.         [Url] [nvarchar](2048) NULL,
  33.         [TotalBytes] bigint
  34.     )
  35.     DECLARE @UserOrIp nvarchar(2048) = N'';
  36.     DECLARE @Curr int = CHARINDEX(N';', @UserList);
  37.     DECLARE @IsIp int = 1;
  38.     WHILE (@Curr <> 0)
  39.     BEGIN
  40. RETRY:
  41.         --------------------------------------------------------------------------
  42.         -- create filter
  43.         --------------------------------------------------------------------------
  44.         -- create an FPCFilterExpressions collection
  45.         DECLARE @Filter int = 0;
  46.         EXEC @HR = sp_OACreate N'FPC.FPCFilterExpressions', @Filter OUT;
  47.         IF @HR <> 0
  48.             GOTO CLEANUP
  49.         -- set filter type
  50.         EXEC @HR = sp_OASetProperty @Filter, N'FilterType', 2; -- 2 == fpcLogViewerFilter
  51.         IF @HR <> 0
  52.             GOTO CLEANUP
  53.         --------------------------------------------------------------------------
  54.         -- configure filter
  55.         --------------------------------------------------------------------------
  56.         -- filter.AddEnumFilter fpcFilterByLogType == 23, fpcEqual == 1, fpcProxyWebLog == 1
  57.         EXEC @HR = sp_OAMethod @Filter, N'AddEnumFilter', @Object OUT, 23, 1, 1;
  58.         IF @HR <> 0
  59.             GOTO CLEANUP
  60.         --------------------------------------------------------------------------
  61.         -- convert start time to OLE DATE format
  62.         --------------------------------------------------------------------------
  63.         DECLARE @Now DATETIME = GETDATE();
  64.         DECLARE @StartTime DATETIME =
  65.         CASE @ReportPeriod
  66.             WHEN 'fpcLastHour' THEN DATEADD(hour, -1, @Now)
  67.             WHEN 'fpcLast24Hours' THEN DATEADD(hour, -24, @Now)
  68.             WHEN 'fpcLast7days' THEN DATEADD(day, -7, @Now)
  69.             WHEN 'fpcLast30days' THEN DATEADD(day, -30, @Now)
  70.         END;
  71.         -- the OLE DATE type's start point is here; (121 = ODBC canonical datetime style, with milliseconds)
  72.         DECLARE @DateBase DATETIME = CONVERT(DATETIME, '1899-12-30 00:00:00.000', 121);
  73.         DECLARE @StartOleDate float(53) = CAST(DATEDIFF(day, @DateBase, @StartTime) AS float(53));
  74.         SET @StartOleDate = @StartOleDate + CAST(DATEPART(hour, @StartTime) AS float(53)) / 24.0;
  75.         SET @StartOleDate = @StartOleDate + CAST(DATEPART(minute, @StartTime) AS float(53)) / (24.0 * 60.0);
  76.         SET @StartOleDate = @StartOleDate + CAST(DATEPART(second, @StartTime) AS float(53)) / (24.0 * 60.0 * 60.0);
  77.         -- filter.AddEnumFilter fpcFilterByLogTime == 18, fpcOnOrLater == 16, StartOleDate
  78.         EXEC @HR = sp_OAMethod @Filter, N'AddDateFilter', @Object OUT, 18, 16, @StartOleDate;
  79.         IF @HR <> 0
  80.             GOTO CLEANUP
  81.         SET @UserOrIp = SUBSTRING(@UserList, 1, @Curr - 1); -- cut off the separator (';')
  82.         IF @IsIp = 1
  83.          -- filter.AddIPAddressFilter fpcFilterByClientIP == 1, fpcEqual == 1, <ip>
  84.             EXEC @HR = sp_OAMethod @Filter, N'AddIPAddressFilter', @Object OUT, 1, 1, @UserOrIp;
  85.         ELSE
  86.             -- filter.AddStringFilter fpcFilterByClientUserName == 2 , fpcEqual == 1, <user>
  87.             EXEC @HR = sp_OAMethod @Filter, N'AddStringFilter', @Object OUT, 2, 1, @UserOrIp;
  88.         SET @IsIp = 1;
  89.         IF @HR <> 0
  90.             GOTO CLEANUP
  91.         -- execute query
  92.         EXEC @HR = sp_OAMethod @Log, N'ExecuteQuery', NULL, @Filter, 10000; -- max. number of records to fetch
  93.         IF @HR = -2147024809 -- invalid parameter
  94.         BEGIN
  95.             -- it's not an ip, let's try treting this substring as a name
  96.             EXEC @Count = sp_OADestroy @Filter;
  97.             SET @IsIp = 0;
  98.             GOTO RETRY
  99.         END
  100.         IF @HR <> 0
  101.             GOTO CLEANUP
  102.         DECLARE @EntryIdx int = 1;
  103.         WHILE (@HR <> -2147024894) -- not found
  104.         BEGIN
  105.             DECLARE @Entry int = 0;
  106.             DECLARE @Url nvarchar(2048) = N'';
  107.             DECLARE @UrlCategory int = -1;
  108.             DECLARE @BytesIn bigint = 0;
  109.             DECLARE @BytesOut bigint = 0;
  110.             EXEC @HR = sp_OAMethod @Log, N'ItemAsync', @Entry OUT, @EntryIdx;
  111.             IF @HR = -2147483638 -- pending
  112.             BEGIN
  113.                 WAITFOR DELAY '00:00:00.100' -- sleep for 100 ms
  114.                 CONTINUE
  115.             END
  116.             IF @HR = -2147024894 -- not found
  117.             BEGIN
  118.                 SET @HR = 0
  119.                 BREAK
  120.             END
  121.             IF @HR <> 0
  122.                 GOTO CLEANUP
  123.             EXEC @HR = sp_OAGetProperty @Entry, 'UrlDestHost', @Url OUT;
  124.             IF @HR <> 0
  125.                 GOTO CLEANUP
  126.             EXEC @HR = sp_OAGetProperty @Entry, 'UrlCategory', @UrlCategory OUT;
  127.             IF @HR <> 0
  128.                 GOTO CLEANUP
  129.             EXEC @HR = sp_OAGetProperty @Entry, 'BytesReceived', @BytesIn OUT;
  130.             IF @HR <> 0
  131.                 GOTO CLEANUP
  132.             EXEC @HR = sp_OAGetProperty @Entry, 'BytesSent', @BytesOut OUT;
  133.             IF @HR <> 0
  134.                 GOTO CLEANUP
  135.             INSERT INTO #tblUserActivitySites([User],[UrlCategory],[Url],[TotalBytes])
  136.             VALUES (@UserOrIp, @UrlCategory, @Url, @BytesIn + @BytesOut)
  137.             EXEC @Count = sp_OADestroy @Entry;
  138.             SET @EntryIdx = @EntryIdx + 1
  139.         END
  140.         EXEC @Count = sp_OADestroy @Filter;
  141.         SET @UserList = SUBSTRING(@UserList, @Curr + 1, LEN(@UserList))
  142.         SET @Curr = CHARINDEX(N';', @UserList)
  143.     END
  144. CLEANUP:
  145.     -- Check whether an error occurred.
  146.     IF (@HR <> 0)
  147.     BEGIN
  148.         -- Report the error.
  149.         DECLARE @Source nvarchar(255), @Desc nvarchar(255);
  150.         EXEC sp_OAGetErrorInfo @Object, @Source OUT, @Desc OUT;
  151.         PRINT 'Error: HR          = '
  152.         PRINT convert(varbinary(4),@HR)
  153.         PRINT 'Error: Source      = ' + @Source
  154.         PRINT 'Error: Description = ' + @Desc
  155.     END
  156.     EXEC @Count = sp_OADestroy @Object;
  157.     EXEC sp_configure 'Ole Automation Procedures', 0;
  158.     RECONFIGURE WITH OVERRIDE
  159.     EXEC sp_configure 'show advanced options', 0;
  160.     RECONFIGURE WITH OVERRIDE
  161.     END TRY
  162.     BEGIN CATCH
  163.         EXEC sp_configure 'Ole Automation Procedures', 0;
  164.         RECONFIGURE WITH OVERRIDE
  165.         EXEC sp_configure 'show advanced options', 0;
  166.         RECONFIGURE WITH OVERRIDE
  167.         PRINT ERROR_MESSAGE()
  168.         DECLARE
  169.             @ErrorMessage    NVARCHAR(4000),
  170.             @ErrorNumber     INT,
  171.             @ErrorSeverity   INT,
  172.             @ErrorState      INT,
  173.             @ErrorLine       INT,
  174.             @ErrorProcedure  NVARCHAR(200);
  175.         SELECT
  176.             @ErrorMessage = ERROR_MESSAGE(),
  177.             @ErrorNumber = ERROR_NUMBER(),
  178.             @ErrorSeverity = ERROR_SEVERITY(),
  179.             @ErrorState = ERROR_STATE(),
  180.             @ErrorLine = ERROR_LINE(),
  181.             @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
  182.         RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine);
  183.     END CATCH
  184.     IF (@HR <> 0)
  185.     BEGIN
  186.         SET @Desc = @Desc + ' , HR returned = ' + CONVERT(varchar(50),@HR)
  187.         RAISERROR (@Desc,10,1)
  188.         SET @HR = 1/0 -- raise exception
  189.     END
  190.     SELECT DISTINCT p.[User] AS [User], q.[UrlCategoryName] AS [UrlCategory], p.[Url] AS [Url], SUM(p.[TotalBytes]) AS [TotalBytes]
  191.     FROM #tblUserActivitySites AS p INNER JOIN [dbo].[tblUrlfMetadata] AS q ON p.[UrlCategory] = q.[URLCategoryId]
  192.     WHERE p.[UrlCategory] = q.[URLCategoryId]
  193.     GROUP BY p.[User], q.[UrlCategoryName], p.[Url]
  194.     ORDER BY [User], [UrlCategory], [TotalBytes] DESC
  195. END
  196.