How do I: Create An Advanced SQL Database Backup Monitor In Visual Studio?

My favorite part of my job is getting to work with customers and understand exactly how they use, and also need a product to work. Some of the time this just means listening closely, answering questions, and relaying information back to the product group. But often there are those tiny changes and use cases that fall outside the realm of things the product group is likely to address.

A change that might be hugely valuable for Customer A, won’t happen if it breaks backwards compatibility for Customers B-Z.

One of my customers has a SCOM environment for SQL that monitors over 20,000 databases. Due to their size there are often cases where the out-of-box monitoring provided by the SQL Product Groups management packs isn’t able to completely meet their needs. While the pack provides fantastic monitoring for SQL, there are times where they need a more granular view of the health of their environment.

An instance of this from the past year was monitoring SQL Database backups. The SQL Product Group’s pack gives you the ability to alert if a database hasn’t been backed up in a certain number of days. By default it is set to 7 days, but you can override that to any integer value of days.

For my customer this wasn’t really good enough. They wanted to be able to drill down and alert on hours since last backup. They also wanted multiple severities so if it had been 20 hours since a backup, an e-mail could go out, but at 30 hours we would generate a page. The 20 & 30 hours would be customizable at the individual database level, and they also wanted some added logic that would check database backups for databases that had a status of “ONLINE”. We have other monitors that look at DB Status in general so in this case if a database was OFFLINE they either knew about it from the other monitors and were fixing it, or it was intentional in which case they didn’t want a backup alert.

The basic logic behind the SQL PG’s MP is a simple T-SQL query wrapped in a fairly complex vbscript. The unwrapped T-SQL is below:

The T-SQL modifications we need to make are relatively simple swap DAY to HOUR, and add in a line to only return database backup info for databases with a status of ONLINE.

To get this into a working Management Pack is a little bit more complex and requires isolating and cloning the Product Groups Database Backup Monitor in Visual Studio, and then making a few changes to the XML for our custom iteration.  To prevent screenshot overload I did a quick step-by-step walkthrough of the process. For this video I opted to leave out three-state severity request, and will show how to add that functionality in a follow up video.

If you have any questions or need any help, just leave a comment.

How do I: Change the default behavior of the DB Mirror Status Monitor

MP Authoring Series (For an explanation of this series read this post first.)

Real World Issue: Customer is seeing a lot of Critical alerts for Mirrored Databases in a Disconnected state, but only Warning alerts for Mirrored Databases in Suspended state. In this customer’s environment brief disconnects are common and not necessarily indicative of a Critical issue, whereas a mirrored database in a Suspended state is always Critical for this customer. The customer wants to swap the default states such that Disconnected will now be Warning and Suspended will be Critical.

There are three Mirrored Database Mirror Status Monitors

01 02

When we dig into the properties


We see the three states as well as the corresponding Statuses that map to those states.


When we check Overrides we find there is nothing we can override to meet the customer needs:


So to accommodate this request we need to do a little custom authoring in Visual Studio + VSAE.

The process isn’t too complex, however, it is much easier to absorb via video than the many page article that would result if I tried to document the steps by hand:

Once you have your final successful build you will find your files in the bin–Debug folder of your project:


Once you import your custom MP into SCOM you will have a cloned monitor with your modified behavior:



How do I: Create a Wildcard SCOM Service Monitor and Recovery

I recently had a question from a customer on how to create  Wildcard Service Monitors + Recoveries. The Service Monitor from the Monitoring template and a simple Unit Monitor for services both require an explicit service name, no wildcards allowed. For most services this is fine, but there are some applications which do fun and interesting things like concatenate computername + service to create a unique service name. This creates a bit of a problem for monitoring. You can create individual monitors, but if you have hundreds of services each with unique service names that follow a particular pattern creating hundreds of corresponding monitors could get a little time consuming.

Brian Wren has a great article from back in the SCOM 2007 R2 days that answers part of this question, but when I went through the steps I found it needed some slight tweaking and updating for SCOM 2012 R2. Once that was complete I also needed to come up with a simple low overhead wildcard service recovery for when one of the services stops and needs to be brought back online.

Below are my steps:

Launch SCOM Console



Management Packs


Tasks – Create a Management Pack


Enter Name + Description – Next – Create


Select Authoring


Right Click Windows Service


Add Monitoring Wizard


Windows Service


Enter a Name, Save to the MP you just created


Enter Service Name. Use % for a wildcard representing multiple characters. As I don’t have any unique services in my environment I am using m% to demonstrate how this can work. For the rest of these instructions wherever you see m% keep in mind that you need to modify this value to match your unique service name wildcard value. Be careful using too broad a wildcard could create a lot of noise and load very quickly in your environment.

Pick a Target Group. In this case I am using All Windows Computers. Generally you would want to target this as precisely as possible. Leave Monitor only automatic service checked


Click Next


Click Create


Select Administration


Select Management Packs


Select the Custom Management Pack you just created


Select Export Management Pack


Select a location to save the unsealed xml file


Click OK


Open the File in your XML editor of choice (Notepad will do, but Visual Studio or Notepad+++ will make it a bit easier to read)


Search the file for your wildcard in my case this is M%


We’ll be making a few replacements in the code.


You will be modifying:

<DataSource ID=”DS” TypeID=”MicrosoftWindowsLibrary7585010!Microsoft.Windows.Win32ServiceInformationProviderWithClassSnapshotDataMapper”>




To: (remember to also swap the m% with the appropriate value)


<DataSource ID=”DS” TypeID=” MicrosoftWindowsLibrary7585010!Microsoft.Windows.WmiProviderWithClassSnapshotDataMapper”>


<Query>select * from win32_service where name like ‘m%'</Query>


  • In Brian Wren’s instructions he used TypeID=”Windows!Microsoft,Windows.Win32…” The Alias in my custom console generated MP is MicrosoftWindowsLibrary7585010! If you run into any errors keep in mind that whatever alias is present in the manifest references must be consistent. I haven’t tested to confirm, but based on the output it looks like the console MP generated alias is based on MP+Version Number. If you have a different version of the MP and you follow my steps exactly you will likely hit an error as the Alias I provide for Microsoft.Windows.Library is going to be off by a few numbers from yours. If this is the case just modify the alias in my example to match what you have in the rest of the .xml file.











Save the .xml file

Go back to the SCOM console – ADministration


Import Management Packs


Add from disk


Select the newly modified .xml file






To check and confirm that the discovery associated with the wildcard monitor is working.

Select Monitoring


Discovered Inventory


Change Target Type


Select Custom Target


A few minutes after importing the updated pack you should see services discovered.


Now we need to create a wildcard recovery. If this was a single service recovery I would create a standard SCOM recovery and call net.exe and pass a start command with the service name. Since this is a wildcard service we have to do things a little differently as I don’t know of a way to pass wildcards to net.exe. (We could use PowerShell, but for this I want to try to be as light weight as possible from an overhead perspective even if that means sacrificing some more advanced error handling that we could easily add in with PowerShell.)

Go to Authoring:


Select Windows Service


Right Click your custom Service Monitor – View Management Pack Objects – Monitors


Expand Entity Health -Availability – Right Click the Basic Service Monitor Stored in your custom MP – Properties


Select the Diagnostics and Recovery Tab


Under Configure recovery tasks select Add – Recovery for critical health state


Select Run Command


Name your Recovery – Check the Boxes for run recovery automatically and recalculate monitor state after recovery finishes


Enter Full path to file


Parameters: (originally I used slightly different param, but found that while it worked in the command line it failed when run as recovery. This method works consistently)

/interactive :off service where “name like ‘m%'” call startservice


Click Create

You should now be all set to test out and validate your new monitor.

EndNote/Cautionary Tangent:

Just keep in mind that a wildcard discovery if targeted incorrectly (too broad a wildcard, too broad a target group, or both) you could have the recipe for a single monitor that can cause a lot of churn/perf issues/and noise in your environment. So be cautious and test very carefully. Make sure you have a good sense of the number of objects this monitor will pick up not just in your test environment, but once you move it into production.  To be clear I would never recommend using a wildcard as broad as m% in production. This picks up way too many services that you likely don’t care about.

Also please note that the recovery is equally general as the monitor if not more so. It is also not checking to see if the services that apply to it are already started. In the case of my example m% picks up a bunch of services. If a single service matching that criteria goes down, the recovery will attempt to recover/start every single service that matches that criteria m%. So if you are building your wildcard service monitor to pickup multiple services on single system that follow a common pattern, a failure of one will result in an attempt to recover all.

In theory this shouldn’t be a problem. The method I am using is extremely lightweight and if the service is already started in the background the service will just output an exit code of “I’m already started” and remain started. With that said this is only a sample, and its still worth testing in your environment to confirm the behavior and make sure you understand exactly how the recovery is working before you consider implementing.

An example of running the recovery for an instance of m% being stopped:



Troubleshooting: SCOM Web Console 500 – Internal Server Error

This is a problem that I occasionally see crop up in customer environments, but until now I had never bothered to document the issue.


Customer is able to log into the web console successfully but when they click on certain views like the active alerts view they see the following error:


Anytime there is an issue with the web console my first suggestion is to attempt to recreate the problem as localhost on the machine that hosts the webconsole. If the server has the same problem locally you know where to start troubleshooting, if it is only happening on client machines it could be firewall or some problem at the client level.

When we tested from SCOM web console server we get the same error, but this time with a little bit more information:


This may not seem useful, but this is actually telling us exactly where the problem is. Note the Version= in regards to .Net Framework. This is telling what version of the Framework is expected for this particular app pool.

If we navigate to IIS we see the following:


OperationsManagerMonitoringView is set to v.40, but it should be set to v.2.0. We need to edit the Basic Setting and select the drop down with v2.0


After that we need to recycle the Application Pool via the Application Pool task


Then just logout of the webconsole and back in and all will be well.

System Center Operations Manager Blogs you should read:

Most of my neck of the woods is shut down and in snowday mode  so it seems like a good day to catch up on some of the OpsMgr blogs.

In no particular order:

01. TechNet – Kevin Holman’s Blog

02. Thoughts on OpsMgr and System Center 2012 – Marnix Wolf’s Blog

03. SCOM, SCCM, Powershell – Tao Yang’s Blog

04. Kevin Greene’s Blog

05. Cameron Fuller’s Blog

06. TechNet – Operations Manager Engineering Blog

07. Bob Cornelissen’s Blog

08. Everything System Center – Tim McFadden’s Blog

09. Matthew Long’s Blog

These are a few of my favorites, if you have any suggestions leave a comment and I will add them to the list.


Rod Trent kindly pointed out that I had left out myITforum which while not dedicated to SCOM is certainly an excellent resource for all things Microsoft & System Center.


The contents of this site are provided “AS IS” with no warranties, or rights conferred. Example code could harm your environment, and is not intended for production use. Content represents point in time snapshots of information and may no longer be accurate. (I work @ MSFT. Thoughts and opinions are my own.)