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

Riya25 · ‎05-02-2020

Hello Ashutosh

Thanks for helping me.

Please let me know your results.

Actually its little bit urgent.

Rahul Priyadars · ‎05-01-2020

https://community.servicenow.com/community?id=community_blog&sys_id=74aca225dbd0dbc01dcaf3231f961978 Seems this blog may help you. BR RP

Riya25 · ‎05-05-2020

Hello experts,

Through the background script i have deleted the duplicate CI relationships.There are still 2 problems:

1. Still there are many records in CI relationship table where Parent and child are both empty and when i am trying to filter them using

:Parent is empty or child is empty I am getting 0 records,so is there any way through which i can delete them as they are of no use or i have to delete them manually only??

2.Next is when i am grouping the existing records by parent field after removing the duplicates then,

for ex 1 CI is ABC that CI is relationships are occurring 2-3 times after grouping also and when i checked the sys id of that Parent CI its different so is there any way by which i can sort out this or before cleaning the CI Relationship table i need to clean the main table??

Please help me to sort out this...

Thank You

Ashutosh Munot1 · ‎05-05-2020

Hi,

Removing relations wont delete the CI. So create a script in such a way that it will first check the Parent CI and then if duplicate then delete relationship and then the CI.

Thanks,

Ashutosh

Riya25 · ‎05-05-2020

Hello Ashutosh ,

Thank you for the reply.

So for deleting the duplicate CI first i need to clean up the CI table and then CI relationship table?

Right?

Or through the same script i can delete the duplicate CI also?

And what about empty parent and child relationships?

Regards,

Riya