Having Organization Members Details from JSM Database after GDPR Change
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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
Since Jira 8.4.0, we introduced a new way to store user's keys in the Jira Database, per the section Generating user keys in the documentation GDPR changes in Jira. The key any new user created after the upgrade to Jira 8.4.0 or JSM 4.4.0 (and any higher version) will have the format "JIRAUSERXXXXX" and when reporting the Organization data from database we need to use User name from APP_USER table instead of Organization tables to have meaningful reports with comprehensible User names
Environment
After JSM 4.4.0
Solution
The below sample query will list all organization members with understandable user names, it can be used for reporting purposes
This was written and tested using a PostgreSQL DB, for other database types you may need to tweak it depending on the database you are using.
SELECT DISTINCT app.lower_user_name as user,
org."NAME" as org_name,
project.pname as project_name , member."ORGANIZATION_ID" ,member."USER_KEY"
FROM "AO_54307E_ORGANIZATION" org ,
"AO_54307E_ORGANIZATION_MEMBER" member,
"app_user" app,
"AO_54307E_ORGANIZATION_PROJECT" orgproject,
"project" project
WHERE
app.user_key = member."USER_KEY"
AND
member."ORGANIZATION_ID" = org."ID"
AND
orgproject."PROJECT_ID" = project.id;