Monday, August 3, 2015

View All SQL Server Report Subscriptions with a Powershell Script

When running SSRS in stand alone mode, there is an option to "Manage My Subscriptions". This is helpful, but doesn't provide a lot of details about each subscription. If you have many subscriptions on the same report with different parameters, there is no way to tell them apart.

SSRS in SharePoint mode is even worse, because there is no way to view all subscriptions on one page. You must go to each report to view each subscription. Even from there, it gives almost no details about that particular subscription.

Searching around, I found two solutions:
  1. Run a query directly against the database tables
  2. Buy a $800 application (
The 2ndRock application is nice, but I don't have that kind of budget and it still doesn't provide exactly what I want since it doesn't export report Parameters.

Instead, I wrote a powershell script to find all reports and dump the data out to a CSV file. This has been tested in SSRS 2008 Stand alone mode and SP2013 with SSRS 2012.

Rather than dump the code here and have Blogger butcher it, I've uploaded the code to GitHub.