My customer has many custom monitoring workflows that target the MSSQL on Windows: DB Engine (Microsoft.SQLServer.Windows.DBEngine) class. For various reasons some of those engines are disabled and they wanted an easy way to override these workflows only for the disabled engines. My goal here is to dynamically populate a group with only disabled SQL engines. This isn’t quite as easy as you might expect but it’s certainly possible.
There might be other ways to solve this but here’s how I did it:
I’ve created a generic hosted class to represent the disabled SQL service for the engine. This generic class is hosted by the Microsoft.SQLServer.Windows.DBEngine.
I also created a discovery that targets every Microsoft.SQLServer.Windows.DBEngine instance which leverages a very simple PowerShell script to identify if the SQL service is disabled for the instance. If so, this generic class instance becomes discovered. Its only function is to represent the ‘Disabled’ status of the SQL engine. If the DisabledEngine object exists for the SQL engine, the service is disabled.
I then use a specialized dynamic group formula to populate a custom group with a Windows.DBEngine only if the SQL engine contains an instance of the generic class (DisabledEngine).
I use VS for authoring. Here’s a pretty picture of the dynamic group recipe:
Here’s where the real magic happens. In the code below I’ve added a “Contains” element. This is something that the Console group wizard can’t do for you. You have to write this manually as shown below.
If we look at the diagram view for the hosting DBEngine, we can visualize things better:
We can see our group populating correctly below:
I can use this group to override the appropriate workflows, in this case my SQL Simple Query Test monitor.
Note: Some might ask, “don’t the SQL MPs already have a basic connection status monitor”? The answer is yes. However, my customer was experiencing a scenario where their encryption keys weren’t getting updated and the connection attempt would simply hang and the basic connection monitor would not detect the problem. I created a simple connection test monitor that could detect a hang.
Override example:
Finally, we can see that the workflow will automatically stop for any disabled Windows DBEngines.
