Merge duplicate records in the user table (sys_user )

[rajesh kumar9]

HI ,

i want to merge user records having same username and different roles 

lets suppose user 1 created before another user 1, i want to merge another user1 roles to the earlier user1 and delete the 

another user1 record 

user1 (primary)       created earlier         role (itil)                           sysid (234)       

user1 (second)      created later             role(security_admin)           sysid(343)

after merging

user1(primary)   created earlier      role (itil,security_admin)         sysid(234)

another user1 with sysid  343 is deleted

Help me with code in background/fix script 

[Konstantin7 · Accepted solution]

Below is a started code I have written before to remove duplicate records. Use it as a start but edit it on what conditions you want to identify which sys_user role you will keep and how you will merge the data.

For best practice, you do NOT want to assign roles directly to users. Assign the roles to groups and move users to groups based on structure and roles needed.

var table = ''; //enter table here
var allRecords = []; //array of records
var rec = {}; //record object
var removedRec = []; //List of removed records

var gr = new GlideRecord(table);
gr.sortByDesc('sys_updated_on');
gr.query();

//first store all records into an array
while(gr.next()){
        rec = {}; //Clear the object
        rec.sys_id = (gr.sys_id).toString(); //Store the system id.
        rec.updated = gr.sys_updated_on; //Store the date updated on.
        rec.s_num = (gr._____).toString(); //Store the Serial Number. [you need to enter the field name]
        rec.ci = (gr.____).toString(); //Store the CI. [you need to enter the field name]
        allRecords.push(rec); //Push the record object into the array
}

//Then loop through all records
for(x=0; x<allRecords.length; x++){
        if(!removedRec.includes((allRecords[x].sys_id).toString())) //Skip record if it has been deleted
                findDuplicate(allRecords[x]); //Pass each object into the function to find duplicate
}

//For testing you, could pring out the sys_id's and verify that they are duplicates
gs.print(removedRec);

var removedRecStr = removedRec.join(); //Convert the array into comma separated string
var del = new GlideRecord(table); //Get the records 
del.addQuery('sys_id','IN',removedRecStr); //where system ID is in the removedRecStr list
del.query();
//now loop through all records
while(del.next()){
        del.deleteRecord(); //and delete the records
}

function findDuplicate(record){
        var dubRecords = []; //duplicate records
        var d2; //date 2 placeholder
        
        //loop through all records
        for(y=0; y<allRecords.length; y++){
                //If serial number matches, and CI matches, and System ID is different
                if(record.s_num == allRecords[y].s_num && record.ci == allRecords[y].ci && record.sys_id != allRecords[y].sys_id){
                        dubRecords.push(record); //then store the record
                }
        }
        
        //if duplicates are found
        if(dubRecords.length > 1){
                //because we sorted by sys_updated_on, the first record should be the most resent updated, but lets make sure of that
                var d1 = new Date(record.updated); //ensure that the date is being rendered correctly
                for(d=0; d<dubRecords.length; d++){
                        d2 = new Date(dubRecords[d].updated); //ensure that the date is being rendered correctly
                        if(d1 > d2){ //if d1 is greater
                                removedRec.push(dubRecords[d].sys_id); //store the duplicate record system id
                        } else { //if d2 is greater
                                removedRec.push(record.sys_id); //Store the duplicate record system id
                                d1 = new Date(dubRecords[d].updated); //Change the d1 to most recent updated record //ensure that the date is being rendered correctly
                                record = dubRecords[d]; //Override the record
                        }
                }
        }
} 

Hope this helps.