I have a github project that has reports that query the system tables for usage for the report server.
https://github.com/SQL-Server-projects/Reporting-Services-examples
Here's the SQL for the execution log report
DECLARE @StartDate DATETIME = '06-AUG-2024';
DECLARE @EndDate DATETIME = '07-AUG-2024';
DECLARE @all_value NVARCHAR(100) = '<ALL>';
DECLARE @ReportFolder NVARCHAR(100) = @all_value;
DECLARE @ReportName NVARCHAR(100) = @all_value;
DECLARE @LogStatus NVARCHAR(100) = @all_value;
DECLARE @StatusGroup NVARCHAR(100) = @all_value;
DECLARE @username NVARCHAR(100) = @all_value;
DECLARE @GroupByColumn NVARCHAR(100) = NULL;
DECLARE @Blank_value NVARCHAR(100) = @all_value;
DECLARE @ReportServerUrl NVARCHAR(100) = 'https://your_server.your_domain.com/';
DECLARE @ExcludeAdmins BIT = NULL;
DECLARE @ExecutionExcludedUsers NVARCHAR(100) = '';
WITH
report_status
AS
(
SELECT tbl.* FROM (VALUES
( 'rrRenderingError', 'Failure')
, ( 'rsHttpRuntimeClientDisconnectionError', 'Failure')
, ( 'rsInternalError', 'Failure')
, ( 'rsInvalidDataSourceCredentialSetting', 'Failure')
, ( 'rsProcessingAborted', 'Failure')
, ( 'rsProcessingError', 'Failure')
, ( 'rsRenderingExtensionNotFound', 'Failure')
, ( 'rsReportServerDatabaseError', 'Failure')
, ( 'rsSuccess', 'Success')
) tbl ([StatusName], [StatusGroup])
)
,
report_users
AS
(
SELECT
[UserID]
, [UserName]
, [SimpleUserName] = LOWER(RIGHT([UserName], (LEN([UserName]) - CHARINDEX('\', [UserName]))))
FROM
[dbo].[Users] WITH(NOLOCK)
)
,
report_catalog
AS
(
SELECT
rpt.[ItemID]
, rpt.[CreatedById]
, rpt.[ModifiedById]
, rpt.[Type]
, rpt.[Name]
, [ReportName] = rpt.[Name]
, rpt.[Description]
, rpt.[Parameter]
, [CreationDate] = CONVERT(DATETIME, CONVERT(VARCHAR(11), rpt.[CreationDate], 13))
, [ModifiedDate] = CONVERT(DATETIME, CONVERT(VARCHAR(11), rpt.[ModifiedDate], 13))
, [ReportFolder] = SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2)
, rpt.[Path]
, [URL_ReportFolder] = @ReportServerUrl + 'Reports/Pages/Report.aspx?ItemPath=%2f' + SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2) + '&ViewMode=List'
, [URL_Report] = @ReportServerUrl + 'Reports/Pages/Report.aspx?ItemPath=%2f' + SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2) + '%2f' + rpt.[Name]
, [ReportDefinition] = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), rpt.[Content]))
FROM
[dbo].[Catalog] AS rpt WITH (NOLOCK)
WHERE
1=1
AND rpt.[Type] IN(2, 4, 13)
)
,
execution_log
AS
(
SELECT
el.[ReportID]
, el.[Status]
, el.[UserName]
, el.[TimeDataRetrieval]
, el.[TimeProcessing]
, el.[TimeRendering]
, el.[TimeStart]
, el.[RowCount]
, el.[ByteCount]
, el.[Format]
, [Parameters] = CONVERT(VARCHAR(2000), el.[Parameters])
FROM
[dbo].[ExecutionLog] AS el WITH (NOLOCK)
LEFT JOIN report_users AS usr ON el.[UserName] = usr.[UserName]
WHERE
1=1
AND (@StartDate IS NULL OR el.[TimeStart] >= @StartDate)
AND (@EndDate IS NULL OR el.[TimeStart] <= @EndDate)
AND (@ExcludeAdmins = 0 OR usr.[SimpleUserName] NOT IN(@ExecutionExcludedUsers))
UNION
SELECT
[ReportID] = rpt.[ItemID]
, el.[Status]
, el.[UserName]
, [TimeDataRetrieval] = 0
, [TimeProcessing] = 0
, [TimeRendering] = NULL
, el.[TimeStart]
, el.[RowCount]
, el.[ByteCount]
, el.[Format]
, [Parameters] = CONVERT(VARCHAR(2000), el.[Parameters])
FROM
[dbo].[ExecutionLog2] AS el WITH (NOLOCK)
LEFT JOIN report_catalog AS rpt ON rpt.[Path] = el.[ReportPath]
LEFT JOIN report_users AS usr ON el.[UserName] = usr.[UserName]
WHERE
1=1
AND el.[ByteCount] != 0
AND el.[Format] IN('PBIX', 'DataModel')
AND (@ExcludeAdmins = 0 OR usr.[SimpleUserName] NOT IN(@ExecutionExcludedUsers))
)
SELECT
GroupBy1 =
CASE
WHEN @GroupByColumn = 'Report Name' THEN rpt.[ReportName]
WHEN @GroupByColumn = 'Report Folder' THEN rpt.[ReportFolder]
WHEN @GroupByColumn = 'Employee' THEN ISNULL(usr.[SimpleUserName], @Blank_value)
ELSE '<N/A>'
END
, rpt.[Path]
, rpt.[ReportFolder]
, rpt.[Name]
, rpt.[URL_ReportFolder]
, rpt.[URL_Report]
, [URL_Report_Filtered] = rpt.URL_Report + '&rs:Command=Render&' + CONVERT(VARCHAR(2000), el.[Parameters])
, el.[Status]
, el.[TimeStart]
, el.[RowCount]
, el.[ByteCount]
, el.[Format]
, el.[UserName]
, [EmployeeName] = usr.[SimpleUserName]
, [Parameters] =
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(CONVERT(NVARCHAR(MAX), el.[Parameters])
, '%20', ' ')
, '%2F', '-')
, '%3C', '<')
, '%3E', '>')
, '%3A', ':')
, [TotalSeconds] = DATEADD(ms, (el.[TimeDataRetrieval] + el.[TimeProcessing] + el.[TimeRendering]), 0)
, [TimeDataRetrieval] = DATEADD(ms, el.[TimeDataRetrieval], 0)
, [TimeProcessing] = DATEADD(ms, el.[TimeProcessing], 0)
, [TimeRendering] = DATEADD(ms, el.[TimeRendering], 0)
, [TotalSecondsNbr] = (el.[TimeDataRetrieval] + el.[TimeProcessing] + el.[TimeRendering])
, [TimeDataRetrievalNbr] = el.[TimeDataRetrieval]
, [TimeProcessingNbr] = el.[TimeProcessing]
, [TimeRenderingNbr] = el.[TimeRendering]
, [OrderbyDate] = CAST([TimeStart] AS DATETIME)
, rpt.[Type]
FROM
report_catalog AS rpt
LEFT JOIN execution_log AS el ON el.[ReportID] = rpt.[ItemID]
LEFT JOIN report_status AS rs ON el.[Status] = rs.[StatusName]
LEFT JOIN report_users AS usr ON el.[UserName] = usr.[UserName]
WHERE
1=1
AND (@StartDate IS NULL OR el.[TimeStart] >= @StartDate)
AND (@EndDate IS NULL OR el.[TimeStart] <= @EndDate)
AND (@all_value IN (@ReportFolder) OR rpt.[ReportFolder] IN(@ReportFolder))
AND (@all_value IN(@ReportName) OR rpt.[ReportName] IN(@ReportName))
AND (@all_value IN(@LogStatus) OR el.[Status] IN(@LogStatus))
AND (@all_value IN(@StatusGroup) OR rs.[StatusGroup] IN(@StatusGroup))
AND (@all_value IN(@UserName) OR usr.[SimpleUserName] IN(@UserName));
