October « 2011 « tim laqua dot com

tim laqua dot com Thoughts and Code from Tim Laqua


Monitoring and Starting Data Collector Sets with Powershell

I figure it's about time I posted an update to my previous post on Monitoring, Starting, and Stopping Perfmon Performance Counter Logs using VBScript. In the years since that post, a few operating systems came out and many much more interesting languages happened by. So this update is in Powershell. You could do it in pretty much any language, but Powershell seems to be my weapon of choice lately.

The basic premise here is that we want our Data Collector Sets to always be running. Seems like a reasonable request, but for various reasons, Data Collector Sets just... stop. This script should be run regularly (every 5 minutes?) to ensure that the sets keep collecting data. In the event that the script finds some work to do, it will send you an email.

#======= START Config ========
$emailServer = "";
$fromAddress = "you@yourcompany.com";
$toAddress = "you@yourcompany.com";
$ignorePattern = '^(RandomCollectorSet|OSTest)$';
#======== END Config =========

$ErrorActionPreference = "SilentlyContinue"
$serverName = Get-Content env:computername;
$emailBody = ""

$collectors = New-Object -COM Pla.DataCollectorSetCollection;
$collectors.GetDataCollectorSets($null, "Service\*");
$collectors._NewEnum | ?{$_.Status -ne 1 -and $_.Name -notmatch $ignorePattern} | %{
	if($_.Status -eq 1) {
		$emailBody += "Started $($_.Name)`n"
	} else {
		$emailBody += "Failed to start $($_.Name)`n"

if($emailBody -ne "") {
  $smtp = new-object Net.Mail.SmtpClient($emailServer);
    "$serverName <$fromAddress>", 
    "Started Data Collector Set(s)", 

If you want the script to keep any of the non-user created collectors running, modify the namespace parameter of the GetDataCollectorSets method call.

Tagged as: 1 Comment

Pattern for Conditionally Sending SSRS Subscriptions

This is a pretty common request - send the report if there's an issue, don't send it if there's not an issue. This is a simple pattern for doing that using Data Driven Subscriptions. Here, we're not using the data driven subscription to dynamically populate recipient and parameter information, we're just using it to suppress the sending of the report if the report has no value.

To do this, we only need one thing - a query that returns one row if the report should be sent and zero rows if the report should not be sent. Here is an example - Let's say I want to send a report out if a JDE Address Book record with an AT1 of 'DC' has been updated after a certain julian date.

First, write a query to return rows meeting that criteria:

Tagged as: , Continue reading

Script for Ad-Hoc SSRS TimedSubscription Scheduling

Quick script to locate and execute SSRS timed subscriptions (useful for testing).

1. Execute the top (uncommented) part and identify which subscription you want to schedule.
2. Paste the SubscriptionId from that row in to the @id variable in the lower section
3. Run the entire lower (commented) section

DECLARE @ReportName VARCHAR(255) = 'Report Name'

      ,CAST(b.Parameters AS XML) AS [Parameters]
      ,CAST(b.ExtensionSettings AS XML) AS [ExtensionSettings]
from ReportServer.dbo.[Catalog] a
      INNER JOIN ReportServer.dbo.[Subscriptions] b
            ON b.Report_OID = a.ItemID
where a.Name = @ReportName

       @id varchar(260) = ''
      ,@Type VARCHAR(32)                  = 'TimedSubscription'

EXEC [ReportServer].dbo.AddEvent @EventType=@Type, @EventData=@id

Tagged as: No Comments