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

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

CREATE PROCEDURE [dbo].[ISA_spUrlfTelemtrySummary]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TelemtryEnabled int
    DECLARE @NthUrl int
    DECLARE @SQLString nvarchar(1000) = N'SELECT @TelemtryEnabledOut = [TelemtryEnabled], @NthUrlOut = [NthUrl] FROM RS_SRV.[ISA_RS_Db].[dbo].[tblUrlfTelemtryConfig]';
    DECLARE @ParmDefinition nvarchar(1000) = N'@TelemtryEnabledOut int OUTPUT, @NthUrlOut int OUTPUT';
    EXECUTE sp_executesql @SQLString, @ParmDefinition, @TelemtryEnabledOut=@TelemtryEnabled OUTPUT, @NthUrlOut=@NthUrl OUTPUT;
    IF (@TelemtryEnabled = 0)
        RETURN
    IF OBJECT_ID ('tblUrlfTelemetrySiteSummary', 'u') IS NOT NULL
        DROP TABLE [tblUrlfTelemetrySiteSummary]
    IF OBJECT_ID ('WEB_LOGS', 'view') IS NOT NULL
    BEGIN
        DECLARE @FromDate datetime = (SELECT [FromDate] FROM [tblSummaryParams])
        SELECT
            [uri] AS [Site],
            Count(*) AS [Requests],
            @FromDate AS [date]
        INTO [tblUrlfTelemetrySiteSummary]
        FROM
            (select [uri], ROW_NUMBER() OVER (order by logtime ASC) AS rownum
            FROM WEB_LOGS) t
        WHERE rownum % @NthUrl = 0
        GROUP BY [uri]
    END
    INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblUrlfTelemetrySiteSummary')
    IF OBJECT_ID ('tblUrlfTelemetryCategorizationReasonSummary', 'u') IS NOT NULL
        DROP TABLE [tblUrlfTelemetryCategorizationReasonSummary]
    IF OBJECT_ID ('WEB_LOGS', 'view') IS NOT NULL
    BEGIN
        SELECT
            [UrlCategorizationReason] AS [CategorizationReason],
            Count(*) AS [Requests],
            @FromDate AS [date]
        INTO [tblUrlfTelemetryCategorizationReasonSummary]
        FROM WEB_LOGS
        GROUP BY [UrlCategorizationReason]
    END
    INSERT INTO [tblSummaryTables] ([TableName]) VALUES ('tblUrlfTelemetryCategorizationReasonSummary')
END