21 November 2016
21 November 2016

Using SQL Monitor with SCOM

This walkthrough shows how to configure SCOM so that it can consume alerts from SQL Monitor.

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.

SQL Monitor SCOM 1

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.

SQL Monitor SCOM

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).

sql-monitor-scom-3

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.

sql-monitor-scom-4

Choose Explicit discovery.

sql-monitor-scom-5

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).

sql-monitor-scom-6

Select the newly created account.

sql-monitor-scom-7

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.

sql-monitor-scom-8

Select Next on the Devices page.

sql-monitor-scom-9

Choose to run the discovery manually.

sql-monitor-scom-10

Review the settings.

sql-monitor-scom-11

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

sql-monitor-scom-12c

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

sql-monitor-scom-13

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).

sql-monitor-scom-14

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.

sql-monitor-scom-15

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.

sql-monitor-scom-16

Use the SNMP Trap (Event) type.

sql-monitor-scom-17

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

sql-monitor-scom-18

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

sql-monitor-scom-19

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

sql-monitor-scom-20

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.

sql-monitor-scom-21

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.

sql-monitor-scom-22

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

sql-monitor-scom-23

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

sql-monitor-scom-24

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).

sql-monitor-scom-25

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

sql-monitor-scom-26

Checking it all works

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

SQL Monitor SCOM

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

SQL Monitor 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

SQL Monitor

SQL Monitor is a SQL server monitoring tool that transforms the way you look at your database. It cuts your daily check to minutes, with a web-based overview of all your SQL Servers.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Monitoring Changes in Permissions, Users, Roles and Logins

    Phil Factor uses the default trace and a SQL Monitor custom metric to alert you to unauthorized changes in security membership or permissions in any of your monitored databases.

  • Community event

    SQLSaturday Cape Town

    SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event. Redgate are excited to be one of the

  • Article

    SQL Server monitoring from the trenches: An interview with Anthony Nocentino

    I recently sat down with Enterprise Architect, Anthony Nocentino, to talk about how effective monitoring can help resolve many of the issues companies face every day. I was fascinated because my career revolves around how we can improve SQL Server monitoring. Anthony’s involves actually doing it at the coalface. He’s made a name for himself

  • Article

    3 ways Managed Services Providers can offer more value

    For many businesses, using a Managed Services Provider (MSP) makes sense, particularly when it comes to database management, monitoring and security. They can control costs while still having access to expert resources, and dial up or down the service as required. It’s perhaps no surprise then that 55% of people surveyed in the Channel Futures

  • Forums

    SQL Monitor Forum

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