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 >
Wrap
Text File
|
2010-06-15
|
9KB
|
196 lines
CREATE PROCEDURE [dbo].[ISA_spUserActivitySitesRDL]
@ReportPeriod varchar(16),
@UserList nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY -- we want to disable OA_procs on exit
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE WITH OVERRIDE
DECLARE @Object int = 0;
DECLARE @HR int = 0;
DECLARE @Count int = 0;
DECLARE @Array int, @Log int;
EXEC @HR = sp_OACreate N'FPC.Root', @Object OUT;
IF @HR <> 0
GOTO CLEANUP
EXEC @HR = sp_OAMethod @Object, N'ConnectToLocalStorage';
-- get array
EXEC @HR = sp_OAMethod @Object, N'GetContainingArray', @Array OUT
IF @HR <> 0
GOTO CLEANUP
-- the MSDE log content collection
EXEC @HR = sp_OAGetProperty @Array, N'LogViewer.LogContentMSDE', @Log OUT;
IF @HR <> 0
GOTO CLEANUP
CREATE TABLE #tblUserActivitySites
(
[User] [nvarchar](2048) NULL,
[UrlCategory] int NULL,
[Url] [nvarchar](2048) NULL,
[TotalBytes] bigint
)
DECLARE @UserOrIp nvarchar(2048) = N'';
DECLARE @Curr int = CHARINDEX(N';', @UserList);
DECLARE @IsIp int = 1;
WHILE (@Curr <> 0)
BEGIN
RETRY:
--------------------------------------------------------------------------
-- create filter
--------------------------------------------------------------------------
-- create an FPCFilterExpressions collection
DECLARE @Filter int = 0;
EXEC @HR = sp_OACreate N'FPC.FPCFilterExpressions', @Filter OUT;
IF @HR <> 0
GOTO CLEANUP
-- set filter type
EXEC @HR = sp_OASetProperty @Filter, N'FilterType', 2; -- 2 == fpcLogViewerFilter
IF @HR <> 0
GOTO CLEANUP
--------------------------------------------------------------------------
-- configure filter
--------------------------------------------------------------------------
-- filter.AddEnumFilter fpcFilterByLogType == 23, fpcEqual == 1, fpcProxyWebLog == 1
EXEC @HR = sp_OAMethod @Filter, N'AddEnumFilter', @Object OUT, 23, 1, 1;
IF @HR <> 0
GOTO CLEANUP
--------------------------------------------------------------------------
-- convert start time to OLE DATE format
--------------------------------------------------------------------------
DECLARE @Now DATETIME = GETDATE();
DECLARE @StartTime DATETIME =
CASE @ReportPeriod
WHEN 'fpcLastHour' THEN DATEADD(hour, -1, @Now)
WHEN 'fpcLast24Hours' THEN DATEADD(hour, -24, @Now)
WHEN 'fpcLast7days' THEN DATEADD(day, -7, @Now)
WHEN 'fpcLast30days' THEN DATEADD(day, -30, @Now)
END;
-- the OLE DATE type's start point is here; (121 = ODBC canonical datetime style, with milliseconds)
DECLARE @DateBase DATETIME = CONVERT(DATETIME, '1899-12-30 00:00:00.000', 121);
DECLARE @StartOleDate float(53) = CAST(DATEDIFF(day, @DateBase, @StartTime) AS float(53));
SET @StartOleDate = @StartOleDate + CAST(DATEPART(hour, @StartTime) AS float(53)) / 24.0;
SET @StartOleDate = @StartOleDate + CAST(DATEPART(minute, @StartTime) AS float(53)) / (24.0 * 60.0);
SET @StartOleDate = @StartOleDate + CAST(DATEPART(second, @StartTime) AS float(53)) / (24.0 * 60.0 * 60.0);
-- filter.AddEnumFilter fpcFilterByLogTime == 18, fpcOnOrLater == 16, StartOleDate
EXEC @HR = sp_OAMethod @Filter, N'AddDateFilter', @Object OUT, 18, 16, @StartOleDate;
IF @HR <> 0
GOTO CLEANUP
SET @UserOrIp = SUBSTRING(@UserList, 1, @Curr - 1); -- cut off the separator (';')
IF @IsIp = 1
-- filter.AddIPAddressFilter fpcFilterByClientIP == 1, fpcEqual == 1, <ip>
EXEC @HR = sp_OAMethod @Filter, N'AddIPAddressFilter', @Object OUT, 1, 1, @UserOrIp;
ELSE
-- filter.AddStringFilter fpcFilterByClientUserName == 2 , fpcEqual == 1, <user>
EXEC @HR = sp_OAMethod @Filter, N'AddStringFilter', @Object OUT, 2, 1, @UserOrIp;
SET @IsIp = 1;
IF @HR <> 0
GOTO CLEANUP
-- execute query
EXEC @HR = sp_OAMethod @Log, N'ExecuteQuery', NULL, @Filter, 10000; -- max. number of records to fetch
IF @HR = -2147024809 -- invalid parameter
BEGIN
-- it's not an ip, let's try treting this substring as a name
EXEC @Count = sp_OADestroy @Filter;
SET @IsIp = 0;
GOTO RETRY
END
IF @HR <> 0
GOTO CLEANUP
DECLARE @EntryIdx int = 1;
WHILE (@HR <> -2147024894) -- not found
BEGIN
DECLARE @Entry int = 0;
DECLARE @Url nvarchar(2048) = N'';
DECLARE @UrlCategory int = -1;
DECLARE @BytesIn bigint = 0;
DECLARE @BytesOut bigint = 0;
EXEC @HR = sp_OAMethod @Log, N'ItemAsync', @Entry OUT, @EntryIdx;
IF @HR = -2147483638 -- pending
BEGIN
WAITFOR DELAY '00:00:00.100' -- sleep for 100 ms
CONTINUE
END
IF @HR = -2147024894 -- not found
BEGIN
SET @HR = 0
BREAK
END
IF @HR <> 0
GOTO CLEANUP
EXEC @HR = sp_OAGetProperty @Entry, 'UrlDestHost', @Url OUT;
IF @HR <> 0
GOTO CLEANUP
EXEC @HR = sp_OAGetProperty @Entry, 'UrlCategory', @UrlCategory OUT;
IF @HR <> 0
GOTO CLEANUP
EXEC @HR = sp_OAGetProperty @Entry, 'BytesReceived', @BytesIn OUT;
IF @HR <> 0
GOTO CLEANUP
EXEC @HR = sp_OAGetProperty @Entry, 'BytesSent', @BytesOut OUT;
IF @HR <> 0
GOTO CLEANUP
INSERT INTO #tblUserActivitySites([User],[UrlCategory],[Url],[TotalBytes])
VALUES (@UserOrIp, @UrlCategory, @Url, @BytesIn + @BytesOut)
EXEC @Count = sp_OADestroy @Entry;
SET @EntryIdx = @EntryIdx + 1
END
EXEC @Count = sp_OADestroy @Filter;
SET @UserList = SUBSTRING(@UserList, @Curr + 1, LEN(@UserList))
SET @Curr = CHARINDEX(N';', @UserList)
END
CLEANUP:
-- Check whether an error occurred.
IF (@HR <> 0)
BEGIN
-- Report the error.
DECLARE @Source nvarchar(255), @Desc nvarchar(255);
EXEC sp_OAGetErrorInfo @Object, @Source OUT, @Desc OUT;
PRINT 'Error: HR = '
PRINT convert(varbinary(4),@HR)
PRINT 'Error: Source = ' + @Source
PRINT 'Error: Description = ' + @Desc
END
EXEC @Count = sp_OADestroy @Object;
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE
END TRY
BEGIN CATCH
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE
PRINT ERROR_MESSAGE()
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine);
END CATCH
IF (@HR <> 0)
BEGIN
SET @Desc = @Desc + ' , HR returned = ' + CONVERT(varchar(50),@HR)
RAISERROR (@Desc,10,1)
SET @HR = 1/0 -- raise exception
END
SELECT DISTINCT p.[User] AS [User], q.[UrlCategoryName] AS [UrlCategory], p.[Url] AS [Url], SUM(p.[TotalBytes]) AS [TotalBytes]
FROM #tblUserActivitySites AS p INNER JOIN [dbo].[tblUrlfMetadata] AS q ON p.[UrlCategory] = q.[URLCategoryId]
WHERE p.[UrlCategory] = q.[URLCategoryId]
GROUP BY p.[User], q.[UrlCategoryName], p.[Url]
ORDER BY [User], [UrlCategory], [TotalBytes] DESC
END