• Redgate Monitor
• Resources and scalability

Using SQL Monitor with SCOM

In my last blog post, I talked about how to get SQL Monitor to send alerts to Slack and how to send SNMP Traps. It’s easy to make SQL Monitor send SNMP traps, but SCOM is more challenging than most management tools to configure to receive these traps.

Discover SQL Monitor

To receive SNMP traps, SCOM first needs to discover the machine SQL Monitor is running on as an SNMP device.

Make the SQL Monitor machine discoverable

You’ll need to be running the SNMP Service on the SQL Monitor machine. This can be installed from Programs and Features -> Turn Windows features on or off -> SNMP Service.

Once this is installed, navigate to the service’s properties dialog box in the computer management console. In the Security tab, add an accepted community name. We’ll use the word public throughout this walkthrough. Either add your SCOM server to the allowed hosts section, or permit packets from any host.

Also ensure that incoming traffic on port 161 is allowed to the SQL Monitor machine.

Allow SCOM to discover SNMP on Windows machines

By default, SCOM can’t discover Windows machines as SNMP devices, so it’s necessary to make a modification to its network discovery rules. On the SCOM machine, open C:\Program Files\Microsoft System Center 2012 R2\Operations Manager\Server\NetworkMonitoring\rules\discovery\ic-post-processor.asl, navigate to following section, and change all of the return TRUE statements to return FALSE:

ISWINDOWSHOST(systemObj) do {
if (systemObj->Type == "HOST" && systemObj->Vendor == "MICROSOFT") {
return TRUE ;
}
if (SEARCHSTRING(systemObj->Description, "Windows")) {
return TRUE ;
}
systemOIDCheck = ".1.3.6.1.4.1.311.1.1.3.1" ;
if (substring(systemObj->SystemObjectID, 0, sizeof(systemOIDCheck)) == systemOIDCheck)
{
return TRUE ;
}
systemOIDCheck = ".1.3.6.1.4.1.99.1.1.3.11" ;
if (substring(systemObj->SystemObjectID, 0, sizeof(systemOIDCheck)) == systemOIDCheck) {
return TRUE ;
}
return FALSE ;
}

Perform the discovery

In SCOM, select the administration tab, and run the Discovery Wizard.

Choose Network devices (not Windows, as would be intuitive).

Give the discovery rule a name and choose a management server. Either create a new Resource Pool for the SQL Monitor server to live in, or assign it to an existing one.

Choose Explicit discovery.

On the next screen, choose Create Account. Create a new Run As account, and on the Credentials screen enter the word public (or whatever other key you chose to use earlier).

Select the newly created account.

On the Devices page, add a new device. Choose the previously created Run As account, and put in the IP address of the SQL Monitor server.

Select Next on the Devices page.

Choose to run the discovery manually.

Review the settings.

After clicking Create, if prompted choose Yes to allow SCOM to distribute the Run As account to your management servers.

c

Once the rule has been created, select to run it.

After a minute or two, you should be able to browse to the Network Devices tab, and see the machine listed (SqlMonitorHost is the name of the Windows machine running SQL Monitor).

If you right click and select the properties for this device, you can confirm it looks like this, and specifically that the access mode is recorded as ICMPSNMP.

Receive SNMP Traps in SCOM

Having discovered the SQL Monitor machine, we now need to configure SCOM to receive SNMP traps from it.

Under Management Pack Objects in the Administration tab, Create a new rule.

Use the SNMP Trap (Event) type.

Either choose an existing Management Pack, or create a new one:

Give the rule a name like SQL Monitor Alert View, and choose Event Collection as the category. Under Rule target, click Select.

Search for and select the Node target (after selecting View all targets).

On the next screen, you can leave the object identifier blank to receive from any OID (or optionally filter to SQL Monitor’s id of 1.3.6.1.4.1.48099.1.1). Then click Create.

To view these alerts, you need to create an event view. Go to the monitoring tab, and create a new folder with the destination management pack set to the same one you created previously.

Right click on the created folder, and create a new Event View.

Select again to show data related to Node, and click OK to save the view.

Expand the folder and open the newly created view.

SCOM should now be ready to receive SNMP traps from the SQL Monitor machine.

Configure SQL Monitor

You need to configure SQL Monitor to send SNMP traps when alerts are raised. In SQL Monitor, browse to the Configuration tab, then select Notification settings.

Select to send an SNMP trap when alerts are raised. Fill in the ip address of your SCOM server, and set the community string to whichever value you used previously (public in this case).

Click SEND TEST NOTIFICATION to make sure messages are being sent successfully.

Checking it all works

Now in SCOM, you should see this test message from SQL Monitor appear:

You’re now receiving alerts like the following, and can consume them however you wish inside SCOM.

Troubleshooting

If you run into any difficulties, you might find the following detailed articles useful (in particular, note that SQL Monitor sends SNMP v2 packets – if your Windows machine is discovered as either a v1 or a v3 device, SCOM will fail to receive the SNMP traps. The first article describes how to work around this filtering by editing some management pack xml.

SNMP Trap monitoring with SCOM 2012 R2

How to discover a Windows Computer as a Network Device in SCOM 2012

SCOM needs to be able to receive UDP traffic on port 162, and SQL Monitor needs to be able to send traffic on that port. During setup, the SQL Monitor Server must be able to receive UDP traffic on port 161, and SCOM needs to be able to send traffic on that port.

Summary

Setting up SCOM to receive SNMP traps can be a little tricky, but getting it working with SQL Monitor can be really valuable, allowing you to take advantage of SQL Monitor’s richer SQL Server alerting capabilities inside SCOM.

If you’ve yet to get started with SQL Monitor, you can download a free 14-day evaluation. As usual, we’d love to hear any feedback – just drop an email to SQLMonitorFeedback@red-gate.com.

Tools in this post

Redgate Monitor

Real-time SQL Server and PostgreSQL performance monitoring, with alerts and diagnostics

Find out more