Tracking Dashboard Usage in Report Server

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 Anonymous_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], Anonymous_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));

Hi, tmhalila 

The ExecutionId in the ReportServer database table tracks each instance of the report execution. ExecutionLog2 means that even if you open the dashboard once, multiple entries may be logged due to different stages of report processing, such as data retrieval, rendering, and export.

Rather than relying solely on ExecutionId, you can track sessions by using ExecutionId in conjunction with other fields, such as TimeStart and TimeEnd, to group related executions.
You can also implement custom logging in your reports to capture user-specific details and actions. This helps distinguish between different users who share the same username.
If possible, record the IP address of the user accessing the report. This helps distinguish between different users who share the same username. Consider using a view, which may provide more detailed or user-friendly fields that can help with better tracking and analysis. You can check the following link:

Report server ExecutionLog and the ExecutionLog3 view - SQL Server Reporting Services (SSRS) | Microsoft Learn

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.