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

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

CREATE PROCEDURE [ISA_spDestinationSummary]
AS
BEGIN
    SET NOCOUNT ON;
    IF OBJECT_ID ('tblDestinationSummary', 'u') IS NOT NULL
        DROP TABLE [tblDestinationSummary]
    DECLARE @FromDate datetime
    SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
    CREATE TABLE #Table1
    (
        DestinationIP [uniqueidentifier],
        BytesIn bigint,
        BytesOut bigint,
        TotalBytes bigint,
        Requests bigint,
        Date datetime
    );
    IF OBJECT_ID ('FWS_LOGS', 'view') IS NOT NULL
    BEGIN
        INSERT INTO #Table1
        SELECT
            [DestinationIP] AS DestinationIP,
            SUM([bytesrecvdDelta]) AS BytesIn,
            SUM([bytessentDelta]) AS BytesOut,
            SUM([bytesrecvdDelta]) + SUM([bytessentDelta]) AS TotalBytes,
            CASE
                WHEN ([Action] = 11) THEN 0
                ELSE COUNT(*)
            END AS Requests,
            @FromDate AS Date
        FROM FWS_LOGS
        WHERE
            ([Action] != 8) AND
            ([Action] != 10) AND
            ([InternalServiceInfo] & 2) = 0
        GROUP BY
            [DestinationIP],
            [Action];
    END
    SELECT
        [DestinationIP],
        SUM([BytesIn]) AS BytesIn,
        SUM([BytesOut]) AS BytesOut,
        SUM([TotalBytes]) AS TotalBytes,
        SUM([Requests]) AS Requests,
        @FromDate AS Date
    INTO [tblDestinationSummary]
    FROM #Table1
    GROUP BY
        [DestinationIP]
    INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblDestinationSummary')
END