SCOM Monitoring Using a Scripted Database Query

I recently had a request to write some code that would enable System Center Operations Manager 2007 R2 (SCOM) to perform a database query against a SQL database and then evaluate the result to determine if various systems were healthy. The first few queries were against a Microsoft SQL Server 2005 database. The remaining couple of queries were against an IBM DB2 database.

Usually, this type of request will involve writing a VB script.

After performing a search on the Internet for similar solutions (always a good idea to see if somebody else has solved the same type of problem), I came up with a couple of useful sites.

When looking at the sample code provided by these links, I noticed that the provider in all samples was “Microsoft OLE DB Provider for SQL Server (SQLOLEDB)”. This would have worked for me, but taking things one step further, I also found out that this provider has been deprecated.

Instead, I chose to use the “SQL Server Native Client OLEDB Provider (SQLNCLI10)”. Using this provider is very similar to using SQLOLEDB.

I could also have chosen to use the “Microsoft OLE DB Provider for ODBC Drivers (MSDASQL)”. During testing, the only thing I didn’t *like* about this provider is that it connects to ODBC, instead of a database. Compared to SQLOLEDB/SQLNCLI10, this means that we need to define a DSN instead of just using a connection string.

How do I enumerate the OLEDB providers on my server?

If you have Microsoft SQL Server installed then this can be done by executing the following stored procedure against the MASTER database:

EXEC master.sys.xp_enum_oledb_providers

The output is similar to the following:

List of OLEDB providers via a stored procedure call

Connection String

“Provider=IBMDADB2;Database=MyApplication;Hostname=192.168.0.1;Port=50000;Uid=<username>;Pwd=<password>

Passing Credentials to the Monitor

We have a class called “MyCompany.MyApplication.Watcher” that represents the set of computers from which we will run a script to monitor the MyApplication application.

We have a “Timed Script Two State Monitor” called “MyCompany.MyApplication.Status.Monitor” that is targeted at the “MyCompany.MyApplication.Watcher” class. This monitor contains the script that performs a database lookup against an IBM DB2 database to determine the health of the MyApplication application.

SCOM provides a secure method of passing a credential to a script using a Run-As Profile. For the purpose of connecting to a DB2 database this is not the same as running the script under different credentials. Hence care should be taken not to change the “Run as profile” setting within the monitor. Instead, we will pass the credentials to the script using script parameters.

(to be continued)

List of external links

(Note: this is a quick publish article and is not yet complete)

Advertisements
This entry was posted in SCOM, SQL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s