Merge Duplicate Records

Go to solution
mballinger
Mega Guru

‎12-28-2021 11:57 AM

Hello,

I am trying to write a fix/background script to find all duplicate records and then merge them. I am not sure how to really do this. Records will be uniquely identified using 3 fields from the table and the most recently updated record will be the primary record. 

Here is what I have so far:

var count = new GlideAggregate('cmdb_ci_computer');
count.addAggregate('count', 'name');
//count.addAggregate('count', 'serial_number'); //This doesn't work 
//count.addAggregate('count', 'model_id'); //This doesn't work 
//count.groupBy('serial_number'); //Doesn't print out the name of the record
count.addHaving('count', '>', 1);
count.query();
while(count.next()) {
    var name = count.name;
    var record_count = count.getAggregate('count', 'name');
    gs.info("Duplicates: " + name + ", Count: " + record_count;
}

The code above returns the duplicate record plus the total count. I am also only able to uniquely identify duplicate records by using only 1 field. I need to use name, serial_number, and model_id to uniquely identify records.

The next step would then need to be merging the records by most recently updated/created. I have no idea how to attempt that.

Thanks!

@Ankur Bawiskar @Brad Bowman 

0 Helpfuls
  • 3,945 Views
1 ACCEPTED SOLUTION

Go to solution
Abhijit4
Mega Sage
In response to mballinger

‎01-04-2022 10:42 PM

Hi,

As per your requirement, I have implemented logic below.

var gaComputer = new GlideAggregate('cmdb_ci_computer');
gaComputer.groupBy("name");
gaComputer.groupBy("serial_number");
gaComputer.groupBy("model_id");
gaComputer.addAggregate("COUNT", "name");
gaComputer.query();

while (gaComputer.next()) {
    
    var duplicateCount = gaComputer.getAggregate("COUNT", "name");// This will return count of records where Name, Model ID and Serial Number is unique
    if (duplicateCount > 1) {

        var grComputer = new GlideRecord("cmdb_ci_computer");
        grComputer.addEncodedQuery("name=" + gaComputer.name + "^serial_number=" + gaComputer.serial_number + "^model_id=" + gaComputer.model_id);
        grComputer.orderByDesc("sys_updated_on"); // This will make sure to give most recently updated/created record first.
		grComputer.setLimit(1);// we are getting only primary record here (we will perform operation with secondary records in next while loop)
        grComputer.query(); // This will return number of records with same Name, Serial Number and Model ID

            if(grComputer.next()) {
                
               if(grComputer.getValue("size")=="" || grComputer.getValue("storage")=="" || grComputer.getValue("screen")==""){
				   
					var grComputer2=new GlideRecord("cmdb_ci_computer");
					grComputer2.addEncodedQuery("name=" + gaComputer.name + "^serial_number=" + gaComputer2.serial_number + "^model_id=" + gaComputer.model_id);
					grComputer2.orderByDesc("sys_updated_on"); // this will make sure that we are updating primary record with recent updated/created secondary records if there are more than 2 duplicate reocrds
					grComputer2.chooseWindow(1,duplicateCount); // this will provide us all duplicate records by excluding primary record as initial window starts with 1
					grComputer2.query();
					
					while(grComputer2.next()){
						
					if(grComputer.getValue("size")=="" && grComputer2.getValue("size")!=""){
					grComputer.setValue("size",grComputer2.getValue("size"));
					}
					if(grComputer.getValue("storage")=="" && grComputer2.getValue("storage")!=""){
					grComputer.setValue("size",grComputer2.getValue("storage"));
					}
					if(grComputer.getValue("screen")=="" && grComputer2.getValue("screen")!=""){
					grComputer.setValue("size",grComputer2.getValue("screen"));
					}
					grComputer.update();
				}
            }
        }
}

Please mark this as Correct or helpful if it helps.

Thanks and Regards,
Abhijit

By marking my response as correct or helpful, you contribute to helping future readers with similar issues.
Regards,
Abhijit
ServiceNow MVP

View solution in original post

16 REPLIES 16

Go to solution
Abhijit4
Mega Sage
In response to mballinger

‎01-14-2022 11:21 PM

Glad to know that your issue is resolved.

Thanks and Regards

Abhijit

By marking my response as correct or helpful, you contribute to helping future readers with similar issues.
Regards,
Abhijit
ServiceNow MVP

Go to solution
Vaishnavi Lathk
Mega Sage
Mega Sage

‎01-04-2022 10:51 PM

Hi ,

You can detect duplicate Ci's using back ground scripts. For instance look for a unique field in your form that is unique for each record like mac ip address or asset tag,

first system definition => Scripts - Background

 

I have used this script to get duplicates based on asset_tag field

 

gatherDupes();

 

function gatherDupes() {

 

var gr = new GlideAggregate('cmdb_ci_computer'); //you can do this based on any table in cmdb

 

gr.addAggregate('COUNT', 'asset_tag'); //based on what field

 

gr.groupBy('asset_tag'); //grouping by field

 

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

 

gr.query();

 

while (gr.next()) {

 

gs.log("Numb of Duplicates: " + gr.getAggregate('COUNT', 'asset_tag') + " => " + gr.asset_tag);

 

}

 

}

0 Helpfuls