June 21, 2012 at 8:48 am
Anyone have code snippet to find reports that have not been used? Any help will be appreciated.
June 21, 2012 at 9:04 am
I put together this bit of code, but I am not sure if it is returning the correct reports.
Select c.Path
,c.Name as Report
,c.CreationDate as [Create Date]
,c.ModifiedDate as [Modified Date]
,e.LastUsed as [Last Used]
,e.UserName as [Last Used By]
From Catalog c
Left Join
(
Select ReportID
,max(TimeStart) as LastUsed
,UserName
From ExecutionLog
Group By ReportID, UserName
)e on c.ItemID = e.ReportID
Where c.Type = 2
AND e.LastUsed IS NULL
Order By c.Path ASC
June 21, 2012 at 9:10 am
Nice code. Looks accurate on my end.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2012 at 9:36 am
Thanks for the verification. It is appreciated.
June 21, 2012 at 9:40 am
bpowers (6/21/2012)
Thanks for the verification. It is appreciated.
You're welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2012 at 9:49 am
Something is not right though. I ran one of the reports, that showed up on my list, and it did not fall off. Not sure why.
June 21, 2012 at 10:01 am
Got it. Forgot to include the TimeStart in my Group By (Sub Query).
-- Reports that are not being used
Select c.Path as Folder
,c.Name as Report
,c.CreationDate as [Create Date]
,c.ModifiedDate as [Modified Date]
,e.LastUsed as [Last Used]
,e.UserName as [Last Used By]
From Catalog c
Left Join
(
Select ReportID
,max(TimeStart) as LastUsed
,UserName
From ExecutionLog
Group By ReportID, UserName, TimeStart
)e on c.ItemID = e.ReportID
Where c.Type = 2
AND e.LastUsed IS NULL
Order By c.Path ASC
June 22, 2012 at 2:19 am
There is the set of SSRS diagnostic reports which Mike Davis wrote
http://qa.sqlservercentral.com/articles/Reporting+Services+%28SSRS%29/69257/
Saves digging around in the RS db to get some useful information out of it.
June 22, 2012 at 6:51 am
Using standard configuration the Execution Log does not keep more than 60(?) days of information, so the posted queries are potentially mis-reporting. You need to keep an historical record of the execution log and report out of that.
July 9, 2012 at 7:17 am
it's interesting, but sometimes we wouldn't take much care about them.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply