Report to find orphaned CIs without upstream relationships

[AlexR2]

I am trying to write several reports by cmdb class to add to a dashboard that would show orphaned CIs that don't appear in cmdb_ci_rel as a child record. I tried using related list condition but I believe that only looks for a parent

Is there a reverse of cmdb_ci_rel table where child is the primary object to query against? I also tried CMDB query builder but I don't believe that could be added to a dashboard, maybe I am wrong. Thanks!

[Tom Sienkiewicz]

I think that in order to do that, you need to create a DB View between cmdb_ci and cmdb_rel_ci.

In the left join clause, put something like this. Let's say you call the cmdb_ci table "ci" and cmdb_rel_ci table "relationship":

relationship_parent = ci_sys_id || relationship_child = ci_sys_id

Then once you get the full list (make sure to add both Child and Parent columns), in the Condition builder above, set a condition like this:

Child is empty AND Parent is empty.

This will show a list of all CIs that are neither  aparent nor child. You can add reports based on DB Views to dashboards.

[AlexR2]

I was hoping to avoid a DB view as our admins are not fond of creating them. Any other way?

[suvro]

Create Orphan Rule for the CI Class from CI Class Manager

From  CMDB  Health Dashboard make sure to enable the concerned the Scheduled Job. 

So tasks will be created for orphan CIs you can report on that. You can see that from CMDB Health Dashboard as well with health score where you can drill down to the orphan records

explore CMDB Health Dashboard

[mikkojuola]

Hi Alex,

You might be interested in the Data Content Manager, a ServiceNow certified Built on Now app available in the ServiceNow Store. With DCM, you just create a visual data model, a blueprint, including the relationships your CIs should have and an audit will tell you if your data is compliant with the model or not.

No DB views, scripting, or extra reports are required. 

If you just want to see how it works, you can book a 30-minute demo, and I'll show you.

Cheers,

--Mikko