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

One Comment

  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

Leave a Reply

 
«
»