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

[Robert H]

Hello @PK14 ,

I would not recommend deleting the users, as that will break references to them.

Deactivating their user record might be a better approach.

Here is a script that finds the users with a duplicate email address and an Employee Status of "T". I have provided two functions: one for deactivating them and one for deletion. Pick the one you want to call.

Put the script into a Fix Script (if this is a one-time cleanup) or a Scheduled Script job.

var gaUser = new GlideAggregate('sys_user');
gaUser.addNotNullQuery('email');
gaUser.groupBy('email');
gaUser.addAggregate('COUNT');
gaUser.addHaving('COUNT', '>', 1);
gaUser.query();
while (gaUser.next()) {
	var grUser = new GlideRecord('sys_user');
	grUser.addQuery('email', gaUser.getValue('email'));
	grUser.addQuery('employee_status', 'T');

	deactivateTerminatedDuplicateUser(grUser);
	// deleteTerminatedDuplicateUser(grUser);
}

function deactivateTerminatedDuplicateUser(grUser) {
	grUser.setValue('active', '0');
	grUser.updateMultiple();
}

function deleteTerminatedDuplicateUser(grUser) {
	grUser.query();
	grUser.deleteMultiple();
}

There is a third approach: archiving the user records. This will remove them from the User table but won't break references. If you would like to do that instead please let me know.

PS: Background script do not generally cause instance hangs and inconsistent results. That happens only if they're not written correctly.

Regards,

Robert