SCOM MP Authoring Example: Generate alerts based on entries from SQL Database (Part 1 of 2)

This is probably going to be a bit too long for one single blog post. I’ll separate this topic into 2 articles:

1. Part 1 includes the background and overview of the rule and it’s workflow
2. Part 2 documents all the steps to create all the module types and the rule itself.

This article is the first part of the 2-part series.

Recently, I’ve been writing a SCOM management pack for a new application that my employer is implementing. This application logs any application related alarms into a SQL express database. One of the requirement for the MP is to catch these alarms from the database and generate alerts based on these alarms.

In the database, I’m interested in any records that has the value of “Alarm triggered” in “EventTypeCaption” column.

The the record is added to the database, the application also adds the time stamp in UTC to the “EventDate” field.

Below is a snapshot of a subset of the database. I’ve highlighted the records that I’m interested in:

To achieve this goal, I’ve written some custom modules and created a rule using these modules.

Rule overview:

As usual, the rule contains 3 modules:

1. Data Source
2. Condition Detection
3. Actions

Below is the flow chat for the entire workflow:

To explain the workflow in details:

1. The workflow takes 3 inputs:
   1. IntervalSeconds – how frequent does the rule run
   2. SQLInstance – Name of the SQL instance
   3. Database – Name of the database
2. The data source member module system.simple.scheduler runs according to the intervalseconds
3. The Probe Action member module (a PowerShell script) takes all 3 inputs:
   1. connect to the database in the SQL instance as specified from the input
   2. calculate the earliest time (current time minus intervalseconds from the input then convert to UTC). store the earliest time in a datetime variable $starttime
   3. Build the SQL query: "Select * from <table name> Where EventTypeCaption LIKE 'Alarm triggered' AND EventDate >= '$StartTime'"
   4. Execute the SQL query.
   5. If returned any data:
      1. Property Bag value "GenerateAlert" = True
      2. For each record, convert the EventDate from UTC time to local time.
      3. combine all records from the record set to a multi line string that include converted event date and event description. return this string as Property Bag value "LogEntry"
      4. return Property Bag Value "LogEntryCount"
4. Condition Detection module detects Property Bag value "GenerateAlert" = True
5. If passed Condition Detection Module, the Write Action module generates alert with LogEntry and LogEntryCount in alert description field.

Note: I’m using PowerShellPropertyTriggerOnlyProbe rather than VBscript because I found it’s easier to convert UTC and local time back and forth as I can simply use .NET class System.TimeZoneInfo and powershell datetime object ToUTC() method to do the conversion. if we are to use VBScript, there is no equivalent trigger only probe for VBScript. I’ll try to cover this in a separate blog post.

What’s Next?

I’ll go through how to create each module types and the rule itself in part 2 of this series.

To be continued…

Part 2