Monitoring SSRS Subscriptions with Powershell

Posted on November 25, 2011, 1:17 pm, by Rhys, under DBA, Powershell, SSRS.

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;

powershell ssrs1 Monitoring SSRS Subscriptions with Powershell

 


Tags: , ,
Comment (RSS)  |  Trackback

5 Comments

  1. Pasha says:
    January 5, 2012 at 11:20 pm

    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:
    April 23, 2012 at 8:18 pm

    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:
    April 24, 2012 at 12:01 am

    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:
    May 15, 2012 at 5:56 pm

    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:
    May 15, 2012 at 6:31 pm

    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;
		}
	}
}

Leave a Reply

 
«
»