How to delete duplicate records through fix script

[Afrin12]

Hi
I have a requirement . In a Product Module(cmdb_model) table 

When a new record is added by any way

Then the system will run a process to identify if a record with the same “Model Name” already exists.If so, proceed deleting the new record.
It should delete the records if Class = Hardware model or Product model ... etc.

Any ideas would be Appreciated.
Thanks in advance.
@AnubhavRitolia  Could you please help me with this.

[AnubhavRitolia · Accepted solution]

Hi @Afrin12 

Instead of creating and deleting again, you can create Before Business Rule to validate your conditions, and if already any record present with similar data according to your requirement, you can simply abort Action.

Fix Script does not work for each record instead yo need to do using Business rule.

[jaheerhattiwale · Accepted solution]

@Afrin12 You can create a before business rule for aborting the models getting created in future as suggested by @AnubhavRitolia.

For existing records you can create a fix script with below code:

var modelClasses = ["cmdb_hardware_product_model", "cmdb_model"];

for (var i = 0; i < modelClasses.length; i++) {
var grModel = new GlideRecord(modelClasses[i]);
grModel.addQuery("sys_class_name=" + modelClasses[i]); //This line is added for "Product Model" class as ot a parent table as well
grModel.orderByDesc("sys_created_on");
grModel.query();

while (grModel.next()) {
var gaModel = new GlideAggregate(modelClasses[i]);
gaModel.addQuery("name="+grModel.name.toString());
gaModel.addAggregate("COUNT");
gaModel.query();

if(gaModel.next()){
if(gaModel.getAggregate("COUNT") > 1){
grModel.deleteRecord();
}
}
}
}

Please mark as correct answer if this solves your issue.

[AnubhavRitolia · Accepted solution]

Hi @Afrin12 

Instead of creating and deleting again, you can create Before Business Rule to validate your conditions, and if already any record present with similar data according to your requirement, you can simply abort Action.

Fix Script does not work for each record instead yo need to do using Business rule.

[jaheerhattiwale · Accepted solution]

@Afrin12 You can create a before business rule for aborting the models getting created in future as suggested by @AnubhavRitolia.

For existing records you can create a fix script with below code:

var modelClasses = ["cmdb_hardware_product_model", "cmdb_model"];

for (var i = 0; i < modelClasses.length; i++) {
var grModel = new GlideRecord(modelClasses[i]);
grModel.addQuery("sys_class_name=" + modelClasses[i]); //This line is added for "Product Model" class as ot a parent table as well
grModel.orderByDesc("sys_created_on");
grModel.query();

while (grModel.next()) {
var gaModel = new GlideAggregate(modelClasses[i]);
gaModel.addQuery("name="+grModel.name.toString());
gaModel.addAggregate("COUNT");
gaModel.query();

if(gaModel.next()){
if(gaModel.getAggregate("COUNT") > 1){
grModel.deleteRecord();
}
}
}
}

Please mark as correct answer if this solves your issue.

[AnubhavRitolia · Accepted solution]

Hi @Afrin12 

For existing duplicate records, you can use below Fix Script:

var model = new GlideAggregate('cmdb_hardware_product_model');
model.groupBy('model_name'); // Backend name of Model Name field
model.query();

while(model.next())
{
var grModel = new GlideRecord('cmdb_hardware_product_model');
grModel.addQuery('model_name', model.model_name); // Backend name of Model Name field
grModel.orderBy('sys_created_on');
grModel.query();
grModel.next();
while(grModel.next())
{
grModel.deleteRecord();
}
}

Now for new records, you can prevent it from getting creating using below Before Business Rule:

var grModel = new GlideRecord('cmdb_hardware_product_model');
grModel.addQuery('model_name', current.model_name); // Backend name of Model Name field
grModel.query();

while(grModel.hasNext())
{
current.setAbortAction(true);
}

[Afrin12]

Thank you