April 25, 2017 at 12:00 am
Comments posted to this topic are about the item SSRS Audit: All reports executed/not-executed during previous 30 days
April 25, 2017 at 5:44 am
I think the default retention period for SSRS logging is 60 days, rather than 30 - unless you specify an amount of time in the queries, they are actually 'reports that have run (or not run) within the retention period'.
I like to configure SSRS to keep execution records for 400 days - it ensures you pick up annual reports, and any odd one-offs, during a fairly long period. Usually things change enough in a year that keeping data for longer than that does not have a lot of value for tracking operations (though your mileage may vary!).
To keep log records indefinitely, set the retention period to -1.
WARNING: I've seen semi-official documentation that says setting the period to 0 days will do this - this is WRONG. Setting it to zero will prevent any execution logging from happening, and will clear any existing entries. I discovered this in the obvious way - fortunately in a test environment!
April 25, 2017 at 1:57 pm
the execution log table? is that only for subscriptions? The catalog table has a column lastaccessed, but in my case out of something like 1800 reports around 1300 of them this column is null.
I'm in the process of migrating from SQL 2005 to SQL 2016 SSRS and was checking reports to make sure they work. MS had some sample reports for stuff like this too and I used another query to get the reports that were most used to check first
April 26, 2017 at 6:31 am
Since you are doing a MAX on the time executed and grouping by user, your top query is only counting the number of users that executed the report, not the actual amount of executions.
May 3, 2017 at 10:17 am
The original query didn't do it for me. It was giving odd results like saying a TimesExecuted is 2 but ExecutedBy lists 7 distinct users. That makes no sense. I did some modifications to it and I get better results, but the results now take ages to come through.
Here is my modified version:DROP TABLE IF EXISTS #temp01
SELECT DISTINCT
SUBSTRING(t2.Path,1,LEN(t2.Path)-LEN(t2.Name)) AS Folder
,t2.Name
,REPLACE(t1.UserName,'JACKPOT\','') AS UserName2
,MAX(t1.TimeStart) OVER (PARTITION BY [t2].path, [t2].name) AS LastExecuted
,[t1].[TimeStart]
INTO #temp01
FROM ReportServer.dbo.ExecutionLog t1 WITH(NOLOCK)
JOIN ReportServer.dbo.Catalog t2 WITH(NOLOCK) ON t1.ReportID = t2.ItemID
--GROUP BY t2.Path,t2.Name
ORDER BY Folder,Name, [t1].[TimeStart]--,t1.UserName
SELECT DISTINCT
Folder AS ReportFolder
,Name AS ReportName
,COUNT(Name) OVER (PARTITION BY folder, name) AS TimesExecuted
,LastExecuted AS LastExcecutedDate
,ExecutedBy = STUFF(( SELECT ', ' + [Y].UserName2
FROM (SELECT DISTINCT USERNAME2, name, folder FROM #temp01) Y WHERE Y.Name = X.Name AND Y.folder = X.Folder FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,'')
FROM #temp01 x
WHERE Name <> ''
ORDER BY TimesExecuted DESC,ReportFolder,Name
When I say it took longer, the original took 3 seconds to complete on our system, my modified one took 22 seconds to complete. BUT it gave a lot beter "Times Executed" results and "Executed By" results. There are probably some optimizations that can be done, but it gets the job done. I also added in "with(nolock)" to the permanent tables to prevent blocking and changed the try catch block into a drop table if exists as it feels cleaner to me. If memory serves though, that is a 2014 and newer feature (possibly 2016)? My version is not very friendly to old SQL versions.
EDIT 1: The above also assumes you have no reports that have the same name but in different folders :/ I'm playing with the code and will fix it shortly.
EDIT 2: code above updated. It should be better now. On my system it is giving better results anyways. Another problem with the original is it would put the UserName2 for a report being executed by someone based on the report name alone. So it had the same problem as my original; duplicate report names would result in the executed by field getting their name tossed into it. That was why I had the 2 times executed by 7 users.
The above (based on my testing) seems to correct these issues. I now see that report as being executed 24 times by 2 distinct users.
EDIT 3: simplified version of the "reports not executed" report:SELECT path, name, [Catalog].[CreationDate], [Catalog].[ModifiedDate]
FROM ReportServer.dbo.Catalog
WHERE [Catalog].[Name] NOT IN (
SELECT name FROM ReportServer.dbo.ExecutionLog t1 JOIN
ReportServer.dbo.Catalog t2
ON t1.ReportID = t2.ItemID AND t2.Type <> 1)
AND TYPE = 2
TYPE = 2 means report. 5 is a data source and 1 is a folder. This method is slower than the original as well (12 seconds on my system vs the 1 with the previous version) but is less code. Probably some optimizations you could do in there too. Those "NOT IN (SELECT" commands I know are slow.
EDIT 4:
sigh... I need to let this be soon, but I'm having fun optimizing! faster version of EDIT 3:DROP TABLE IF EXISTS #temp01
SELECT distinct name
INTO #temp01
FROM ReportServer.dbo.ExecutionLog t1 JOIN
ReportServer.dbo.Catalog t2
ON t1.ReportID = t2.ItemID AND t2.Type = 2
SELECT path, name, [Catalog].[CreationDate], [Catalog].[ModifiedDate]
FROM ReportServer.dbo.Catalog
WHERE [Catalog].[Name] NOT IN (
SELECT name FROM #temp01)
AND TYPE = 2
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 17, 2020 at 8:58 am
Just my 2p-worth....
We have standard headers on all our reports and, as part of those headers, it runs a stored procerure that logs who ran the report and when.
We can then query our own home-grown log table to find out when a report was last run, when, etc.
We also have home-grown security where users request access to a report (and that is logged in the same table with a different Event Type). The report then checks if the user has permissions by querying a permissions table and, assuming you don't have access, stops the report from running.
We did it this was as we found that users were sharing URLs with people that shouldn't be running certain reports and this was the easiest way for us to kill off the permissions.
Part of this also logs when when a user last used a report so permissions can be rescinded if they don't use run the report in 3 months (that's an arbitrary figure that works for us).
January 20, 2020 at 5:23 pm
thank you for sharing, it's a very good starting point. I changed a lot to use CTE only instead of using both CTE and temp table. I also added an interval integar parameter so that I can fetch 30/90/360 days. default 30 days(now 60 days) was often not used and I have one year history.
January 20, 2020 at 5:59 pm
Something to note - SOMETIMES using a CTE and temp table together will give better performance. SOMETIMES it is better to look at using JOINs instead of CTEs. And sometimes readability is more important than performance (my opinion, readability is always more important than performance, at least where I work. Your workplace may put the preference on performance).
And there are other times where the performance improvements are shaving off fractions of a second on a query that is executed 1 time per month in which case it is not worth the optimization. But sometimes when you change your query to include a temp table you can notice a huge performance boost over not using it. In my examples above, with testing on my machine, the last example which uses a temp table had the best performance.
Plus, different SQL Server versions can have different performance.
richardmgreen1 - Since the report name, execution time and who executed it is already being logged in the SSRS server, doesn't having a special header that calls a stored procedure to drop that data in cause unnecessary overhead? As for permissions, SSRS already has a built-in permission system for both reports and folders. Why recreate those at the report level? Not trying to make you change your process, I am just trying to think of a good reason to recreate these.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 20, 2020 at 9:48 pm
thank you, Brian, good to know the benefit of using CTE and temp table together. I sometimes convert table variable to temp table due to very bad performance issue.
January 21, 2020 at 9:48 am
Brian - We decided to do it that way as we have a menu system (again, homegrown) to replace the normal folder structure in SSRS.
On top of that, we can also track when a specific user last ran a report a lot easier (for us at least) and it's easier for us to revoke access to a report if needed or to allow access to die off naturally if a report isn't used by a user in a given time period.
The usual routine for us as follows:-
If access has been revoked, users have to re-request access to the report and the whole things starts again.
For us, it makes access to reports a lot easier to manage and reporting on who is running what report is a nice by-product of that.
We also did it that way to stop people sharing the URLs for reports and running reports they shouldn't have access to (we all know that sometimes people do have access to reports they shouldn't due to oversights when setting up security).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply