Awhile back, Travis posted a blog on some useful queries as a way to tour the CMDB (found here
). As he mentions, sometimes it is just faster and easier to get the data you are looking for just by querying the database itself.
Over the past months, I've found myself building my own useful queries that have helped me tremendously in learning more about how the system works and assist in troubleshooting various issues. So, I wanted to share these queries in hopes that others will also find them useful. Today's query will focus on Rule Workflows.
There is a ton of information on the Service Manager Engineering Blog that describes what workflows are, how they work, etc. (found here
). Basically for Service Manager purposes, a "workflow" is either a Rule, Task or Discovery. Of the 3, Rules are used the most. So, I wanted a query that would show me all relevant data about a Rule. Let's take a look at each column. As an FYI, some of the data in the columns is very large and is easier to read if you copy to Excel.
This is the name of the rule. This column will give the best description of what the Rule actually does.
Most all rules have two, one for the Data Source Module and one for the Write Action Module. This is why you see two rows for each Rule. The Data Source Module is super helpful since it will give you insight as to when the Rule is scheduled to run (highlighted in purple below). The Write Action module can give you insight as to what the Rule actually does, like the name of a SQL Stored Procedure.
Indicates what Management Pack the Rule can be found in.
Indicates what type of Rule it is (Maintenance, System, PerformanceCollection, etc.).
Indicates whether the Rule is enabled or not. 0 = Disabled and 4 = Enabled. But don't be too fooled by this value, there are some Rules that look to be enabled but really aren't (more on that below).
This indicates if an override exists for the Rule (more on overrides can be found here
). As you can see in the below screenshot, there are technically two rules for DiscoveryDataPurge, but one has an override.
OverridableParameterName and Value
This indicates that if an override exists for the Rule, which property is being overridden. As you can see below, one of the Rules is overriding the Enabled property to False. That means that even though the RuleEnabled property indicates it is active (4), the override is actually turning it off. The Value column indicates what the new property value is after the override.
This is where you will see (if applicable) the current watermark value for each Rule (more on watermarks can be found here
This basically indicates whether or not the Rule is a periodic query subscription.
LEFT OUTER JOIN ManagementPack M
ON R.ManagementPackId = M.ManagementPackId
LEFT OUTER JOIN ManagedType MT
ON R.TargetManagedEntityType = MT.ManagedTypeId
LEFT OUTER JOIN ModuleOverride O
ON R.RuleId = O.ParentId
LEFT OUTER JOIN Module MO
ON R.RuleId = MO.ParentId
LEFT OUTER JOIN CmdbInstanceSubscriptionState W
ON R.RuleId = W.RuleId
LEFT OUTER JOIN OverrideableParameter OP
ON O.OverrideableParameterId = OP.OverrideableParameterId
And that's all there is to it. Stay tuned for more useful queries you can use for your own Service Manager environment.
Until the Whole World Hears (and Happy Thanksgiving!!),