Use Operations Manager to Create a Custom Monitor for SQL Agent Jobs

The more I play with Systems Center Operations Manager 2007, the more I become comfortable tinkering around and configuring it to monitor my SQL Servers. Out of the box, Operations Manager gives a decent amount of functionality, but if you are willing to dig a little deeper you will find ways to make your job as a DBA a little bit easier.

Operations Manager is an enterprise level monitoring, reporting, and alerting system. It is part of the Microsoft Operations Framework, an implementation of the IT Infrastructure Library (ITIL). Operations Manager 2007 relies on the use of Management Packs for the actual work of monitoring systems. There are two SQL management packs, one for SQL 2000 and another separate pack for SQL 2005. Out of the box, Operations Manager gives enough basic information to help any DBA effectively monitor their server environment.

The one common item that all DBAs share is the diversity of the shops they administer. In some shops the DBA is more of a developer, in others it is more of a server engineer role, in others it is BI, or architecture. As such, it is difficult for anyone to build a tool that can satisfy the needs of everyone. But with Operations Manager, each shop is given the ability to take the tool and shape it to work specifically for their needs. Think of it as an open source monitoring tool, if you will, in the sense that you can make changes to how the system will collect, monitor, report, and alert you to potential issues.

One item that tends to pop up in our shop is the sudden appearance of jobs within SQL Agent. When these mysterious jobs appear, and subsequently fail, it forces us to spend time investigating what has happened. Of course the job fails at a time that is most inconvenient, which only adds to our frustration. In order to avoid this altogether, I started to think of how I could use Operations Manager to quickly alert our team to any jobs that had been recently created or modified.

In this article, I will describe how to create a customized monitor within Operations Manager 2007. The purpose of this custom monitor is to look for jobs within SQL Agent that have either been created or modified within the past day. Before you create this customized monitor, you must enable the SQL Agent Job discovery in Operations Manager. You can find out how to enable the discovery by going here, then downloading the SQL 2000/2005 Management Pack, and running the .msi package. The package unzips a handful of files to your local C: drive (C:\Program Files\System Center Management Packs\SQL Server Management Pack ), and in there you should be able to find the OM2007_MP_SQLSrvr.doc document. Once you have enabled the discovery, the rest of this article will function as expected.

Choosing the Right Monitor
It is easy to initiate a monitor in Operations Manager, but it can be harder to implement it. The basic idea is simple; just do a right-click and select 'Create Unit Monitor', but there is more work to be done than just a few clicks. To make things more frustrating, the help files are not very helpful, and I found myself scouring the internet looking for pieces of information that would help me put everything together.

So, I want to create a custom monitor that will raise an alert should a job be created (or modified) inside of SQL Agent within the past day. I am aware that there are two columns in the msdb..sysjobs table, named date_created and date_modified, so I plan on using these columns in my monitor. The next question is: how do I actually go about using those columns in the monitor?

I decide to open up and examine the default SQL 2005 discovery Management Pack to see how Operations Manager is actually discovering the jobs. What I find is that Operations manager executes a stored procedure (sp_help_job) to return a result set that is then used to populate a custom class. Inspired by this, I decide that what I need to do is create my own VB script that runs a SELECT statement against the sysjobs table, places that information into a property bag, and then use it in order to view the state of the SQL Agent job inside the Operations Manager console. Sounds simple, right?

Well, what monitor do I create? From the Authoring tab in Operations Manager, I can right-click on 'Monitors', and select 'Unit Monitor'

So, we have eight choices for types of monitor, each with sub-choices. How are we to know which one to select? If you were to navigate through all the possibilities (as I did), it should become clear that what you really want to use is a Scripting type of monitor. The reason for this is that we are going to be querying a table in the msdb database, and the other monitor types are monitoring specific objects such as log files, services, performance counters, etc. With a scripting monitor you have more flexibility, which is just what we need for this situation.

This is exactly what we want, a monitor that executes a script on a fixed schedule. This particular monitor allows for us to define a healthy, degraded, and unhealthy state, thus a “Three State” monitor. Later on you will see how we will define each of these states. Of course, we do need to put the script together, so perhaps we should look at that first before going any further with the creation of the monitor. Creating the Monitoring Script

The script itself is very basic, with the idea being that we want to collect a few pieces of information and store them in a property bag. I have included the script with this article, should you want to get this monitor working in your shop as well. The section of code that is doing the actual work is found inside the GetJobCrMoDt function, and the actual SELECT statement being used in the script as follows:

SCRIPT_SQL = "SET NOCOUNT ON" & VbCrLf &_
"SELECT" & VbCrLf &_
"[name]" & VbCrLf &_
", [days_since_creation] = datediff(dd, date_created, getdate()) " & VbCrLf &_
", [days_since_modification] = datediff(dd, date_modified, getdate())" & VbCrLf &_
" " & VbCrLf &_
"FROM [msdb]..[sysjobs]"


That is it, nothing more: just tell me the job name, and the date difference from today with regards to when it was created as well as modified. We are interested in a datediff result of one (1) or zero (0), which would indicate that the job has been created, or modified, within the past day. The function then populates and returns the property bag. If you prefer to be alerted if the job has been created or modified in the past hour, or the past week then simply modify the logic appropriately.

Creating the Job Monitor
Once you have your script working, go back to the creation of the Unit Monitor (Figure 3). If you have not yet customized your Operations Management environment to have a custom Management Pack, then now would be a good time to do so. For simplicity, I will use the Default Management Pack, but this is not a recommended best practice. If you rely on the default management pack for modifications, your modifications could be erased during an upgrade. By creating your own management pack you avoid this possible issue. Once you have decided which Management Pack to use (default or custom), click 'Next'. You should see the following screen

Full article...