If you build SSRS reports for your company that are available in Web Apps (or even just on your Report Server) I recommend taking a page from Microsoft and adding an SLUserID parameter. Other than usage for potential security, this will give you a way to see which of your users are running reports in Web Apps. That can help drive development to certain reports over others and / or act as an audit trail.
To add the SLUserID to one of your reports, create a shared dataset in your Visual Studio solution named SLUserID. The Data source should be your SYS database. Have it execute the stored procedure
This is a standard procedure from Microsoft, so you should have it.
Click on the Parameters tab and assign
[&UserID] as the Default Value for @parm1 and select Internal (read-only)
Add a dataset to your SSRS report, again named SLUserID and point it toward this Shared Dataset.
Now create a Report Parameter named SLUserID. Toggle the visibility to hidden and then set the default values (below) to use your report dataset.
When deploying the report to your report server, you may have to click ‘Manage’ and point the report toward your shared dataset on the report server. This should only have to be done once per report.
Now, whenever a report is run, the UserID will be included in the parameters of the report. Why is this important? Web Apps typically runs the reports under a Trusted Web Service Account. So if you query your ReportServer database, you can’t tell which user ran the report; they are all listed as the Trusted Account.
By placing the User into the parameter, we can pluck that value out, join to the UserRec table and then the PJEmploy table.
You can find your Trusted Web Service Account in screen 95.261.00
The script below will show you the report name, execution time, parameters, the SLUserID (that we extract from the parameters) and then the employee name.
You have some ‘To-do’s here…
- Get the value of your Trusted Web Service. Place it into the query were you see
- Enter your domain where you see
- Enter your System database where you see
- Enter your Application database where you see
use ReportServer; go ;with cte as ( select c.name, l.timeend, l.Parameters, case when l.username = 'YOUR_TRUSTED_ACCOUNT_FROM_SL' then case when charindex('SLUserID',l.parameters) = 0 then '' else substring(l.parameters,charindex('SLUserID',l.parameters, 0)+9, charindex('%',l.parameters,charindex('SLUserID',l.parameters,0))-charindex('SLUserID',l.parameters, 0)-9) end else Replace(UPPER(l.username),'YOUR_DOMAIN\','') end as userid from ExecutionLogStorage l join catalog c on c.ItemID = l.reportid where l.timeend > getdate()-2 --how many days do you want to go back? and c.name not in ('SLUserID','AvailableSLUserIDs','LastWEDate') ) select c.name, c.timeend, c.parameters, c.userid, replace(e.emp_name,'~',', ') as emp_name from cte c left join SYS_DATABASE..userrec r on r.userid COLLATE DATABASE_DEFAULT = c.userid COLLATE DATABASE_DEFAULT left join APP_DATABASE..pjemploy e on e.user_id COLLATE DATABASE_DEFAULT = r.userid COLLATE DATABASE_DEFAULT order by timeend desc
One quick note…the ExecutionLogStorage has a 60 day ‘expiration’ which means it deletes rows older than 60 days. You can extend this if you want by updating the
ConfigurationInfo table in your ReportServer database.
select * from ConfigurationInfo where name = 'ExecutionLogDaysKept'
update ConfigurationInfo set Value = 'days_to_keep' where name = 'ExecutionLogDaysKept'