identify records that are in one table, but not another

[funkjunkie]

Looking for a way to put a script together that will identify all records that are NOT in 2 tables.

For example, all CI that are in the cmdb_ci table, but not in the task_ci table, maybe filtering by class.

not a count.. but a list

The following seems to print out all those fit my example above, listing all the classes of CI in both, with a count at the end.

var ci = new GlideRecord('cmdb_ci');
var count = 0;
ci.addJoinQuery('task_ci');
ci.addEncodedQuery('sys_class_name=<class>');
ci.query();
while (ci.next()) {   
count++
gs.print(ci.sys_class_name);
}
gs.print(count);

This one seems to find all the ci records that are in both cmdb_ci and task_ci and in a particular class

var ci = new GlideRecord('cmdb_ci');
var count = 0;
ci.addJoinQuery('task_ci');
ci.addEncodedQuery('sys_class_name=u_as400');
ci.query();
while (ci.next()) {   
count++
gs.print(ci.name);
}
gs.print(count);

My question:

how might I reverse this to show those that do not exist in both rather than those that do?

[sergiu_panaite]

Hi George,

First of all, using a count variable for counting is not indicated for performance reasons. You can use either GlideRecord.getRowCount() or better GlideAggregated. See more information here:

Performance considerations when using GlideRecord

Now, coming back to your request, here are 2 scripts that fulfill your requirements:

1. script to print all records from task_ci that are not in cmdb_ci (task_ci has a field ci_item that is a reference field to cmdb_ci, which makes it easier for us to identify the records we want):

//print all records from task_ci that are not in cmdb_ci
var gr = new GlideRecord('task_ci');
gr.addNullQuery('ci_item');
gr.query();

while(gr.next()){
   gs.print(gr.task.getDisplayValue());
}

2. script to print all records from cmdb_ci that are not in task_ci:

//print all records from cmdb_ci that are not in task_ci
var grn = new GlideRecord('cmdb_ci');
//here I could do directly grn.query() but if table is large then that's not very good, so adding a condition on a field not empty is a better way
grn.addNotNullQuery('name');
grn.query();

while(grn.next()){

   var grc = new GlideRecord('task_ci');
   grc.addQuery('ci_item', grn.sys_id);
   grc.query();

   if (!grc.hasNext()){
       gs.print(grn.name);
   }
}

Is this what you wanted?

Regards,

Sergiu