How can an CMDB Health Orphan Rule be used when the CI is part of a Dynamic Group?

[Bob Mullineaux]

We generally use an CMDB Health Orphan Rule which determines if a CI has a missing relationship to a Service (Application or Technical).   It works as expected and flagged in CMDB Correctness when there is not a relationship in cmdb_rel_ci between the CI and any Service, but we have recently configured Dynamic CI Groups to align CIs to Services and thus no explicit relationships created in cmdb_rel_ci...rather the CI to Service relations are stored in svc_ci_assoc, thus the CIs that are part of the Dynamic Group are still being flagged as Orphaned even though they do have a Service Relationship.

Does anyone know of a way to develop an Orphan rule around svc_ci_assoc?  

I was thinking of scripting something to automatically create entries in cmdb_rel_ci based upon the entries in the svc_ci_assoc table, but it sort of defeats the purpose of having a Dynamic Group.  

[CMDB Whisperer]

Yes.  Pretty much the standard answer to anything along these lines is to create a custom field that calculates the number or boolean that would help you to determine whether it is an orphan, and then create the orphan rule based on that attribute.  For example, if you want to consider something an orphan if its number of records in svc_ci_assoc is zero, then just run a scheduled job that runs on a recurring basis and stores the number of service associations resulting from that query in a custom field called "Number of associated services", and then configure the orphan rule to include Number of associated services = 0.

Please mark as helpful/correct if this helped you.

[Bob Mullineaux]

Hi Whisp,

I am not quite following your suggestion as it pertains to how to create an orphan rule based upon the custom field?  The desirable outcome would be to have the Orphan rule processing, look at svc_ci_assoc in addition to cmdb_rel_ci in order to determine if the is a relationship in either of those two files.   This seems to be a bit of a miss on ServiceNow's part.  

[CMDB Whisperer]

@Bob Mullineaux since an Orphan rule can be created with conditions that refer to an attribute, you can define a custom attribute that provides the data needed, to do this, here are the basic steps:

1. Define a custom integer field on the cmdb_ci table called u_associated_service_count with a default value of 0.
2. Write a script include that calculates the number of associated services for a CI by looking at the svc_ci_assoc table. 
3. Determine how and when you want to run this script (scheduled job, business rule, etc.)
4. Once the script has been run on your CIs they will have a value in the custom field indicating the number of associated services (which will include dynamic CI groups)
5. In your orphan rule, include the condition that associated services = 0.
6. You shouldn't need to look at the CI Relationship table, because that would give you redundant information, as CI relationships are already taken into account in calculating the svc_ci_assoc table.

While it's not the cleanest solutions, this is the basic approach that will get you what you are looking for, i.e. to identify CIs that are not part of an application service or dynamic CI group.  I would suggest further filtering this orphan rule to just include certain subclasses of CI, and possibly just Production CIs.

[Bob Mullineaux]

Thanks Whisp...like you said "not the cleanest solution".   I believe it would be better to use the svc_ci_assoc table as the basis for automating the creation (or deletion) of CI relationships.  That way, the Orphan Rule will work out of box.  Clearly a miss on ServiceNow's part since the Service to CI association represents a relationship between the CI and the Dynamic Group thus the OoB Orphan calculations should be including svc_ci_assoc to make the determination if a particular CI of a particular CI Class is orphaned.

I have submitted a HI ticket, but suspect the outcome will be a feature request or a recommendation to use data certification.