Find duplicate users by email and username in Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Duplicate user records can occur when the same email address or username exists in multiple user directories or across multiple accounts. This article helps to identify duplicate active users by email address or username, including their directories.
Solution
You can use SQL queries against the Jira database to list active users that share the same email address or username, along with their directory details.
The queries below are written and tested for PostgreSQL. If you use another database, ask your database administrator (DBA) to adjust the syntax as needed.
Find duplicate active users by email address
This query returns active users whose email addresses are used by more than one account:
SELECT
u.user_name,
u.lower_user_name,
u.email_address,
d.id AS directory_id,
d.directory_name,
d.directory_type,
COUNT(*) OVER (PARTITION BY u.email_address) AS duplicate_count_per_email
FROM cwd_user u
JOIN cwd_directory d ON u.directory_id = d.id
WHERE u.active = 1
AND u.email_address IS NOT NULL
AND u.email_address <> ''
AND u.email_address IN (
SELECT email_address
FROM cwd_user
WHERE active = 1
AND email_address IS NOT NULL
AND email_address <> ''
GROUP BY email_address
HAVING COUNT(*) > 1
)
ORDER BY
u.email_address,
d.directory_name,
u.lower_user_name;
Find duplicate active users by username across directories
This query returns active users whose lowercase username appear more than once, for example, across multiple directories. The column duplicate_count_per_username shows how many entries share the same lower_user_name.
SELECT
u.user_name,
u.lower_user_name,
u.email_address,
d.id AS directory_id,
d.directory_name,
d.directory_type,
COUNT(*) OVER (PARTITION BY u.lower_user_name) AS duplicate_count_per_username
FROM cwd_user u
JOIN cwd_directory d ON u.directory_id = d.id
WHERE u.active = 1
AND u.lower_user_name IN (
SELECT lower_user_name
FROM cwd_user
WHERE active = 1
GROUP BY lower_user_name
HAVING COUNT(*) > 1
)
ORDER BY
u.lower_user_name,
d.directory_name,
u.user_name;
You may also find duplicate user accounts through REST calls.
Was this helpful?