Monitoring SSRS Subscriptions with Powershell

We don’t use SSRS much at my workplace but its usage is slowly creeping up. I realised that none of us are keeping an eye on the few subscriptions we have set-up. So I decided to do something about that.

Here’s a bit of Powershell code that uses the SSRS Web Service to pull out a list of subscriptions from SSRS and print out some information to the screen. As always I try to bring problems to attention with a little red text so you can identify any failed subscriptions. There’s a few assumptions in the script, so be sure to read the comments, and it’s a far from complete and fully tested script. Just a little something to get me started onto something better.

?View Code POWERSHELL
# If you use multiple ssrs server just stick in another foreach loop
# and iterate over an array of ssrs server names.
$reportserver = "sqlserver";
$url = "http://$($reportserver)/reportserver/reportservice.asmx?WSDL";
 
$ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential -Namespace "ReportingWebService";
 
# Uncomment to list all available methods & properties in the ssrs web service...
#$ssrs | Get-Member;
 
# Get datasources (assumes default path to folder)
$datasources = $ssrs.ListChildren("/Data Sources", $true);
 
# for each datasource
foreach($ds in $datasources)
{
	# Get reports
	$reports = $ssrs.ListReportsUsingDataSource($ds.Path);
	# For each report
	foreach($r in $reports)
	{
		# Get all subscriptions
		$subscriptions = $ssrs.ListSubscriptions($r.Path, $r.Owner);
		# foreach subscription
		foreach($s in $subscriptions)
		{
			# Uncomment to view more subscription methods & properties
			# $s | Get-Member;
			# Probably missing a few important keywords here...
			$colour = "Green";
			if($s.Status -match "Failure" -or $s.Status -match "Error")
			{
				$colour = "Red";
			}
			Write-Host -ForegroundColor $colour $s.Report $s.Status $s.LastExecuted | Format-Table -AutoSize;
		}
	}
}

After running this script successfully you should be something like below;

 


12 Comments

  1. Pasha says:

    I get an error when running this script (and I had to modify the url for SQL Server 2008 R2)

    Method invocation failed because [ReportingWebService.ReportingService2005] doesn’t contain a method named ‘ListReportsUsingDataSource’.
    At line:14 char:45
    + $reports = $ssrs.ListReportsUsingDataSource <<<< ($ds.Path);
    + CategoryInfo : InvalidOperation: (ListReportsUsingDataSource:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

    I have modified line 14 to make it work:
    $reports = $ssrs.ListDependentItemsAsync($ds.Path);

    Thanks,

    Pasha

  2. Emily says:

    Does the script only work if all of your data sources are in one folder?
    I get these errors:

    You cannot call a method on a null-valued expression.
    At C:\test\ssrs3.ps1:12 char:34
    + $datasources = $ssrs.ListChildren <<<< ("/Data Sources", $true);
    + CategoryInfo : InvalidOperation: (ListChildren:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\test\ssrs3.ps1:18 char:45
    + $reports = $ssrs.ListReportsUsingDataSource <<<< ($ds.Path);
    + CategoryInfo : InvalidOperation: (ListReportsUsingDataSource:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\test\ssrs3.ps1:23 char:43
    + $subscriptions = $ssrs.ListSubscriptions <<<< ($r.Path, $r.Owner);
    + CategoryInfo : InvalidOperation: (ListSubscriptions:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

  3. Rhys says:

    Hi Emily,

    It’s been a long time since I wrote this. But I think there wasn’t a method to return all of the available data sources (at least in 2005). Makes you wonder but C’est la vie.

    Cheers,

    Rhys

  4. Rhys says:

    For those wondering the format for the web service url for SSRS 2008 R2 should be…

    http://hostname/reportserver/ReportService2010.asmx?wsdl

  5. Rhys says:

    In addition to Pasha’s comment about line 14 I needed to change this line…

    $subscriptions = $ssrs.ListSubscriptions($r.Path, $r.Owner);

    to

    $subscriptions = $ssrs.ListSubscriptions($r.Path);

    to get it working under SSRS 2008 R2. Tested script for SSRS 2008 R2…

    ?View Code POWERSHELL
    # If you use multiple ssrs server just stick in another foreach loop
    # and iterate over an array of ssrs server names.
    $reportserver = "soProdSSRS02";
    # 2005
    #$url = "http://$($reportserver)/reportserver/reportservice.asmx?WSDL";
    # 2008 R2
    $url = "http://$($reportserver)/reportserver/ReportService2010.asmx?wsdl";
     
    $ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential -Namespace "ReportingWebService";
     
    # Uncomment to list all available methods & properties in the ssrs web service...
    #$ssrs | Get-Member;
     
    # Get datasources (assumes default path to folder)
    $datasources = $ssrs.ListChildren("/Data Sources", $true);
     
    # for each datasource
    foreach($ds in $datasources)
    {
    	# Get reports
    	$reports = $ssrs.ListDependentItemsAsync($ds.Path);
    	# For each report
    	foreach($r in $reports)
    	{
    		# Get all subscriptions
    		# 2005
    		#$subscriptions = $ssrs.ListSubscriptions($r.Path, $r.Owner);
    		# 2008
    		$subscriptions = $ssrs.ListSubscriptions($r.Path);
    		# foreach subscription
    		foreach($s in $subscriptions)
    		{
    			# Uncomment to view more subscription methods & properties
    			# $s | Get-Member;
    			# Probably missing a few important keywords here...
    			$colour = "Green";
    			if($s.Status -match "Failure" -or $s.Status -match "Error")
    			{
    				$colour = "Red";
    			}
    			Write-Host -ForegroundColor $colour $s.Report $s.Status $s.LastExecuted | Format-Table -AutoSize;
    		}
    	}
    }
  6. smarin says:

    Great article, some modifications were required to get this to function in my SSRS 2008 server environment, cheers!

    # If you use multiple ssrs server just stick in another foreach loop
    # and iterate over an array of ssrs server names.
    $reportserver = “YourSSRSServerInstance”;
    # 2005
    #$url = “http://$($reportserver)/reportserver/reportservice.asmx?WSDL”;
    # 2008
    $url = “http://$($reportserver)/reportserver/ReportService2005.asmx?wsdl”;
    # 2008 R2
    #$url = “http://$($reportserver)/reportserver/ReportService2010.asmx?wsdl”;

    $ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential -Namespace “ReportingWebService”;
    # Uncomment to list all available methods & properties in the ssrs web service…
    #$ssrs | Get-Member;
    # Get datasources (assumes default path to folder)
    $datasources = $ssrs.ListChildren(“/General/Data Sources”, $true);
    # for each datasource
    foreach($ds in $datasources)
    {
    # Get reports
    $reports = $ssrs.ListDependentItems($ds.Path.ToString());

    # For each report
    foreach($r in $reports)
    {
    # Get all subscriptions
    # 2005/2008
    $subscriptions = $ssrs.ListSubscriptions($r.Path, $r.Owner);
    # 2008 R2/2012
    # $subscriptions = $ssrs.ListSubscriptions($r.Path);
    #
    # foreach subscription
    foreach($s in $subscriptions)
    {
    # Uncomment to view more subscription methods & properties
    # $s | Get-Member;
    if($s.DeliverySettings.Extension -like “*Email”)
    {
    if($s.Status -notlike “*0 error*” -or $s.Status -like “*Fail*” ) { $colour = “Red”; } else { $colour = “Green”; }
    if($s.Status -like “Mail sent*”) { $colour = “Green”; }
    Write-Host -ForegroundColor $colour $s.Report $s.Status.ToString() $s.LastExecuted | Format-Table -AutoSize;
    }
    }
    }
    }

  7. maya says:

    Hi,
    Is there an option to configure the subsciptions through PowerShell?

  8. Rhys says:

    Hi Maya,

    yep, there is .

    Rhys

  9. David Ocampo says:

    So, if there was a bad email within a subscription, I could use this script to remove it from any subscription?

  10. Rhys says:

    Hi David,

    Not really. You’d have to write a fair bit of code in addition. What do you mean by “bad email”. If you mean format then it would make sense to validate this on the way in.

    Cheers,

    Rhys

  11. David Ocampo says:

    Sorry for the ambiguity. By “bad email” I meant an email that was once valid and, now, is no longer valid (e.g., person left the company).

    It would be useful if I had code that would remove his email from all of his subscriptions, not necessarily the ones he owned, simply those that were emailed to him.

  12. Rhys says:

    Hi David,

    You could probably do it through the SetSubscriptionProperties method of the web service api.

    Regards,

    Rhys

Leave a Reply