Monitor SQL Queries Management Pack, Then and Now

Two years ago one of my project managers came to me with a request for monitoring the project he is responsible for. The project services different complex applications. This request was unique because he wanted to make sure that none of the applications that use the service have a problem. So, I had one of two options: 1) to develop a management pack for each application. 2) to monitor the repository with an SQL query. You guessed right I have chosen the second option. At first, I created dedicated a Management Pack and after a while, we found this idea of monitoring an application by using an SQL query very useful.

I decided to create a template so it could be easier for my teammate and me to create such monitors.

This is how my new management pack was born.

This Management Pack doesn’t add monitors directly to SCOM, rather, it adds a new template to the Authoring tab of SCOM console so you can easily take any SQL query (Note: return value has to be a single numeric value) and use it as the basis for SCOM monitoring.

Now, you can monitor the behavior of your application such as the number of active users on your website or the number of minutes without any interaction on your application. You can configure it so that it would alert when the number dips are below or above a certain threshold. Moreover, the query result is collected as performance so you can use it for graphing and analyzing.

This Management Pack includes files; QueryOleDbMonitorLibrary.mp – it includes all the definitions and QueryOleDbMonitorTemplate.mpb – it includes the template definition. Import these two files to your SCOM environment.

The next chapter will be much more technical. I will explain how I develop template wizard in the Visual Studio IDE.