How to identify users that have used a specific project
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
In some specific migration use cases, Jira admins would need to identify the users that have used a specific project (Issues, comments, assign, reporter, permissions etc), so that all user profiles can be populated accordingly in the target instance.
Solution
It is possible to retrieve a list of users that have created/assigned/commented in an issue, or is a member of any project roles in this specific project:
SQL Query
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT u.lower_user_name as "Username", u.directory_id
FROM projectroleactor pra
JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID
JOIN project p ON p.ID = pra.PID
JOIN app_user au ON au.user_key = pra.ROLETYPEPARAMETER
JOIN cwd_user u ON u.lower_user_name = au.lower_user_name
WHERE pra.roletype = 'atlassian-user-role-actor'
and p.pkey = 'project_key_here'
UNION
SELECT u.lower_user_name as "Username", u.directory_id
FROM projectroleactor pra
JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID
JOIN project p ON p.ID = pra.PID
JOIN cwd_membership cmem ON cmem.parent_name = pra.roletypeparameter
JOIN app_user au ON au.lower_user_name = cmem.lower_child_name
JOIN cwd_user u ON u.lower_user_name = au.lower_user_name
WHERE pra.roletype = 'atlassian-group-role-actor'
and p.pkey = 'project_key_here'
UNION
SELECT u.lower_user_name as "Username", u.directory_id
FROM jiraissue I
JOIN project p ON p.ID = I.project
JOIN app_user au ON au.user_key = I.assignee or au.user_key = I.reporter or au.user_key = I.creator
JOIN cwd_user u ON u.lower_user_name = au.lower_user_name
where p.pkey = 'project_key_here'
UNION
SELECT u.lower_user_name as "Username", u.directory_id
FROM jiraaction JA
JOIN jiraissue I on JA.issueid = I.id
JOIN project p ON p.ID = I.project
JOIN app_user au ON au.user_key = JA.author
JOIN cwd_user u ON u.lower_user_name = au.lower_user_name
where p.pkey = ''
order by 1, 2;ℹ️ This query is looking for users that have used a specific project key, make sure to adjust the where p.pkey = '' to whichever project key you would like to search.
Was this helpful?