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

Text File | 2010-06-15 | 7KB | 177 lines

CREATE PROCEDURE [dbo].[ISA_spUserSummary]
AS
BEGIN
    SET NOCOUNT ON;
    IF OBJECT_ID ('tblUserSummary', 'u') IS NOT NULL
        DROP TABLE [tblUserSummary]
    IF OBJECT_ID ('tblMalwareUserSummary', 'u') IS NOT NULL
        DROP TABLE [tblMalwareUserSummary]
    IF OBJECT_ID ('tblUrlfUserSummary', 'u') IS NOT NULL
        DROP TABLE [tblUrlfUserSummary]
    DECLARE @FromDate datetime
    SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
    CREATE TABLE #Table1
    (
        UserName nvarchar(514),
        BytesOut bigint,
        BytesIn bigint,
        TotalBytes bigint,
        Requests bigint,
        WebBytesOut bigint,
        WebBytesIn bigint,
        WebTotalBytes bigint,
        WebRequests bigint,
        AuthorizedFailures bigint,
        MalwareIncidents bigint,
        UrlfIncidents bigint,
        MalwareInspectionAction int,
        DeniedFailedIntra int
    );
    IF OBJECT_ID ('WEB_LOGS', 'view') IS NOT NULL
    BEGIN
        INSERT INTO #Table1
        SELECT
                CASE
                    WHEN ([ClientIP] = '00000000-0000-0000-0000-FFFF7F000001') THEN N'-'
                    WHEN [ClientUserName] = N'anonymous' THEN '@\@' + CAST([ClientIP] AS nvarchar(500))
                    WHEN [ClientUserName] = N'-' AND [ClientIP] = '00000000-0000-0000-0000-000000000000' THEN
                         CASE
                            WHEN CHARINDEX('Check Upstream',[ClientAgent])<>0 THEN N'{[23140]}'
                            WHEN CHARINDEX('Member Ping',[ClientAgent])<>0 THEN N'{[23141]}'
                            WHEN CHARINDEX('Fetch API',[ClientAgent])<>0 THEN N'{[23142]}'
                            WHEN CHARINDEX('Delete API',[ClientAgent])<>0 THEN N'{[23143]}'
                            ELSE N'-'
                         END
                    ELSE [ClientUserName]
                END AS UserName,
               SUM([bytessent]) AS BytesOut,
               SUM([bytesrecvd]) AS BytesIn,
               SUM([bytessent]) + SUM([bytesrecvd]) AS TotalBytes,
               COUNT(*) AS Requests,
               SUM([bytessent]) AS WebBytesOut,
               SUM([bytesrecvd]) AS WebBytesIn,
               SUM([bytessent]) + SUM([bytesrecvd]) AS WebTotalBytes,
               COUNT(*) AS WebRequests,
               CASE
                   WHEN ([resultcode] = 403) or ([resultcode] = 401) Then COUNT(*)
                   ELSE 0
               END AS AuthorizedFailures,
               CASE
                   WHEN ([ThreatName] = '-') THEN 0
                   ELSE COUNT(*)
               END AS MalwareIncidents,
               CASE
                   WHEN (([resultcode] = 13301) OR ([Action] = 8))
                       AND ([InternalServiceInfo] &  1 <> 0)  THEN  COUNT(*)
                   ELSE 0
               END AS UrlfIncidents,
               [MalwareInspectionAction],
               CASE
                    WHEN
                        ([Action] = 8) OR
                        ([resultcode] = 13301) OR
                        ([Action] = 10) OR
                        ([objectsource] = 6)
                            THEN 1
                    ELSE 0
               END AS DeniedFailedIntra
        FROM WEB_LOGS
        WHERE
            ([InternalServiceInfo] & 2) = 0
        GROUP BY
               CASE
                    WHEN ([ClientIP] = '00000000-0000-0000-0000-FFFF7F000001') THEN N'-'
                    WHEN [ClientUserName] = N'anonymous' THEN '@\@' + CAST([ClientIP] AS nvarchar(500))
                    WHEN [ClientUserName] = N'-' AND [ClientIP] = '00000000-0000-0000-0000-000000000000' THEN
                         CASE
                            WHEN CHARINDEX('Check Upstream',[ClientAgent])<>0 THEN N'{[23140]}'
                            WHEN CHARINDEX('Member Ping',[ClientAgent])<>0 THEN N'{[23141]}'
                            WHEN CHARINDEX('Fetch API',[ClientAgent])<>0 THEN N'{[23142]}'
                            WHEN CHARINDEX('Delete API',[ClientAgent])<>0 THEN N'{[23143]}'
                            ELSE N'-'
                         END
                    ELSE [ClientUserName]
               END,
               [resultcode],
               [ThreatName],
               [MalwareInspectionAction],
               [Action],
               [objectsource],
               [InternalServiceInfo]
    END
    IF OBJECT_ID ('FWS_LOGS', 'view') IS NOT NULL
    BEGIN
        INSERT INTO #Table1
        SELECT
               CASE
                    WHEN [ClientUserName] = '-' OR  [ClientUserName] = 'SYSTEM'
                            THEN '@\@' + CAST([OriginalClientIP] AS nvarchar(500))
                    ELSE [ClientUserName]
               END AS UserName,
               SUM([bytessentDelta]) AS BytesOut,
               SUM([bytesrecvdDelta]) AS BytesIn,
               SUM([bytessentDelta]) + SUM([bytesrecvdDelta]) AS TotalBytes,
    -- dont count intermediate packets
               CASE
                    WHEN ([Action] = 11) THEN 0
                    ELSE COUNT(*)
               END AS Requests,
               0 AS WebBytesOut,
               0 AS WebBytesIn,
               0 AS WebTotalBytes,
               0 AS WebRequests,
               0 AS AuthorizedFailures,
               0 AS MalwareIncidents,
               0 AS UrlfIncidents,
               0 AS MalwareInspectionAction,
               0 AS DeniedFailedIntra
        FROM FWS_LOGS
        WHERE
            ([InternalServiceInfo] & 2) = 0 AND
            ([Action] != 8) AND
            ([Action] != 10)
        GROUP BY
             CASE
                 WHEN [ClientUserName] = '-' OR  [ClientUserName] = 'SYSTEM'
                         THEN '@\@' + CAST([OriginalClientIP] AS nvarchar(500))
                 ELSE [ClientUserName]
             END,
             [Action]
    END
-- now sum it all up
    SELECT UserName,
        SUM([BytesOut]) AS BytesOut,
        SUM([BytesIn]) AS BytesIn,
        SUM([TotalBytes]) AS TotalBytes,
        SUM([Requests]) AS Requests,
        SUM([AuthorizedFailures]) AS AuthorizedFailures,
        SUM([WebBytesOut]) AS WebBytesOut,
        SUM([WebBytesIn]) AS WebBytesIn,
        SUM([WebTotalBytes]) AS WebTotalBytes,
        SUM([WebRequests]) AS WebRequests,
        @FromDate AS Date
    INTO [tblUserSummary]
    FROM #Table1 WITH(NOLOCK)
    WHERE [DeniedFailedIntra] = 0
    GROUP BY [UserName]
    SELECT UserName,
        SUM([MalwareIncidents]) AS MalwareIncidents,
        [MalwareInspectionAction] AS [MalwareInspectionAction],
        @FromDate AS Date
    INTO [tblMalwareUserSummary]
    FROM #Table1 WITH(NOLOCK)
    WHERE [MalwareIncidents] > 0
    GROUP BY [UserName],
        [MalwareInspectionAction]
    SELECT UserName,
        SUM ([UrlfIncidents]) AS UrlfIncidents,
        @FromDate AS Date
    INTO [tblUrlfUserSummary]
    FROM #Table1
    WHERE [UrlfIncidents] > 0
    GROUP BY [UserName]
    INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblUrlfUserSummary')
    INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblMalwareUserSummary')
    INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblUserSummary')
END