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-06-2020

Hello Ashutosh,

I have used the same script given by you in the last reply.

And i have added the screenshot also.

Thanks!!

Riya25 · ‎05-07-2020

Hello Ashutosh,

Have you got something by which we can solve these errors in the script.

And also do you think we should delete CI's from each class rather than the base class?

And I have read that we shouldn't delete the CI's but make them retired but in our if we do so then relationship associated with that CI will remain the same ?

Right?

What Should be the best approach to solve this issue?

And Sorry for troubling you this much!!

Regards,

Riya

Ashutosh Munot1 · ‎05-08-2020

Hi,

Yes we should not delete the CI if they have incident ,task assigned to it. But if you think they are really duplicate then we can do cleanup and substitute original CI this task records,etc.

You can keep the CI as retired and then delete only CI relations. If you don't delete the CI Relations then it will remain.

I see that the script is not showing count in del.getRowCount()

Meaning the query is wrong.

Thanks,
Ashutosh

Riya25 · ‎05-08-2020

Hello Ashutosh, Any idea how can we clean CI relationship table without deleting CI's . And we want to delete CI's what will be the right script to execute. Thanks, Riya

Hemaxiben Mistr · ‎05-30-2023

Anyone tried this script?