How to identify user accounts sharing the same e-mail address in Jira
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
Scope Alert
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.
環境
Jira Data Center 8.13.x synced with external User Directories.
PostgreSQL database.
ソリューション
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.
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)