How to list all Users and Groups in Jira

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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

 

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.


Summary

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!


Environment

Any version of Jira Software and Service Management, Server or 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 Nested Groups (see the other queries below).

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_user table
  • It'd directory is active in the cwd_directory table (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:

For Postgres, MySQL and MSSQL
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;
For Oracle
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;


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:

For Postgres, MySQL and MSSQL
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;
For Oracle
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;

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).



Last modified on Mar 1, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.