Retrieve Licensed Users List in Jira Data Center
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
Learn how to get a list of licensed users in Jira.
Jira applications license count is based on Global Permissions and Application Access. Users with any of the following Global Permissions and is an Active status will count towards the license:
Jira applications System Administrators (Global Permission)
Jira applications Administrators (Global Permission)
Jira applications Users (groups given Application Access via Administration >> Application >> Application Access)
Within the UI, you can get a listing of users by individually examining the groups that are assigned one or more of the above Global Permissions. If you are using Jira 4.3 and above, you can also use the following SQL query to return a single list of users that count towards the license, per our documentation:
Licensing fees are quoted per number of 'active users'. An active user in Jira Core is by definition any user account in the system with the "Jira Software Users" global permission or the "Jira Administrators" permission,i.e., anyone who can log in. Unlimited 'anonymous users' are permitted on all licenses.
This does not, however, affect assignable users, as long as the user exists in cwd_user, and they are marked as active, they will not need application access in order to assign them a ticket, they will however not be able to log in and work the ticket unless anonymous access is set up on the instance.
Additionally, there is an improvement request to better handle this in Jira applications as tracked under https://jira.atlassian.com/browse/JRASERVER-2841
Solution
Counting users
This SQL query may not return accurate results if you are using nested groups in LDAP or Crowd, or if you have users with duplicated usernames across multiple directories. Therefore, in case of discrepancy, consider the license count given in the Jira user interface instead.
The queries below will retrieve all users. If you only need to count the number of licensed users, replace the first portion SELECT DISTINCT u.lower_user_name of the queries with SELECT count as shown below instead. This will reduce the load on the database when running the queries, especially if you intend to run it on a regular basis.
SELECT count(distinct u.lower_user_name)
Jira 7 and above
Jira 7 separated the licenses per each of the applications installed (Jira Core, Jira Software, Jira Service Management) so we won't have a single list of active users anymore, but a list of active users per application.You can find the queries for each of the applications active users below. Please note the Jira Core license will only count users that are not present in any of the other two applications
Jira Core
SELECT DISTINCT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN licenserolesgroup lrg
ON Lower(m.parent_name) = Lower(lrg.group_id)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE d.active = '1'
AND u.active = '1'
AND license_role_name = 'jira-core'
AND u.lower_user_name not in
(SELECT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN licenserolesgroup lrg
ON Lower(m.parent_name) = Lower(lrg.group_id)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE d.active = '1'
AND u.active = '1'
AND license_role_name in ('jira-software','jira-servicedesk'));Jira Software
SELECT DISTINCT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN licenserolesgroup lrg
ON Lower(m.parent_name) = Lower(lrg.group_id)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE d.active = '1'
AND u.active = '1'
AND license_role_name = 'jira-software';Jira Service Management
SELECT DISTINCT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN licenserolesgroup lrg
ON Lower(m.parent_name) = Lower(lrg.group_id)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE d.active = '1'
AND u.active = '1'
AND license_role_name = 'jira-servicedesk';List of active users included administration
<Append-SQL-query-above>
UNION
SELECT DISTINCT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN globalpermissionentry gp
ON Lower(m.parent_name) = Lower(gp.group_id)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE gp.permission IN ('USE','ADMINISTER','SYSTEM_ADMIN')
AND d.active = '1'
AND u.active = '1'
ORDER BY lower_user_name;Jira 6.2.1 to 6.4.14
SELECT DISTINCT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN globalpermissionentry gp
ON Lower(m.parent_name) = Lower(gp.group_id)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE gp.permission IN ('USE','ADMINISTER','SYSTEM_ADMIN')
AND d.active = '1'
AND u.active = '1';Jira 6.2 and below
SELECT DISTINCT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN schemepermissions sp
ON Lower(m.parent_name) = Lower(sp.perm_parameter)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE sp.permission IN ( '0', '1', '44' )
AND d.active = '1'
AND u.active = '1';List of Users per Directory
In some cases you may wish to also return more information from that user, such as first, last and e-mail and also any other directories they are in. If an user is in multiple directories with different first, last or e-mail addresses, then they would be returned multiple times. This would skew the number of returned users, so if you're simply looking for a list + count, use the above. In this case the query can be modified as follows:
Jira 6.2 and below
SELECT DISTINCT u.lower_user_name,
u.first_name,
u.last_name,
u.email_address,
d.directory_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN schemepermissions sp
ON Lower(m.parent_name) = Lower(sp.perm_parameter)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE sp.permission IN ( '0', '1', '44' )
AND d.active = '1'
AND u.active = '1'
ORDER BY directory_name,
lower_user_name;Starting with Jira 5.1, you can deactivate users without removing the user from all groups as documented in our JIRA 5.1 Release Notes.
Jira 6.2.1 and above
SELECT DISTINCT u.lower_user_name,
u.first_name,
u.last_name,
u.email_address,
d.directory_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN globalpermissionentry gp
ON Lower(m.parent_name) = Lower(gp.group_id)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE gp.permission IN ('USE','ADMINISTER','SYSTEM_ADMIN')
AND d.active = '1'
AND u.active = '1'
ORDER BY directory_name,
lower_user_name;To identify any users belonging to a particular group, this can either be done through the GUI or with the below SQL, replacing jira-users with the appropriate group:
SELECT DISTINCT u.lower_user_name,
d.directory_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE m.lower_parent_name = 'jira-users'
AND d.active = 1
AND u.active = '1'
ORDER BY directory_name,
lower_user_name;Count active users present in all the groups
Below SQL query can be used to count the total number of active users present in each Jira user group. Under the where clause, the users can specify the cwd_group.directory_id and can provide the list of the desired directory IDs.
User directory IDs can be fetched using SQL. The Id column displays the same.
SELECT * FROM cwd_directory;
Example: In the below SQL query, we are using directory IDs 10000 and 1
SELECT cwd_group.group_name,
count(cwd_user.id)
FROM cwd_user
JOIN cwd_membership
ON cwd_membership.child_id = cwd_user.id
JOIN cwd_group on cwd_membership.parent_id = cwd_group.id
WHERE cwd_user.active = 1
AND cwd_group.directory_id in (10000,1)
group by cwd_group.group_name;The above examples SQL have been tested with PostgreSQL and may need to be modified depending on the DBMS used.
Another option is to go into the "User Management" page and click on "Create User". A pop-up window will appear with the fields to create a new user, and on the top of this pop-up there should be a message with your user's number information.
Was this helpful?