none
Query ExecutionLogStorage for reports never ran for the past 30 days

    Question

  • I know there's a table calls 'ExecutionLogStorage' and it contains the historical data for the list of reports ran, by whom, the time it takes, and etc.  How do I write a query to find out how many reports did not get used or run the past 30 days?  Ours SSRS is integrated with SharePoint.

    Thanks.

    Wednesday, April 24, 2013 9:02 PM

Answers

  • Hello ,

      Try the below code

    DECLARE @NotUsedDays INT
    SELECT @NotUsedDays = 30
    SELECT Name,Path,LastUsedDate,NotUsedsince=DATEDIFF(DD,LastUsedDate,GETDATE())
      FROM dbo.catalog C
      JOIN (
    		  SELECT ReportID,LastUsedDate= MAX(timestart) 
    			FROM dbo.executionlog
    		   GROUP BY ReportID
    	   ) E
    	ON C.ItemID = E.ReportID
     WHERE DATEDIFF(DD,LastUsedDate,GETDATE()) >= @NotUsedDays


    Best Regards Sorna

    Thursday, April 25, 2013 8:29 AM