Duplicate user options

Known fixes for duplicate user email entries reported by Cloud data import.

General approaches

Preferred outcome for duplicate user. However actually implementing one of these options is complicated depending on where the user references exist.

Conflict	Strategy	E.g.
Internal and External user entries for same email	Remove internal user	hashim blah and hashim@blahl.com have same email. probably came onto system via system import.
Domain and external users with same email	Move data to Domain user
Internal users with same email attached	merge user entries	it.support@blah.com, noreply@blah2.com
Multiple functions with same email	create separate emails. Should be valid where automation owner and will receive fail notifications.	jira.support@blah.com used in automation account users
Mysteries	flagged as duplicate but can't be seen in user data. search for emails, case insensitive	bognar@blah.com

Fixes

Find all duplicate users

Find all duplicates

select lower_email_address, count(lower_email_address), array_agg(user_name) as "Users with Dupe E-Mail"
from cwd_user group by lower_email_address having count(lower_email_address) > 1;

Remove internal user duplicate.

Find user entries with the same email address. Email address is a unique key in the cloud.

Find duplicate user

SELECT id, directory_id, user_name, lower_user_name, active, created_date, updated_date, first_name, lower_first_name, last_name, lower_last_name, display_name, lower_display_name, email_address, lower_email_address, credential, deleted_externally, external_id
	FROM public.cwd_user
	where lower_email_address like '%upatel%'

for results as below

The directory_id with value equals 1 is the internal directory entry. 10700 is the Crowd entry.

Delete the internal user by record id

DELETE FROM public.cwd_user
	WHERE id = 11621;

You will need to re-export groups and users to cloud using then Atlassian Jira Migration Assistant.

Options

User reference
User has made comments	Script option to change comment author but does not always work.	Old ticket and minor comment, consider deleting it.
User is reporter or assignee	Script option to change entries.	https://github.com/listertuk/groovy/blob/5c7ea4f720ba8b8f69cc825f74a2f10760114fc0/server-dc/Scriptrunner/LDAP/FormMoveUserGroupsAndTickets.groovy
System users	Can they be merged to one user.	Or use separate email addresses
Multiple functions	merge or create separate emails
Domain and external users with same email	this can be complicated by users history	e.g.