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

[Riya25]

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]

HI,

Go step by step:

1) Check Duplicate CI.

2) If duplicate CI then look at there relationship.

3) Delete relationships and then CI in same script.

4) Once this is done then concentrate on empty parent and child relationship.

Thanks,
Ashutosh

[Riya25]

Hello Ashutosh,

Thank you for the reply.

I am using the following script in order to delete the duplicate CI Relationships:

var rel= new GlideAggregate('cmdb_rel_ci');

rel.groupBy('parent');

rel.groupBy('child');

rel.groupBy('type');

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.query();  

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

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

            del.deleteRecord();  

    }  

}

Can you please tell where to add and what to add in this script in order to delete the CI's.

Regards,

Riya

[Ashutosh Munot1 · Accepted solution]

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

[Riya25]

Hello Ashutosh.

Thank you so much for your help.

I have tried executing this script and before deleting i have tried to print the row count of deleted records.

I have attached the screenshot of the results.

It is giving Undefined so can you please tell how to solve this issue.

Regards,

Riya

[Ashutosh Munot1]

Show your script