Delete Duplicate record in user table

[PK14]

Hi SN Expert,

I’m currently facing a challenge with deleting user records from the sys_user table based on the following condition:

1. Multiple users might share the same email.
2. I need to identify users with a terminated employee status (i.e., “T”) for each email group.
3. If any user(s) within that email group has a terminated status, those specific records should be deleted.
   
   Here’s an example for clarity:
   If there are 3 users with the same email and 1 (or all) has employee status “T,” those terminated records should be removed.
   
   Key considerations:
   I cannot use a background script as it causes instance hangs and inconsistent results. I'm looking for the correct approach to efficiently identify and delete such records, preferably with accurate and reliable steps. This is quite urgent, so your prompt assistance will be greatly appreciated.
   
   Regards,
   Priya

[J Siva]

Hi @PK14 

If you are going to do this as a one time activity, then go with Fix script and run as async.

Else, use scheduled job to fulfill your requirement.

As a best practice, you shouldn't delete any foundation data as it might have referred somewhere like incidents, tasks etc.

Regards,

Siva

[Ankur Bawiskar]

@PK14 

the 1st point itself is not valid -> Multiple users might share the same email.

How are your users loaded?

If it's via LDAP and AD then ideally it should not allow same email for multiple users.

1st identify why users are there with same email so that this issue doesn't occur in future

If my response helped please mark it correct and close the thread so that it benefits future readers.

[maheshkhatal]

@PK14  You should use fix script(you can always roll back in case deletion doesn't go as expected). Rollback is also available with the fix script. This is the safest and reliable approach as per my understanding.

Mark my response as helpful,

Thanks,

Mahesh.

[sunil maddheshi]

@PK14 

Create a Script Include to Get Terminated Users in Batches

var UserCleanupUtil = Class.create();
UserCleanupUtil.prototype = {
    initialize: function() {},

    getTerminatedUsersToDelete: function(limit) {
        var usersToDelete = [];

        // 1. Query all users with employee_status = "T"
        var terminatedGR = new GlideRecord('sys_user');
        terminatedGR.addQuery('employee_status', 'T');
        terminatedGR.query();

        var emailMap = {};

        while (terminatedGR.next()) {
            var email = terminatedGR.email + '';
            if (!emailMap[email]) {
                emailMap[email] = [];
            }
            emailMap[email].push(terminatedGR.sys_id.toString());
        }

        // 2. For each email group, check if other users share that email
        for (var email in emailMap) {
            var countGR = new GlideAggregate('sys_user');
            countGR.addQuery('email', email);
            countGR.addAggregate('COUNT');
            countGR.query();
            if (countGR.next() && parseInt(countGR.getAggregate('COUNT')) > 1) {
                usersToDelete = usersToDelete.concat(emailMap[email]);
                if (usersToDelete.length >= limit) {
                    break;
                }
            }
        }

        return usersToDelete.slice(0, limit);
    },

    deleteUsersByIds: function(userIds) {
        var gr = new GlideRecord('sys_user');
        gr.addQuery('sys_id', 'IN', userIds.join(','));
        gr.query();

        var count = 0;
        while (gr.next()) {
            gr.deleteRecord();
            count++;
        }
        return count;
    },

    type: 'UserCleanupUtil'
};

Create a Scheduled Script Execution Job-Use this to chunk deletions and avoid performance issues.(Run every min or hour as per requirement)

var util = new UserCleanupUtil();
var batchSize = 100; // Safe size
var usersToDelete = util.getTerminatedUsersToDelete(batchSize);

if (usersToDelete.length > 0) {
    var deleted = util.deleteUsersByIds(usersToDelete);
    gs.info('[UserCleanup] Deleted ' + deleted + ' terminated users.');
} else {
    gs.info('[UserCleanup] No terminated users to delete in this batch.');
}

Please mark correct/helpful if this helps you!