Fix script to remove duplicate users

[Kanika4]

Hi All,

Can you please help me in writing the script to fix all the duplicate users in sys_user table.

All the CIs, assets should move from the duplicate profile to the correct one.

I have a list of users with duplicate profiles.

Thanks

[Eshwar Reddy]

Hi @Kanika4 
Could you explain the criteria you use to determine if a user profile is a duplicate?

Thanks
Eshwar

[Kanika4]

I just have a list of 450 users that are duplicates.

[PrashantLearnIT]

HI @Kanika4 

You can use the below script to fix your problem statement:

// Adjust the query to identify duplicate users based on email or other criteria
var duplicateUsers = new GlideAggregate('sys_user');
duplicateUsers.addAggregate('COUNT', 'email'); // Use 'user_name' or other fields if needed
duplicateUsers.addHaving('COUNT', '>', '1');
duplicateUsers.groupBy('email');
duplicateUsers.query();

while (duplicateUsers.next()) {
var email = duplicateUsers.getValue('email');
var primaryUser = getPrimaryUser(email); // Fetch the correct user profile to retain

// Find all duplicate users with the same email
var dupeUserGR = new GlideRecord('sys_user');
dupeUserGR.addQuery('email', email);
dupeUserGR.addQuery('sys_id', '!=', primaryUser.sys_id);
dupeUserGR.query();

while (dupeUserGR.next()) {
gs.info('Processing duplicate user: ' + dupeUserGR.getValue('name'));

// Transfer Assets
transferRelatedRecords('alm_asset', 'assigned_to', dupeUserGR.sys_id, primaryUser.sys_id);

// Transfer Configuration Items
transferRelatedRecords('cmdb_ci', 'owned_by', dupeUserGR.sys_id, primaryUser.sys_id);

// Add any other relevant table mappings here...

// Optionally, deactivate or delete the duplicate user
dupeUserGR.setValue('active', false); // Recommended instead of delete
dupeUserGR.update();
}
}

// Function to fetch the primary user profile (update logic based on your criteria)
function getPrimaryUser(email) {
var userGR = new GlideRecord('sys_user');
userGR.addQuery('email', email);
userGR.orderBy('sys_created_on'); // Keep the oldest record
userGR.query();
if (userGR.next()) {
return userGR;
}
return null;
}

// Helper function to transfer related records
function transferRelatedRecords(table, field, oldUserId, newUserId) {
var gr = new GlideRecord(table);
gr.addQuery(field, oldUserId);
gr.query();
while (gr.next()) {
gr.setValue(field, newUserId);
gr.update();
gs.info('Transferred record ' + gr.getDisplayValue() + ' in ' + table);
}
}

[Kanika4]

Actually, I have a set of users with me like below:

{

        "sparedRecord": "61bebbefdbe48xxxxxxx7f43a961997",

        "duplicateRecords": "ccf415f5dxxxxxx3602a584059619ae"

    },

    {

        "sparedRecord": "f652aaa0dbxxxxx7f43a9619e2",

        "duplicateRecords": "c4b40c594xxxxx2801e4aafee0310c796"

    }

Need to transfer the CIS, assets, tickets from the sparedRecord to duplicateRecords.

The number of tables are around 40 and number of users are around 400.

Could you please help me to do in this way