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/25141

Text File | 2010-06-15 | 2KB | 56 lines

CREATE PROCEDURE [dbo].[ISA_spUrlfSummary]
AS
BEGIN
    SET NOCOUNT ON;
    IF OBJECT_ID ('tblUrlfSummary', 'u') IS NOT NULL
        DROP TABLE [tblUrlfSummary]
    DECLARE @FromDate datetime
    SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
    DECLARE @LogServerName nvarchar(32)
    SET @LogServerName = dbo.fnGetLogsServerName();
    IF OBJECT_ID ('WEB_LOGS', 'view') IS NOT NULL
    BEGIN
        SELECT
            [UrlCategory] AS URLCategoryId,
            [UrlCategorizationReason] AS [CategorizationReason],
            CASE
                WHEN (([Action] = 8) OR ([resultcode] = 13301))
                        THEN 1
                WHEN ([Action] = 10)
                        THEN 2
                WHEN ([objectsource] = 6)
                        THEN 3
                ELSE 0
            END AS Action,
            CASE
                WHEN [InternalServiceInfo] & 1 <> 0
                                                                                  THEN 1
                ELSE 0
            END AS UrlfMatched,
            COUNT(*) AS Requests,
            SUM([bytesrecvd]) AS BytesIn,
            SUM([bytessent]) AS BytesOut,
            SUM([bytesrecvd]) + SUM([bytessent]) AS TotalBytes,
            @FromDate AS Date,
            @LogServerName AS ServerName
        INTO [tblUrlfSummary]
        FROM WEB_LOGS
        WHERE
            ([InternalServiceInfo] & 2) = 0
        GROUP BY
            [UrlCategory],
            CASE
                WHEN (([Action] = 8) OR ([resultcode] = 13301))
                        THEN 1
                WHEN ([Action] = 10)
                        THEN 2
                WHEN ([objectsource] = 6)
                        THEN 3
                ELSE 0
            END,
            [InternalServiceInfo],
            [UrlCategorizationReason]
    END
    INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblUrlfSummary')
END