Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql to list all jobs on SSRS web interface ?? Expand / Collapse
Author
Message
Posted Thursday, August 14, 2014 12:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
Hi

Not sure if I am explaining this correctly.. but...

The web interface for SSRS which we call Report Store has gotten a bit out of hand...
I would like to write an SP to list all the reports that have been deployed, their name, last use (if possible) and path

Any info, tables etc.. would be great

Thanks
Joe
Post #1603428
Posted Thursday, August 14, 2014 5:31 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 839, Visits: 5,423
http://stackoverflow.com/questions/1179082/ssrs-tracking-report-usage was interesting and probably will help you.

It mentions that once you determine the reports that nobody uses that you can move them to unclutter your RS instance.
Post #1603502
Posted Friday, August 15, 2014 10:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:11 AM
Points: 10,282, Visits: 13,264
The solution pointed to by Piet is a good starting point. I'd modify it to do something more along these lines:

SELECT
Catalog.Type,
Catalog.Name,
Catalog.Path,
MAX(ExecutionLog.TimeStart) AS lastUsed
FROM
dbo.Catalog
LEFT JOIN dbo.ExecutionLog
ON Catalog.ItemID = ExecutionLog.ReportID
WHERE
Catalog.Type = 2
GROUP BY
Catalog.Type,
Catalog.Name,
Catalog.Path

The LEFT JOIN ensures that you will get all the reports currently with a NULL in the lastUsed column if there is no longer a row in the Execution Log because the last use has aged out of the execution log.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1603780
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse