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

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

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

  • Parker Smith

    Mr or Mrs. 500

    Points: 591

    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!

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    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

  • zelsbrock

    SSC Enthusiast

    Points: 142

    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.

  • bmg002

    SSC-Insane

    Points: 22420

    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

Viewing 5 posts - 1 through 5 (of 5 total)

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