SSRS Audit: All reports executed/not-executed during previous 30 days

  • Comments posted to this topic are about the item SSRS Audit: All reports executed/not-executed during previous 30 days

  • 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!

  • 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

  • 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.

  • 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:

    SUBSTRING(t2.Path,1,LEN(t2.Path)-LEN(t2.Name)) AS Folder
    ,REPLACE(t1.UserName,'JACKPOT\','') AS UserName2
    ,MAX(t1.TimeStart) OVER (PARTITION BY [t2].path, [t2].name) AS LastExecuted
    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

    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:
    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

  • 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).

  • 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.

  • 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.

  • 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.

  • 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:-

    • User requests access to a report (which is logged)
    • User (usually) is granted access to said report (which, again, is logged)
    • Reporting system confirms that access has not expired (and user has access to report) before the report can be run (this happens every time)
    • If a user doesn't run a report in 3 months, we send them an email that asks if they still need access to the report
    • If they do, they can run the report which resets various fields
    • If they don't, they can ignore the email and access will be revoked a month after that

    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 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply