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

Text File | 2010-06-15 | 4KB | 99 lines

CREATE PROCEDURE [ISA_spTrafficSummary]
AS
BEGIN
    SET NOCOUNT ON;
    IF OBJECT_ID ('tblTrafficSummary', 'u') IS NOT NULL
        DROP TABLE [tblTrafficSummary]
    DECLARE @FromDate datetime
    SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
    CREATE TABLE #Table1
    (
        Interval int,
        CacheHits bigint,
        NonCacheHits bigint,
        CachedProcessingTime bigint,
        NonCachedProcessingTime bigint,
        TotalBytes bigint,
        WebRequests bigint,
        Requests bigint
    );
    IF OBJECT_ID ('WEB_LOGS', 'view') IS NOT NULL
    BEGIN
        INSERT INTO #Table1
        SELECT
            15 * FLOOR(DATEPART(minute, [logTime]) / 15 ) + 60* DATEPART(hour, [logTime]) AS Interval,
            CASE
                WHEN ([objectsource] IN (1, 2, 3, 4, 8)) THEN COUNT(*)
                ELSE 0
            END AS CacheHits,
            CASE
                WHEN ([objectsource] NOT IN (1, 2, 3, 4, 8)) THEN COUNT(*)
                ELSE 0
            END AS NonCacheHits,
            CASE
                WHEN ([objectsource] NOT IN (1, 2, 3, 4, 8)) THEN 0
                ELSE SUM(CAST([processingtime] AS bigint))
            END AS CachedProcessingTime,
            CASE
                WHEN ([objectsource] IN (1, 2, 3, 4, 8)) THEN 0
                ELSE SUM(CAST([processingtime] AS bigint))
            END AS NonCachedProcessingTime,
            SUM([bytesrecvd]) + SUM([bytessent]) AS TotalBytes,
            COUNT(*) AS WebRequests,
            COUNT(*) AS Requests
        FROM WEB_LOGS
        WHERE
            ([InternalServiceInfo] & 2) = 0 AND
            ([Action] != 8) AND
            ([resultcode] != 13301) AND
            ([Action] != 10) AND
            ([objectsource] != 6)
        GROUP BY
            15 * FLOOR(DATEPART(minute, [logTime]) / 15 ) + 60* DATEPART(hour, [logTime]),
            DATEPART(minute, [logTime]),
            [objectsource]
    END
    IF OBJECT_ID ('FWS_LOGS', 'view') IS NOT NULL
    BEGIN
        INSERT INTO #Table1
        SELECT
            15 * FLOOR(DATEPART(minute, [logTime]) / 15 ) + 60* DATEPART(hour, [logTime]) AS Interval,
            0 AS CacheHits,
            0 AS NonCacheHits,
            0 AS CachedProcessingTime,
            0 AS NonCachedProcessingTime,
            SUM([bytesrecvdDelta]) + SUM([bytessentDelta]) AS TotalBytes,
            0 AS WebRequests,
            CASE
                WHEN ([Action] = 11) THEN 0
                ELSE COUNT(*)
            END AS Requests
        FROM FWS_LOGS
        WHERE
            ([InternalServiceInfo] & 2) = 0 AND
            ([Action] != 8) AND
            ([Action] != 10)
        GROUP BY
            15 * FLOOR(DATEPART(minute, [logTime]) / 15 ) + 60* DATEPART(hour, [logTime]),
            DATEPART(minute, [logTime]),
            [Action];
    END
    SELECT
        [Interval] AS Interval,
        SUM([CacheHits]) AS [CacheHits],
        SUM([CacheHits]) AS [CachedRequests],
        SUM([NonCacheHits]) AS [NonCachedRequests],
        SUM([CachedProcessingTime]) AS CachedProcessingTime,
        SUM([NonCachedProcessingTime]) AS NonCachedProcessingTime,
        SUM([TotalBytes]) AS TotalBytes,
        SUM([WebRequests]) AS WebRequests,
        SUM([Requests]) AS Requests,
        MAX([Requests]) AS MaxConcurrentConnectionsPerMinute,
        @FromDate AS Date
    INTO [tblTrafficSummary]
    FROM #Table1
    GROUP BY
        Interval
    INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblTrafficSummary')
END