Merge Duplicate Records

[mballinger]

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 

[mballinger]

@Allen A - Thanks for your response. Yes I would be merging certain values from the duplicate record to the primary record. Can you please provide me an example query of how I would get to code snippet you provided?

Would it be something like the following:

var count = new GlideAggregate('cmdb_ci_computer');
count.addAggregate('count', 'name');
count.addHaving('count', '>', 1);
count.query();
while(count.next()) {
    var count2 = new GlideAggregate('cmdb_ci_computer');
    count2.addAggregate('count', 'serial_number');
    count2.addHaving('count', '>', 1);
    while(count2.next()) {
        var count3 = new GlideAggregate('cmdb_ci_computer');
        count3.addAggregate('count', 'model_id');
        count3.addHaving('count', '>', 1);
        count3.orderBy('created');
        while(count3.next()) {
            var duplicate = new GlideRecord('cmdb_ci_computer');
            duplicate.query('sys_id=' + count.sys_id);
            duplicate.query();
            if (duplicate.field_name) {
                primary.setValue('field_name', duplicate.getValue('field_name'));
            }
        }
    }
}

***EDITED***

The nested queries does not seem to be returning any data even though I know there are duplicates. This is what I tried, and nothing printed:

var count = new GlideAggregate('cmdb_ci_computer');
count.addAggregate('count', 'name');
count.addHaving('count', '>', 1);
count.query();
while(count.next()) {
    var count2 = new GlideAggregate('cmdb_ci_computer');
    count2.addAggregate('count', 'serial_number');
    count2.addHaving('count', '>', 1);
    while(count2.next()) {
        var count3 = new GlideAggregate('cmdb_ci_computer');
        count3.addAggregate('count', 'model_id');
        count3.addHaving('count', '>', 1);
        count3.orderBy('created');
        while(count3.next()) {
            var name = count3.name;
            var record_count = count3.getAggregate('count', 'name');
            gs.info("Duplicates: " + name + ", Count: " + record_count;
        }
    }
}

[Abhijit4]

Hi,

Coder inside me forced to solve your complicated issue.

I saw your updated code on Allen's reply. However, I think you are unnecessarily making code complicated.

Below script will help you to achieve your goal,

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.query(); // This will return number of records with same Name, Serial Number and Model ID

            while (grComputer.next()) {
                
               //Perform your opeartion on old and most recent record here.
               //Note: For each iteration, first record here will be your recent updated/create record
            }
        }
}

 I was not clear about what you wanted to do with latest and old records with same name, asset id and serial number therefore I kept that part onto you, let me know if you need my assistance for that part as well.

Please mark this as Correct or Helpful if it helps.

Thanks and Regards,
Abhijit

[mballinger]

@Abhijit - Thanks for your response! Yes I would definitely like your assistance for the second part as well.

The most recently created record will be the primary. The old record will need to be deleted. Before the old record is deleted, There are a few fields that need to be checked if empty on the primary record. If those fields are empty and the duplicate record has that missing information, then the dupe record fields (only missing ones from primary) would need to be copied over to the primary record. Once it is copied over, then it could be deleted.

Example:

Record 1 (Primary): 

• size: empty
• storage: empty
• screen: empty

Record 2 (Duplicate): 

• size: 32"
• storage: 128gigs
• screen: 4k

I need to copy record 2 info into Record 1 and then delete Record 2. 

[Abhijit4 · Accepted solution]

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

[Abhijit4]

Hi,

If my answers were helpful for you then please mark answer as Correct or Helpful so that it will be useful for others in future for similar scenario.

Thanks and Regards

Abhijit