Monitoring SSRS Subscriptions with Powershell

We don’t use SSRSmuch 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.

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