Need to remove duplicate CI relationships CMDB Relationship Table through Background script

Riya25 · ‎04-30-2020

Hello Experts,

At present in our instance there are thousands of duplicate relationships are present in CI relationship Table.

I want to clean this table.For this i am running the Background script in my sandbox to validate if it works fine or note.

I have given the link of the Background script which i am using below.

My issue is that when i am filtering the records using the query:

"Parent is not empty" and Parent.sysid is not empty or child is not empty and child.sysid is not empty" and then grouping by Parent then also i am getting the Empty Parent and Child records ,sometime with diffrenent Sysid of Parent..Also when i am running the query Parent is null or empty i am not getting any records in the table.I don't know why?

For the relationships in which either Parent is empty or child is empty or both parent and child is empty.what should we do for those records.Should we delete them?

And can anyone validate my background script which i am using is right or not?

https://hi.service-now.com/kb_view.do?sysparm_article=KB0780988//After running the last script i am getting duplicate records=0

https://community.servicenow.com/community?id=community_question&sys_id=b0314be5db98dbc01dcaf3231f96...

https://community.servicenow.com/community?id=community_blog&sys_id=74aca225dbd0dbc01dcaf3231f961978...

Regards,

Riya

Ashutosh Munot1 · ‎05-05-2020

Hi,

Try this with one ci. It is first checking the duplicate ci and then for that ci it will check relationships and delete those.

var gr = new GlideAggregate('cmdb_ci');
gr.addAggregate('COUNT', 'serial_number');
gr.groupBy('serial_number');
gr.addHaving('COUNT', '>', 1);
gr.query();
while (gr.next())
{

var dup = new GlideRecord('cmdb_ci');
dup.addQuery('serial_number',gr.serial_number);
dup.orderByDesc('sys_created_on'); // keep latest record
dup.query();
if(dup.next())
{
var recent = dup.u_carrier_imei;//This should not be deleted as it is our original record
var del1 = new GlideRecord('cmdb_ci');
del1.addQuery('sys_id','!=',dup.sys_id);
del1.addQuery('serial_number',dup.serial_number);
del1.query();
while(del1.next())
{

var rel= new GlideAggregate('cmdb_rel_ci');

rel.addQuery('parent',del1.getValue('sys_id')); // ONly for this parent search all relationships

rel.groupBy('parent');

rel.groupBy('child');

rel.groupBy('type');

rel.setLimit(2);

rel.addHaving('COUNT', '>', 1);

rel.query();

while (rel.next()) {

var del= new GlideRecord('cmdb_rel_ci');

del.addQuery('parent', rel.parent);

del.addQuery('child', rel.child);

del.addQuery('type', rel.type);

del.setLimit(2);

del.query();

del.next(); // Skip the first result

while (del.next()) { // delete the rest

gs.print(del.parent.name.toString());

}
}
}
del1.deleteRecord();

}
}
}

Thanks,

Ashutosh

View solution in original post

Ct111 · ‎04-30-2020

Hello ,

Refer the below script

var dpchk = new GlideAggregate('table_name');

dpchk.groupBy('field_name'); // where you expect duplicate values

dpchk.addHaving('COUNT', '>', 1);

dpchk.query();

while(dpchk.next())

{

gs.print('CI that is duplicate is '+dpchk.u_name);

}

Mark my ANSWER as CORRECT and HELPFUL if it works

Ashutosh Munot1 · ‎04-30-2020

HI,

What happens if you try this

var relCI = new GlideRecord('cmdb_rel_ci');
relCI.addEncodedQuery('parentISEMPTY^ORchildISEMPTY^ORtypeISEMPTY');
relCI.query();
//relCI.deleteMultiple();

Thanks,

Ashutosh

Riya25 · ‎04-30-2020

Hello Ashutosh,

On running the above query i am getting 0 records in my sandbox but actually there are records where either child is empty or parent is empty or both child and parent are empty when i group by Parent.

Thank You.

Ashutosh Munot1 · ‎04-30-2020

HI,

Lemme try that.

Thanks,
Ashutosh