How to identify user accounts sharing the same e-mail address in Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian products 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
Using SQL for business intelligence is beyond the scope of Atlassian Support. Some of our products have a community page for database queries. Please help us as well as the community by contributing your knowledge!
We have popular communities – see both the "Reporting for JIRA" and "Reporting for Confluence" parts of our community.
This knowledge base is intended to provide a simple query to find different accounts using the same e-mail address in Jira database for both Internal and External directories.
Environment
Jira Data Center 8.13.x synced with external User Directories.
PostgreSQL database.
Solution
The SQL query below can be used to list user accounts that are using the same e-mail address between different User Directories.
This query was built to PostgreSQL and may require some changes to run in different database flavors.
1
2
3
4
5
6
7
8
9
SELECT cwd_directory.directory_name as "Directory Name",
cwd_user.lower_user_name as "User Name",
cwd_user.lower_email_address as "E-mail Address"
FROM cwd_user, cwd_directory
WHERE lower_email_address IN (
SELECT lower_email_address
FROM cwd_user
GROUP BY lower_email_address
HAVING COUNT(*) > 1)
Was this helpful?