21 November 2016
21 November 2016

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. This walkthrough shows how to configure SCOM 2012R2 to consume alerts from SQL Monitor.

Discover SQL Monitor

Make the SQL Monitor machine discoverable

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

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

    How SQL Monitor Monitors Azure-based SQL Server Failover Cluster Instances

    If you’re working with SQL Server, one of the most appealing features of cloud-based computing services, such as Azure and AWS, is that they make it so much easier to design systems that are both highly-available and offer disaster recovery. No longer do you have to worry about the myriad issues arising from setting up

  • Webinar

    Data privacy & protection: A logical extension to DevOps

    Are you considering data privacy and protection as part of your DevOps process? In light of legislation like GDPR, making sure that any personally identifiable information (PII) is protected as it moves through your development and testing environments, is now an essential part of the process to ensure that your Database DevOps practices are compliant.

  • Article

    Monitoring Distributed SQL Servers using SQL Monitor

    Many organizations are experiencing rapid expansion and diversification of their SQL Server estate to include Cloud, VMWare and other platforms, alongside traditional on-premise servers. This article explains the basic architecture and components of SQL Monitor, and then how to set up a distributed monitoring solution. It will allow a single administrative team to manage and

  • Webinar

    SQL Monitor Overview

    SQL Server Monitoring Specialist James King takes you from getting a simple overview of your entire SQL Server estate to alerting and diagnosis of SQL Server problems.

  • Article

    SQL Monitor plans for the second half of 2017

    SQL Monitor’s dev team has made huge improvements to the product over the last year. In the first half of 2017 alone, they released reporting capabilities, support for collecting metrics from VMWare hosts, significant improvements to performance and scalability, improved configurability of alerts, as well as dozens of smaller enhancements. Since we’re about half way

  • University

    Take the SQL Monitor course

    This course takes you from installation and configuration, all the way up to getting the most out of the advanced features in SQL Monitor to help you proactively monitor your SQL Server estate.

    Learn how to explore in-depth issues in your environment, run an analysis, manage alerts, create custom metrics, and more.