How to list all Users and Groups in Jira
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
Please be mindful that the information below pertains to a service that is outside of Atlassian Support Scope. Any effort provided to support issues related to out of scope issues will be on a best-effort, as-is basis by the support engineer.
As an Admin you may want a report of all users and respective group membership in Jira.
Jira doesn't provide a UI functionality for this, so this article presents an alternative by querying the database directly.
While you're on this, you may also be interested in these other how-to articles on listing users. Depending on your specific need, you may need to run multiple DB queries or work on joining them together. Be welcomed to ask the Community if you've any questions on these or share your own queries!
Retrieve a list of users assigned to project roles in Jira server
How to list all permissions granted to a user in Jira(with Nested Groups)
Environment
Any version of Jira Software and Service Management Data Center.
Solution
The queries below are examples built on Postgres syntax that you may edit to adapt to your particular database. Our documentation on User and Group tables may prove useful.
This doesn't account for Managing nested groups (see the other queries below).
1
2
3
4
5
6
7
8
select u.id as "User Id", a.user_key as "App User Key", u.lower_user_name as "Lower Username", u.active as "User status", ud.id as "User Directory Id", ud.directory_position as "User Directory Order", ud.directory_name as "User Directory Name", ud.active as "User Directory Status", g.id as "Group Id", g.group_name as "Group Name", gd.id as "Group Directory Name", gd.directory_name as "Group Directory Name", gd.active as "Group Directory Status"
from cwd_user u
left join app_user a on a.lower_user_name = u.lower_user_name
join cwd_directory ud on ud.id = u.directory_id
left join cwd_membership m on m.child_id = u.id
left join cwd_group g on g.id = m.parent_id
left join cwd_directory gd on gd.id = g.directory_id
order by ud.directory_position ASC;On active users
A user's considered to be active if
It's active on the
cwd_usertableIt'd directory is active in the
cwd_directorytable (inactivating the directory doesn't update all users on the DB, it's an application logic)
If the same username's present in multiple directories, Jira considers the user from the first active directory in ascending order (directory_position), as ordered on the query above. On Jira's User Directory admin screen it's the same order as top-to-bottom.
Keep in mind a user may be active but not have access to Jira or particular projects and operations.
Other controls such as license attribution, permissions and project roles may prevent an active user from actually being able to use Jira.
List all Users member of a Group (with nested)
This query will list all users from the parent group, with and without Nested Groups enabled:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH RECURSIVE nested AS
(
select m.* from cwd_membership m where m.lower_parent_name = lower('some group')
UNION ALL
select m.* from cwd_membership m
join nested on m.lower_parent_name = nested.lower_child_name
),
all_users AS
(
select * from nested
where membership_type = 'GROUP_USER'
)
SELECT
parent_name as "Group",
child_name as "Username"
directory_id as "Directory Id"
FROM all_users;1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH nested AS (
SELECT m.* FROM cwd_membership m
START WITH m.lower_parent_name = 'group a'
CONNECT BY PRIOR m.lower_child_name = m.lower_parent_name
),
all_users AS
(
SELECT * FROM nested
WHERE membership_type = 'GROUP_USER'
)
SELECT
parent_name AS "Group",
child_name AS "Username",
directory_id as "Directory Id"
FROM all_users;1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH nested AS
(
select m.* from cwd_membership m where m.lower_parent_name = lower('some group')
UNION ALL
select m.* from cwd_membership m
join nested on m.lower_parent_name = nested.lower_child_name
),
all_users AS
(
select * from nested
where membership_type = 'GROUP_USER'
)
SELECT
parent_name as "Group",
child_name as "Username"
directory_id as "Directory Id"
FROM all_users;Replace "some group" on line 3 with the actual Group name. The lower function is to help mitigate upper/lowercase typos.
The "Group" column will show the immediate groups the user belongs to, but all groups shown on the result will be in the hierarchy of the "some group" through nested groups.
List all Groups a User belongs to (with nested)
This query will list all Groups a user belongs too, with and without Nested Groups:
1
2
3
4
5
6
7
8
9
10
11
12
WITH RECURSIVE nested AS
(
select m.* from cwd_membership m where m.membership_type = 'GROUP_USER' AND m.lower_child_name = lower('charlie')
UNION ALL
select m.* from cwd_membership m
join nested on m.membership_type = 'GROUP_GROUP' AND m.lower_child_name = nested.lower_parent_name
)
SELECT
parent_name as "Parent",
child_name as "Child",
directory_id as "Directory Id"
FROM nested;1
2
3
4
5
6
7
8
9
10
WITH nested AS (
SELECT m.* FROM cwd_membership m
START WITH m.membership_type = 'GROUP_USER' AND m.lower_child_name = lower('charlie')
CONNECT BY PRIOR m.lower_parent_name = m.lower_child_name AND m.membership_type = 'GROUP_GROUP'
),
SELECT
parent_name as "Parent",
child_name as "Child",
directory_id as "Directory Id"
FROM nested;1
2
3
4
5
6
7
8
9
10
11
12
WITH nested AS
(
select m.* from cwd_membership m where m.membership_type = 'GROUP_USER' AND m.lower_child_name = lower('charlie')
UNION ALL
select m.* from cwd_membership m
join nested on m.membership_type = 'GROUP_GROUP' AND m.lower_child_name = nested.lower_parent_name
)
SELECT
parent_name as "Parent",
child_name as "Child",
directory_id as "Directory Id"
FROM nested;Replace "charlie" on line 3 for the username.
The resulting "Parent" column will list all Groups the user belongs to, and the "Child", from where it's inherited (it can be the user itself directly or another Group).
Was this helpful?