More Service Manager Useful Queries – Rule Workflows

Author by Christopher Mank

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. 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, here and 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. RuleName This is the name of the rule.  This column will give the best description of what the Rule actually does. ModuleConfiguration 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. MPFriendlyName Indicates what Management Pack the Rule can be found in. Rule Query Results 1 Image RuleCategory Indicates what type of Rule it is (Maintenance, System, PerformanceCollection, etc.). RuleEnabled 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). OverrideName 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. State This is where you will see (if applicable) the current watermark value for each Rule (more on watermarks can be found here). IsPeriodicQueryEvent This basically indicates whether or not the Rule is a periodic query subscription. Rule Query Results 2 Image Copy/Paste Mode SELECT R.RuleName, MO.ModuleConfiguration, M.MPFriendlyName, MT.TypeName, R.RuleCategory, R.RuleEnabled, O.OverrideName, OP.OverrideableParameterName, O.Value, W.State, W.IsPeriodicQueryEvent FROM Rules R 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 ORDER BY R.RuleName ASC 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!!), Christopher

Christopher Mank

Systems Architect