topic Re: Tracking Dashboard Usage in Report Server in Report Server

Tracking Dashboard Usage in Report Server

tmhalila — Tue, 06 Aug 2024 11:40:17 GMT

Hello team,

I am trying to use ExecutionLog2 from the ReportServer database, so it can help to track dashboard usage. However, I have failed to understand what the ExecutionId is tracking because when I was trying to open a dashboard just once I see like 5 records of my username repeated with a unique ExecutionId which is becoming difficult to know how many users accessed the report/dashboard.

If I try to use a distinct username it will not be realistic because one username is used by multiple users. Can anyone suggest the idea here?

Re: Tracking Dashboard Usage in Report Server

aduguid — Wed, 07 Aug 2024 01:45:32 GMT

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));

Re: Tracking Dashboard Usage in Report Server

Anonymous — Wed, 07 Aug 2024 02:51:53 GMT

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.

Re: Tracking Dashboard Usage in Report Server

tmhalila — Fri, 09 Aug 2024 11:30:35 GMT

Thank you for the suggestions. I tried using the ExecutionId with other fields, like TimeStart and TimeEnd, for group-related executions, but it didn't work because some stage executions are delayed for the same user.

The second and third options require admin privileges, so we can add IP tracking or implement custom logging. I will check with the team to see if these are possible. In the meantime, I will go with another alternative as I have observed execution stages repeat 5 times for each login account so I decided to count all and divide by 5.

Re: Tracking Dashboard Usage in Report Server

tmhalila — Fri, 09 Aug 2024 11:36:46 GMT

I am working with a team that is proficient in SQL, so we can explore this option. We have also reviewed your GitHub; thank you for the good work. I'll come back here if this is successful.

In the meantime, I will go with another alternative. I have observed that the execution stages repeat 5 times for each login account, so I have decided to count them all and divide by 5.

Re: Tracking Dashboard Usage in Report Server

Anonymous — Mon, 26 Aug 2024 08:36:44 GMT

Hi, @tmhalila

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

Re: Tracking Dashboard Usage in Report Server

tmhalila — Tue, 27 Aug 2024 15:27:48 GMT

Thanks for the reminder.

The solution I applied was quite simple. In my case, I have 10 dashboards on the Power BI Server. I noticed that when any dashboard report is accessed, it returns 5 execution records if the dashboard is fully opened. So, I counted all the records and divided them by 5.