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.

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.

$reportServerurl = "http://$SSRSServerName/Reportserver/ReportService2010.asmx?wsdl"

    try
    {
      #Create Proxy
      if ($PSCmdlet.ShouldProcess($reportServerurl))
      {
        $global:proxy = New-WebServiceProxy -Uri $reportserverurl -UseDefaultCredential -Namespace SSRS -ErrorAction stop
      }
    }
    catch
    {
      Write-Error $error[0].Exception
    }

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.

 $Subscriptions = $Proxy.listSubscriptions("/")
    #Path search    
    if ($Path)
    {
      if ($WildCardSearch)
      {
        $Subscriptions = $Subscriptions | Where-Object Path -like "$Path*"
      }
      else
      {   
        $Subscriptions = $Subscriptions | Where-Object Path -eq $Path
      }
    }
    #Owner search
    if ($Owner)
    {
      $Subscriptions = $subscriptions | Where-Object Owner -eq $Owner
    }

    #Description search
    if ($Description)
    {
      $Subscriptions = $Subscriptions | Where-Object Description -eq $Description
    }
    $Subscriptions

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.

[ref]$extSettings = $null
    [ref]$description = $null
    [ref]$activestate = $null
    [ref]$status = $null
    [ref]$eventtype = $null
    [ref]$matchdata = $null
    [ref]$ParamValues = $null
   
    #$proxy.DisableSubscription($subscriptions.SubscriptionID)
    #$proxy.EnableSubscription($subscriptions.SubscriptionID)
    Try
    {
      $proxy.GetSubscriptionProperties($Subscriptionid, $extSettings, $description, $activestate, $status, $eventtype, $matchdata, $paramvalues) | Out-Null
    }
    Catch
    {
      Write-Error $error[0].Exception
    }
    #create new xml variable to add/change nodes
    [xml]$xmlmatch = $matchdata.Value

 Try
      {
        if ($PSCmdlet.ShouldProcess($SubscriptionID))
        {
          $proxy.SetSubscriptionProperties($subscriptionID, $extSettings.Value, $description.value, $eventtype.Value, $xmlmatch.OuterXml, $ParamValues.value)
        }
        
      }
      Catch
      {
        Write-Error $error[0].Exception
      }

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!

 #check to see if end date exists as a node
      $EndExists = $xmlmatch.SelectNodes("//*") | Select-Object name | Where-Object name -eq "EndDate"

      #if enddate doesn't exist in nodes create it under scheduledefinition parent
      if ($EndExists -eq $null)
      {
        $child = $xmlmatch.CreateElement("EndDate")
        $child.InnerText = $EndDate
        try
        {
          $xmlmatch.ScheduleDefinition.AppendChild($child)
        }
        catch
        {
          Write-Error $error[0].Exception          
        }

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.

 else
      {
        try
        {
          $xmlmatch.ScheduleDefinition.EndDate.InnerText = $enddate
        }
        catch
        {
          Write-Error $error[0].Exception
        }

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

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.
  

• #load the functions
  
  . "Z:\G\DEMO\SSRS\SSRSSubscriptions.ps1"
  #create the proxy
  set-ssrswebproxy -SSRSServerName WIN-EB8DHRH79KL
  

---

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.

Get-SSRSSubscriptions -description "really important" 

Get-SSRSSubscriptions -path "/Important/Time and Expense/EMP1"

Get-SSRSSubscriptions -Path "/Important/Time and Expense/" -WildCardSearch

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

Get-SSRSSubscriptions -Path "/Important/Time and Expense/" -WildCardSearch -description "really important"

Get-SSRSSubscriptions -Path "/Important/Time and Expense/" -WildCardSearch | Set-SSRSsubscription -enddate "11/1/2017"

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