Speak guys!
In this post I would like to show you how to log the report view and identify which user is accessing, that is, how to write to a database table, which user is accessing a particular report and when it was done.
The idea for this post came from a question in a Telegram group and I thought it might help more people by publishing this article. Even when I needed to create something like that, I didn't find much clear and objective documentation on the internet.
How to log in to view reports
Since the purpose of this post is not to teach how to create reports in SSRS (I will create a post about it), I will move towards the goal of this article. In short, what you must do to achieve this goal is to use a default SSRS variable to identify the user running the report (= User! UserID) and pass this variable to the dataset that is querying the data in the database. so that it includes in the query process the write operation of this log.
Did you find it difficult? I will detail 🙂
Assuming you have a functional Reporting Services report, as shown below, let's start preparing our routine so we can log the report execution:
I can't help but comment here, as I support, recommend and ALWAYS use Stored Procedures for running my reports. This gives me a great deal of freedom and flexibility to query and process data, passing parameters and making it possible for me to edit the query just by changing objects in the database without having to change my report.
The current source code for my Stored Procedure looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE PROCEDURE dbo.stpConsulta_Relatorio ( @Ds_Objeto AS VARCHAR(100) ) AS BEGIN SELECT * FROM sys.objects WHERE [name] = @Ds_Objeto END GO |
Now let's start the changes to start logging the changes, even with the name of the user who is querying the report.
First, let's create the table that will store the report execution log information:
|
1 2 3 4 5 6 |
CREATE TABLE dbo.Logs_Relatorios ( Id_Log INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Dt_Log DATETIME DEFAULT GETDATE(), Ds_Relatorio VARCHAR(100), Ds_Usuario VARCHAR(100) ) WITH(DATA_COMPRESSION=PAGE) |
Let's change our Stored Procedure that queries the data to include the log record. For this, I will add the @Ds_Usuario parameter in SP so that it receives the username and enter in the bank log.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
ALTER PROCEDURE dbo.stpConsulta_Relatorio ( @Ds_Objeto AS VARCHAR(100), @Ds_Usuario AS VARCHAR(100) ) AS BEGIN -- Loga as operações INSERT INTO dbo.Logs_Relatorios ( Dt_Log, Ds_Relatorio, Ds_Usuario ) VALUES ( GETDATE(), 'Consulta_Relatorio', @Ds_Usuario) -- Executa o relatório SELECT * FROM sys.objects WHERE [name] = @Ds_Objeto END GO |
The next step is to change our dataset in Report Builder to include the user parameter:
Once the dataset settings screen opens, click the Parameters option and add the @Ds_Usuario variable to the Stored Procedure call:
When you click the Expression button, select the internal field UserID (= User! UserID):
After making these changes, save Report and access it on the SSRS portal:
When querying the data generated in the history table (dbo.Logs_Relatorios), we can view the generated records, with the date when the report was consulted and who consulted:
[Video] - Logging in to view reports
If you're the kind of person who is most likely to learn from rich visuals of image and audio (aka Video), I've prepared a quick video lesson on how to do this:
And the ExecutionLog view?
If you know the Reporting Services views and catalog tables, you may be asking yourself: “Why not use the ExecutionLog view, which already has all this information stored automatically, for all reports?”
Well, first of all, I will present the ExecutionLog view to anyone who doesn't know. This view, which brings data from the ExecutionLogStorage table, records all executions of all Reporting Services reports, including the parameters used, user who ran the report, response time, render time, etc.
|
1 2 |
SELECT * FROM ReportServer.dbo.ExecutionLog |
Result:
"So, why use manual audit control, if the Report Server itself already does this for me, bringing a lot more information and interesting statistics, to all reports and automatically?"
A: Although I agree with all this, there are certain situations that force you to look for alternatives to implement your controls and some of the reasons that would lead me to using this solution are:
- BI team wants to centralize all log information from all RS instances to a specific database
- BI team does not have read access to ReportServer database
- By default, Report Server has a limit of 60 days to store data from the ExecutionLogStorage table.
An interesting alternative would be for the BI team to create a job that collects this temporary data and stores it in a definitive table, but when BI analysts do not have access to create jobs, manual log control is a viable option.
- Miscellaneous disk space restrictions and permissions on instance
In addition, the example shown above may be for purposes other than auditing, such as filtering data according to the user, returning different queries, or even restricting access within the Stored Procedure itself.
Well guys, that's it!
Hope you enjoyed this article and see you next time!
Hug!
Show Dirceu !! Very well explained!