Stored Procedure to maintain RSExecutionLog

USE RSExecutionLog
GO

IF OBJECT_ID('dbo.Numbers') IS NOT NULL 
DROP TABLE dbo.Numbers;
GO
CREATE TABLE dbo.Numbers
(
number INT NOT NULL CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED
);

SET NOCOUNT ON;

DECLARE
 @max AS INT,
 @rc AS INT;

SET @max = 10000;
SET @rc = 1;

BEGIN TRAN;
 INSERT INTO dbo.Numbers(number) VALUES(1);

 WHILE @rc * 2 <= @max
 BEGIN
 INSERT INTO dbo.Numbers(number)
 SELECT number + @rc 
 FROM dbo.Numbers;

 SET @rc = @rc * 2;
 END

 INSERT INTO dbo.Numbers(number)
 SELECT number + @rc 
 FROM dbo.Numbers
 WHERE number + @rc <= @max;
COMMIT TRAN; 
GO

IF OBJECT_ID('dbo.Split') IS NOT NULL 
DROP FUNCTION dbo.Split;
GO

CREATE FUNCTION dbo.Split ( 
 @List nvarchar(max), --The delimited list 
 @Del1 char(1) = '&', --The delimiter between each name value pair
 @Del2 char(1) = '=' --The delimiter between name and value
) 
RETURNS @T TABLE (Name nvarchar(2000), Value nvarchar(2000)) 
AS 
BEGIN 

 DECLARE @WrappedList nvarchar(max), @MaxItems int 

 SELECT @WrappedList = @Del1 + @List + @Del1, @MaxItems = LEN(@List) 

 INSERT INTO @T (Name, Value) 
 SELECT CAST(SUBSTRING(@WrappedList, Number + 1, CHARINDEX(@Del2, @WrappedList, Number + 1) - Number - 1) AS NVARCHAR(2000)) AS Name
 ,CAST(SUBSTRING(@WrappedList, CHARINDEX(@Del2, @WrappedList, Number + 1) + 1, CHARINDEX(@Del1, @WrappedList, Number + 1) - CHARINDEX(@Del2, @WrappedList, Number + 1) - 1) AS NVARCHAR(2000)) AS Value 
 FROM dbo.Numbers n 
 WHERE n.Number <= LEN(@WrappedList) - 1 
 AND SUBSTRING(@WrappedList, n.Number, 1) = @Del1 

RETURN 
END
GO

IF OBJECT_ID('dbo.Upd_RSExecutionLog') IS NOT NULL 
DROP PROCEDURE dbo.Upd_RSExecutionLog;
GO

CREATE PROCEDURE dbo.Upd_RSExecutionLog
AS

--***********************************
-- Initialise
--***********************************

DECLARE @myRows INT
DECLARE @myTimeEnd DATETIME
DECLARE @myTime DATETIME

SET @myTimeEnd = (SELECT MAX(TimeEnd) FROM RSExecutionLog.dbo.ExecutionLogs)
SET @myTime = GETDATE()

--***********************************
-- Lookup Tables
--***********************************

-- FormatTypes
INSERT INTO RSExecutionLog.dbo.FormatTypes
 (Format)
SELECT DISTINCT 
 Format
FROM ReportServer.dbo.ExecutionLog WITH(NOLOCK)
WHERE Format IS NOT NULL
AND Format COLLATE Latin1_General_CI_AS NOT IN (SELECT Format
 FROM RSExecutionLog.dbo.FormatTypes)

SET @myRows = @@ROWCOUNT

INSERT INTO RSExecutionLog.dbo.RunLogs 
 ([Event]
 ,[When]) 
VALUES 
 ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table FormatTypes'
 ,@myTime)

-- Machines
INSERT INTO RSExecutionLog.dbo.Machines
 (InstanceName)
SELECT DISTINCT 
 InstanceName
FROM ReportServer.dbo.ExecutionLog WITH(NOLOCK)
WHERE InstanceName IS NOT NULL
AND InstanceName COLLATE Latin1_General_CI_AS NOT IN (SELECT InstanceName
 FROM RSExecutionLog.dbo.Machines)

SET @myRows = @@ROWCOUNT

INSERT INTO RSExecutionLog.dbo.RunLogs 
 ([Event]
 ,[When]) 
VALUES 
 ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table Machines'
 ,@myTime)

-- Reports
INSERT INTO RSExecutionLog.dbo.Reports
 (ReportID
 ,Path
 ,Name
 ,ReportType)
SELECT DISTINCT 
 c.ItemID
 ,c.Path
 ,c.Name
 ,c.Type
FROM ReportServer.dbo.Catalog c WITH (NOLOCK)
     INNER JOIN 
 ReportServer.dbo.ExecutionLog l WITH (NOLOCK)
     ON l.ReportID = c.ItemID 
WHERE c.ItemID NOT IN (SELECT ReportID
 FROM RSExecutionLog.dbo.Reports)

SET @myRows = @@ROWCOUNT

INSERT INTO RSExecutionLog.dbo.RunLogs 
 ([Event]
 ,[When]) 
VALUES 
 ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table Reports'
 ,@myTime)

-- ReportTypes - Inserted via the setup createtables.sql file

--INSERT INTO RSExecutionLog.[dbo].[ReportTypes] ([ReportType], [Name]) VALUES (1, N'Folder')
--INSERT INTO RSExecutionLog.[dbo].[ReportTypes] ([ReportType], [Name]) VALUES (2, N'Report')
--INSERT INTO RSExecutionLog.[dbo].[ReportTypes] ([ReportType], [Name]) VALUES (3, N'Resource')
--INSERT INTO RSExecutionLog.[dbo].[ReportTypes] ([ReportType], [Name]) VALUES (4, N'Linked Report')
--INSERT INTO RSExecutionLog.[dbo].[ReportTypes] ([ReportType], [Name]) VALUES (5, N'Data Source')

-- RequestTypes - Inserted via the setup createtables.sql file

--INSERT INTO RSExecutionLog.[dbo].[RequestTypes] ([RequestType], [Name]) VALUES (0, N'User')
--INSERT INTO RSExecutionLog.[dbo].[RequestTypes] ([RequestType], [Name]) VALUES (1, N'System')

-- SourceTypes - Inserted via the setup createtables.sql file

--INSERT INTO RSExecutionLog.[dbo].[SourceTypes] ([SourceType], [Name]) VALUES (1, N'Live')
--INSERT INTO RSExecutionLog.[dbo].[SourceTypes] ([SourceType], [Name]) VALUES (2, N'Cache')
--INSERT INTO RSExecutionLog.[dbo].[SourceTypes] ([SourceType], [Name]) VALUES (3, N'Snapshot')
--INSERT INTO RSExecutionLog.[dbo].[SourceTypes] ([SourceType], [Name]) VALUES (4, N'History')
--INSERT INTO RSExecutionLog.[dbo].[SourceTypes] ([SourceType], [Name]) VALUES (5, N'Adhoc')

-- StatusCodes
INSERT INTO RSExecutionLog.dbo.StatusCodes
 (Status)
SELECT DISTINCT 
 Status
FROM ReportServer.dbo.ExecutionLog WITH(NOLOCK)
WHERE Status IS NOT NULL
AND Status COLLATE Latin1_General_CI_AS NOT IN (SELECT Status
 FROM RSExecutionLog.dbo.StatusCodes)

SET @myRows = @@ROWCOUNT

INSERT INTO RSExecutionLog.dbo.RunLogs 
 ([Event]
 ,[When]) 
VALUES 
 ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table StatusCodes'
 ,@myTime)

-- Users
INSERT INTO RSExecutionLog.dbo.Users
 (UserName)
SELECT UserName
FROM ReportServer.dbo.Users WITH(NOLOCK)
WHERE UserName COLLATE Latin1_General_CI_AS NOT IN (SELECT UserName
 FROM RSExecutionLog.dbo.Users)

SET @myRows = @@ROWCOUNT

INSERT INTO RSExecutionLog.dbo.RunLogs 
 ([Event]
 ,[When]) 
VALUES 
 ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table Users'
 ,@myTime)

--***************************
-- Main Tables
--***************************

-- ExecutionLogs
INSERT INTO RSExecutionLog.dbo.ExecutionLogs
 (ReportKey
 ,UserKey
 ,MachineKey
 ,RequestType
 ,FormatType
 ,StatusCode
 ,SourceType
 ,Parameters
 ,TimeStart
 ,TimeEnd
 ,TimeDataRetrieval
 ,TimeProcessing
 ,TimeRendering
 ,ByteCount
 ,[RowCount])
SELECT R.ReportKey
 ,U.UserKey
 ,M.MachineKey
 ,L.RequestType
 ,F.FormatType
 ,S.StatusCode
 ,L.Source AS SourceType
 ,L.Parameters
 ,L.TimeStart
 ,L.TimeEnd
 ,L.TimeDataRetrieval
 ,L.TimeProcessing
 ,L.TimeRendering
 ,L.ByteCount
 ,L.[RowCount]
FROM ReportServer.dbo.ExecutionLog L WITH(NOLOCK)
 INNER JOIN
 ReportServer.dbo.Catalog C WITH(NOLOCK)
 ON L.ReportID = C.ItemID
 INNER JOIN
 RSExecutionLog.dbo.Reports R WITH(NOLOCK)
 ON C.Name COLLATE Latin1_General_CI_AS = R.Name
 AND C.Path COLLATE Latin1_General_CI_AS = R.Path
 AND C.Type = R.ReportType
 INNER JOIN
 RSExecutionLog.dbo.Users U WITH (NOLOCK)
 ON L.UserName COLLATE Latin1_General_CI_AS = U.UserName
 INNER JOIN
 RSExecutionLog.dbo.Machines M WITH(NOLOCK)
 ON L.InstanceName COLLATE Latin1_General_CI_AS = M.InstanceName
 INNER JOIN
 RSExecutionLog.dbo.FormatTypes F WITH(NOLOCK)
 ON L.Format COLLATE Latin1_General_CI_AS = F.Format
 INNER JOIN
 RSExecutionLog.dbo.StatusCodes S WITH(NOLOCK)
 ON L.Status COLLATE Latin1_General_CI_AS = S.Status
WHERE L.TimeEnd > @myTimeEnd

SET @myRows = @@ROWCOUNT

INSERT INTO RSExecutionLog.dbo.RunLogs 
 ([Event]
 ,[When]) 
VALUES 
 ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table ExecutionLogs'
 ,@myTime)

-- ExecutionParameters

INSERT INTO RSExecutionLog.dbo.ExecutionParameters
 (Name
 ,Value
 ,ExecutionLogID) 
SELECT P.Name
 ,P.Value
 ,L.ExecutionLogID
FROM RSExecutionLog.dbo.ExecutionLogs L
 CROSS APPLY
 (SELECT Name
 ,Value
 FROM RSExecutionLog.dbo.Split(L.Parameters, '&', '=')) P
WHERE L.Parameters IS NOT NULL

SET @myRows = @@ROWCOUNT

INSERT INTO RSExecutionLog.dbo.RunLogs 
 ([Event]
 ,[When]) 
VALUES 
 ('Inserted ' + CONVERT(nvarchar(50), @myRows) + ' rows into table ExecutionParameters'
 ,@myTime)

-- ExecutionLogs NULL out Parameters that have been processed
UPDATE RSExecutionLog.dbo.ExecutionLogs 
SET Parameters = NULL 
WHERE Parameters IS NOT NULL

-- Report updates
SELECT [Event]
 ,[When]
FROM RSExecutionLog.dbo.RunLogs 
WHERE [When] = @myTime

RETURN
GO

-- Test it
EXEC dbo.Upd_RSExecutionLog
GO