SharePoint Integrated Mode - Where can I find what reports are associated with which shared schedule(s)?

Question

SharePoint Integrated Mode - Where can I find what reports are associated with which shared schedule(s)?

asheppardwork

SSCommitted

Points: 1667
More actions
December 12, 2013 at 2:44 pm

#280307

One would think this is fairly simple. All I want is to find out in SharePoint running SSRS 2008 R2 (Integrated Mode) what reports are associated with which shared schedules?
I have searched this forum SQL Server Central, and Google; till I am literally nauseated and ready to throw my monitor out of a window. All I can find is queries like I have at the bottom; or instructions (not helpful at all btw) on how to view where the shared schedules are; or get the Guid from the Sql Server Agent Job Activity Monitor and helplessly match it up with the schedule on the "Manage Shared Schedules" screen. However, NONE of these options tell me WHAT report is on WHICH schedule. SharePoint Integrated mode has been such a pain in the general gluteaus maximus that I will NEVER recommend it to ANYONE ever and this is just one of many simple tasks that take an entire afternoon when dealing with more than a dozen reports.
In the SSRS Native Mode, all you do is go to Site Settings > Schedules click on a schedule and then click Reports and TAH DAH a freakin list. Please someone save my sanity and tell me how to do this in Integrated Mode, so I can get us back to Native and leave this backwards, overly complicated, poorly designed, poorly documented, poorly executed piece of flotsam off my network.
Thanks 🙂
USE
ReportServer
SELECT
Schedule.ScheduleID AS SQLAgent_Job_Name, Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt,
[Catalog].Name AS ReportName, [Catalog].Path AS ReportPath, StartDate,NextRunTime,Schedule.LastRunTime,EndDate,Schedule.EventType,ExtensionSettings,Subscriptions.Description,LastStatus
FROM ReportSchedule
INNER JOIN Schedule
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN Subscriptions
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [Catalog]
ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID
WHERE
ExtensionSettings
Like '%blahblahblah%' --use to find who is on the to to,cc,bcc field settings
ORDER
BY
Schedule.LastRunTime DESC
USE
ReportServer
SELECT
Schedule.ScheduleID AS SQLAgent_Job_Name,
Subscriptions.Description AS sub_desc,
Subscriptions.DeliveryExtension AS sub_delExt,
[Catalog].Name AS ReportName,
[Catalog].Path AS ReportPath,
Schedule.LastRunTime AS LastRun
FROM ReportSchedule
INNER JOIN Schedule
ON
ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN Subscriptions
ON
ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [Catalog]
ON
ReportSchedule.ReportID = [Catalog].ItemID
AND
Subscriptions.Report_OID = [Catalog].ItemID
WHERE
Subscriptions.DeliveryExtension = 'Report Server Email'
ORDER
BY
Schedule.LastRunTime DESC

Viewing 3 posts - 1 through 2 (of 2 total)

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

asheppardwork SSCommitted Points: 1667 More actions · Answer 1

Major props go out to Trevor Seward on TechNet for providing a way to see all the subscriptions via a PowerShell Script.

http://social.technet.microsoft.com/Forums/sharepoint/en-US/6950236a-61a4-4430-bc1c-ec4a50555e03/where-can-i-find-what-reports-are-associated-with-which-shared-schedules?forum=sharepointadminprevious#6950236a-61a4-4430-bc1c-ec4a50555e03

Seriously,

Thanks Trevor

:w00t:

asheppardwork SSCommitted Points: 1667 More actions · Answer 2

Trevor's Script:

$siteUri = "[yourSharepointSiteHere]"

$proxy = New-WebServiceProxy -Uri "$siteUri/_vti_bin/ReportServer/ReportService2010.asmx" -UseDefaultCredential -Namespace "SSRS"

$proxy.ListSubscriptions($siteUri) | Export-CSV -NoType -Path c:\ReportsSubs_List.csv

Trevor's blog post to get more info:

http://sharepoint.nauplius.net/2013/12/quickly-identifying-reporting-services-subscriptions/