How to Get a List of Active Users Counting Towards the JIRA application License

Still need help?

The Atlassian Community is here for you.

Ask the community

This article only applies to Atlassian's server products. Learn more about the differences between cloud and server.

Purpose

Sometimes it is useful to know exactly which users count against your license in JIRA applications.

JIRA applications license count is based on Global Permissions. Users with any of the following Global Permissions and is an Active status will count towards the license:

  • JIRA applications System Administrators
  • JIRA applications Administrators
  • JIRA applications Users


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.
Additionally, there is an improvement request to better handle this in JIRA applications as tracked under  JRA-29149 - Getting issue details... STATUS


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.

Managing 500+ users across Atlassian products?
Find out how easy, scalable and effective it can be with Crowd!
See  centralized user management .


Solution

JIRA 7 and above

JIRA 7 separated the licenses per each of the applications installed (JIRA Core, JIRA Software, JIRA Service Desk) 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 Desk
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 somecasesyou 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. Ifan useris 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 thiscasethe 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;

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.


Description

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.
Additionally, there is an improvement request to better handle this in JIRA applications

Product Jira
Platform Server



Last modified on Jan 14, 2019

Was this helpful?

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