Update multiple SSRS subscriptions

warrenPowershell, SSRS0 Comments

If you have a reporting environment you probably have a report or 2-hundred. You may even have multiple subscriptions per report. So what happens if you want to update, or add, something to hundreds of subscriptions, or just a very specific subset?

What is the best way to do this? ( I seriously had no idea and typically only deal with SSRS every leap year)

In a conversation with someone at work, I assumed that updating multiple SSRS subscription dates programmatically was 300% possible. Although I was way off in my math, I was totally correct in my non-GUI assumption. However, It didn’t happen in the time frame, or language, I had hoped.

last time I dealt with this many subscriptions, it involved a penny, scotch tape, Milli Vanilli and tears (for fears and sadness)

Going A Little Crazy

After poking around the reporting database I did find some tables that might work:

  • dbo.Catalog
  • dbo.Subscriptions
  • dbo.Schedule
  • dbo.ReportSchedule

Surely one of these tables has what I’m looking for. I could probably update a column, or find a system stored procedure for this simple task.

I tried updating [EndDate] manually in dbo.Schedule, but that didn’t work.  The changes didn’t reflect in the GUI. Such a rookie mistake here going for the quick fix. EndDate in a table named schedule? Silly me.

End date in a schedule table? HAHAHA MeaninglessSSRS

Finally turning to the wisdom of the Internet, I did find a post on dba.stackexchange referencing the real [EndDate] from a column in dbo.Subscritions called… wait for the descriptive name…..[MatchData]!

I could update the [MatchData] XML in dbo.subscriptions, if I wanted to work with XQuery. I could also replace an existing value in the MatchData ntext. However, this means I have to know the existing value ahead of time for the replace. This did work, but just wasn’t an efficient option when different scenarios present themselves, or working with more than one subscription.

State Of The Art Offer

My hamster wheel slowly started turning after reading a blog post from MVP Andy Mallon(b|t) regarding exporting and Importing report files. This post ultimately led me down a different path than one I had anticipated. I could ditch T-SQL and let the XML hate flow through me to fuel my Powershell dark side.

After investigating how SSRS actually works behind the scenes, thanks to @amtwo’s blog post kick in the tookus, I realized I could simply interact with SSRS via SOAP API and use Powershell to manipulate the XML. Excellent!

The cmdlet I used was New-WebServiceProxy creating a proxy object allowing me to interact with the SSRS webservice.

The methods I used are:
ListSubscriptions – to get subscriptions based on a property
GetSubscriptionProperties – Returns properties for a subscription
SetSubscriptionProperties – Sets properties for a subscription

The workflow is fairly simple and I created 3 functions to work with the above methods.

  • Set-SSRSWebProxy
    • Instantiate a web proxy object
  • Get-SSRSSubscriptions
    • Call method ListSubscription on created proxy to get subscriptions based on either folder, author or description
  • Set-SSRSSubscriptions  (takes pipleline info from get-ssrssubscriptions)
    • Call getSubscription to get existing subscription data, using passed subscriptionID
    • Call setSubscription to update subscription data, using passed subscriptionID

1. Instantiate web proxy object
with function Set-SSRSWebProxy
The SSRS proxy is assigned to a global variable since I couldn’t figure out how to pass it directly to another function. This $proxy variable is referenced in 2 downstream functions.

I am also only using the current user authentication here with default credentials. If you need anything else you will need to create a credential object with get-credential and add it to this function.

2. Get a list of subscriptions with function Get-SSRSSubscriptions
Simply using the ListSubscriptions method to get all subscriptions and predicate on path, owner or description.

3. GetSubscription Meta Data with function Set-SSRSSubscriptons
[ref] is a type accelerator for [System.Management.Automation.PSReference] . I needed to assign a null value to create the variable when declared as psreference. MVP Adam Bertram wrote a great powershell specific overview here

xml manipulation happens after we get the subscription information.

4. Set Subscription Information with function Set-SSRSSubscriptions
Here I am passing back most of the variables pulled from the GetSubscription method, except for the XML MatchData that is updated based on parameters.

The real magic happens when looking at the code to either update node text or add a new node to the XML with Powershell.
Here i’m checking for the presence of the enddate node in the XML. If it doesn’t exist add it!
Powershell XML easy button engaged!

If the node does exist, just assign the innertext to my EndDate variable. Also by using the API I don’t have to format the StartDateTime using any offsets. The API does that for me.

I’m not sure about you, but dealing with control flow and XML is mucho mucho easier in Powershell than T-SQL

Here come the hits

Now that I have the basic methods outlined, I can utilize advanced functions to simply pass all the subscriptions from a get to a set and then it’s Miller Time.

I can use the functions I created to get all, or some, subscriptions based on path, owner and/or description. Let’s say I need to update the EndDate to all my subscriptions associated with anything in the “Time and Expense” folder and all subsequent folders. The report EMP1 has 2 subscriptions associated with it.

  • After dot sourcing my functions, I instantiate a web proxy service object to manage my SSRS instance. Again , if you need different credentials, I didn’t write that in here because I didn’t need it. You could easily adapt this to pass a credential object.

Now I can list all the subscriptions based on a specific path, author, or description. If I want to only change the “really important” subscription, I would need to specify this in the description variable like in the code on  Line 1

If I wanted to change all subscriptions for a specific report, I could simply specify this in the path parameter on  Line 3. The only drawback to this is that I need to know the exact path.

But what if I want to update all subscriptions in a folder, not a specific path?

Using the switch -WildCardSearch on  Line 5  below I can simply specify a root folder I want to return all the subsequent subscriptions for. This is exactly what I was trying to accomplish from the beginning.

You can see below both subscriptions to my EMP1 report are returned based on the root folder.

Using the pipleline I can now update all the subscriptions with 1 line of code.
I could also add an owner, or description to this to narrow down the list even more on line 3

Another advantage is that powershell will check the EndDate parameter to make sure the date is valid. If you are replacing dates within matchdata directly with xquery or replace, you could end up making a mistake. A friend I know updated the EndDate to 9/31 with T-SQL and received a generic “An Error has Occured” in the Portal. If this does happen you can simply look in the report server error logs to find the exact GUID offender.

After running the code above on line 3, all the subscriptions in the /Important/Time and Expense/ folder were set To End on 11/1/2017 and verified in the report management portal.

As always, test in non prod and these functions can easily be modified to add additional scheduling needs.

You can find all 3 functions in the following github repository: warren2600/SSRSSubscriptions