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

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

CREATE PROCEDURE [ISA_spErrorSummary]
AS
BEGIN
    IF OBJECT_ID ('tblErrorSummary', 'u') IS NOT NULL
        DROP TABLE [tblErrorSummary]
    DECLARE @FromDate datetime
    SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
    CREATE TABLE #Table1
    (
        Error varchar(32),
        Requests bigint,
    );
    IF OBJECT_ID ('WEB_LOGS', 'view') IS NOT NULL
    BEGIN
        INSERT INTO #Table1
        SELECT
            N'{[23068]}' AS Error,
            COUNT(*) AS Requests
        FROM WEB_LOGS
        WHERE
            ([InternalServiceInfo] & 2) = 0 AND
            ([Action] != 8) AND
            ([resultcode] != 13301) AND
            ([objectsource] != 6) AND
            ([resultcode] = 400) OR
            ([resultcode] = 404) OR
            ([resultcode] >= 500)
    END
    IF OBJECT_ID ('FWS_LOGS', 'view') IS NOT NULL
    BEGIN
        INSERT INTO #Table1
        SELECT
            N'{[23069]}' AS Error,
            COUNT(*) AS Requests
        FROM FWS_LOGS
        WHERE
            ([Action] = 8) AND
            ([InternalServiceInfo] & 2) = 0 AND
            ([resultcode] <> 0) AND
            ([resultcode] <> 0x80074E20) AND
            ([resultcode] <> 0x80074E21)
    END
-- add 'WEB' & 'WinSock' entries to be sure these exist
    INSERT INTO #Table1
    VALUES (N'{[23068]}', 0)
    INSERT INTO #Table1
    VALUES (N'{[23069]}', 0)
-- and sum it up
    SELECT
        Error,
        SUM([Requests]) AS Requests,
        @FromDate AS Date
    INTO [tblErrorSummary]
    FROM #Table1
    GROUP BY Error
    INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblErrorSummary')
END