Query against Configuration Items and Relationship Table

[mdidrikson]

Hello,

We are trying to get a list of all of our Configuration Items (CI) that do not contain a relationship to another Configuration Item.

I was building a query to go against the cmdb_ci and cmdb_ci_rel tables to get this information, but it appears to be taking a long time to complete.

Is there a more straight forward way to retrieve this information?

Here is my query:

var gr = new GlideRecord('cmdb_ci');
gr.query();
while (gr.next()) {
  gs.print('Sys Id ' + gr.sys_id);
  gs.print('Name: ' + gr.name);


var rec = new GlideRecord('cmdb_rel_ci');
rec.query();

rec.addQuery('parent', '!=', gr.sys_id);
rec.addQuery('child', '!=', gr.sys_id);
//rec.addOrCondition('parent', gr.sys_id);
//rec.addOrCondition('child', gr.sys_id);

while (rec.next()) {
  gs.print('Record ' + gr.name);

}
  gs.print(rec.getRowCount());

}

[michaelhade]

I think the problem is that your 2nd query is doing a query without any conditions being added to it. Try moving your query statement below the 2 addQuery statements you have. Also, I don't know if this makes a difference, but if all you care about is a count, then you could try GlideAggregate. I tried the below query in my system, but it took a long time as well due to the fact we have over 2 million records in our cmdb_ci table. In fact, it didn't even finish or I wasn't patient enough to wait. It's telling the system to print out over 2 million counts, which will take a long time regardless of what query you use.

var gr = new GlideRecord('cmdb_ci');
gr.query();
while (gr.next()) {
//gs.print('Sys Id ' + gr.sys_id);
//gs.print('Name: ' + gr.name);

var rec = new GlideAggregate('cmdb_rel_ci');
rec.addQuery('parent', '!=', gr.sys_id);
rec.addQuery('child', '!=', gr.sys_id);
rec.addAggregate('COUNT');
rec.query();
if (rec.next()) {
gs.print('Name = ' + gr.name + ' Count = ' + rec.getAggregate('COUNT'));
}
}

If this information is needed on a regular basis, then maybe the better solution is to add a flag on the cmdb_ci table to indicate whether the cmdb_ci record is related to another cmdb_ci record. You could then create an After business rule on the cmdb_rel_ci table to update this flag when records are inserted, updated, or deleted from the cmdb_rel_ci table. This would make reporting a lot easier on unrelated cmdb_ci records.

Thanks

[mdidrikson]

Thanks for the response Mike! I am working with your query now and will let you know what I am able to determine.

Thanks again.

Mark Didrikson