How can I get a report on CI (specific types) that don't have a particular relationship with another CI type?

[stuboasman]

This will eventually be part of our CMDB Health metrics, but I am starting with a report.  We have Servers that are populated by Discovery and have a manually maintained (imported) set of Applications.  When it is fully populated we shouldn't have any servers that aren't "doing" anything as defined by it having a "Run::Runs On" relationship with the Applications (class=Application).

I can't find how to view the relationships on a report, any advice?

[robertmaxwell]

Ok, for example, to find where the parent CI class is Computer, the child CI   class is Application and the   relationship type is Runs::Runs on you'll do a bit of dot walking in the report conditions.

In the report filter set:

1. Parent.Class is Computer (you may need to expose this by scrolling to the bottom and selecting Show Related Fields and the using Parent>Configuration item fields)

2. Child.Class is Application

3. Type is Runs::Runs on

Play around wirh the filter conditions to achieve your required result.

[Teena Singh]

Stu,

Glad to see that you are using the Community to learn more.

The Customer Experience team is striving to ensure that customer queries posted from the HI Service Portal are answered in timely and accurate fashion.

If you feel your question has been resolved, please mark the appropriate reply in the thread as being the Correct Answer.

This enables other customers to learn from your thread.

Thank you in advance.

If you are viewing this from the community inbox you will not see the correct answer button.   If so, please review How to Mark Answers Correct From Inbox View.

Regards,

Teena Singh
Customer Experience: UX Strategy and Customer Insights
teena.singh@servicenow.com

ServiceNow

[stuboasman]

Still not quite what I am looking for, if this were a SQL query I would say

SELECT [] from cmdb_ci_appl as a

WHERE a.class = "Application"

and NOT IN (SELECT

FROM cmdb_rel_ci as r

WHERE r.type = "Depends on::Used by"

and ((parent.class = "Business Service" and child.name = a.name)

or     (child.class = "Business Service" and parent.name = a.name)));

[angeliccharm · Accepted solution]

Hey Stu,

Sorry you haven't been able to achieve what you want yet. There's the option to do this based on something from the query builder, but it's pretty limited. Ultimately, a script will be needed so it will have to retrieve the CIs and all related CIs and parse through it for the results you want.

If this is something you need assistance with, please let me know.

[stuboasman]

OK, I tried accomplishing what I was looking for using a Database View, but couldn't get that to work either.   How would I use scripting to allow a report to use this dataset?