Merging Duplicate Users

If you accidentally end up with duplicate users, say for example if you started with a CSV import and then switched to SCIM/LDAP syncing, and you now have duplicate users, one with jsmith as a username and one with [email protected] as a username, we have a few ways to handle that without losing associated checked out assets, accessories, etc.

Manually Merging Users via the GUI

If you only have a few duplicated users, you can manually merge them using the GUI merge tool in the Users listing:

Command-line Merge Tool

If you have a large directory with a lot of duplicated users where the username needs to be changed from jsmith to [email protected], you can use the command line merge tool.

This command allows you to merge the history of users. It looks for users without an email address as their username and merges them into the version that does have an email username. It will then mark the non-email username as deleted. As always, we recommend doing a backup before running any data-altering scripts.

php artisan snipeit:merge-users

Note that this will (currently) ONLY merge non-email usernames into email usernames, NOT the other way around.

Changing Username Format to Prevent Duplicates

If you already have your user list, but you know you're going to switch over to LDAP/AD/SCIM, etc where the username is expected to be an email address and activating those syncs would create duplicate users, you there are a few MySQL commands you can run beforehand to make the usernames set to be their email address.

As always, run a backup first. Running SQL commands directly on the database can end up in tears, so you always want to make sure you have a backup before starting.

First, check for duplicate email addresses:

SELECT id, username, email, first_name, last_name, COUNT(*) FROM users WHERE deleted_at IS NULL GROUP BY email HAVING COUNT(*) > 1;

You want to do this so that you don't end up with users with non-unique usernames. If there are 0 results, that's good news!

To copy the email address into the username field, run:

UPDATE `users` SET `username` = `email` where email!='' AND deleted_at IS NULL';

If you need to change the email address domain for your usernames and email addresses, for example after a rebrand or if your company got acquired, you could run this:

UPDATE `users` SET `email` = REPLACE(email, '@old-domain.com', '@new-domain.com') WHERE `email` LIKE '%@old-domain.org';

If you need to convert your usernames to a [email protected] format, you can run:

UPDATE `users` SET `email` = lower(CONCAT(first_name, '.', last_name,'@example.org')) WHERE `last_name`!='';