Retrieve the list of licensed project users using a database query
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
This article combines the information from Retrieve a list of users assigned to project roles in Jira server and Get list of licensed users in Jira server to get the list of users assigned to project roles who are using up a license slot.
This can be used for auditing or housekeeping purposes.
Solution
This was written and tested using a PostgreSQL DB, so you may need to tweak it depending on the database you are using.
Project roles can either be assigned to groups, or directly to users. Both of these queries use the queries from Retrieve a list of users assigned to project roles in Jira server to get groups and users assigned to project roles, and then join them with the query in Get list of licensed users in Jira server to filter out those users who are taking up a license
DB query to retrieve the groups assigned to project roles and group members
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT p.pname as ProjN, pr.NAME as roleN, pra.roletype, pra.roletypeparameter, cm.child_name, cu.active
FROM projectroleactor pra
INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID
INNER JOIN project p ON p.ID = pra.PID
INNER JOIN cwd_membership cm on pra.roletypeparameter = cm.parent_name
INNER JOIN cwd_user cu ON cu.id = cm.child_id AND cu.directory_id = cm.directory_id
INNER JOIN licenserolesgroup lrg ON Lower(cm.parent_name) = Lower(lrg.group_id)
INNER JOIN cwd_directory d
ON cm.directory_id = d.id
WHERE d.active = '1'
AND cu.active = '1'
AND license_role_name in ('jira-software','jira-servicedesk','jira-core')
order by p.pname;DB query to retrieve users directly assigned to project roles
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT distinct p.pname as ProjN, pr.NAME as roleN, pra.roletype, pra.roletypeparameter, cu.user_name, cu.active
FROM projectroleactor pra
INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID
INNER JOIN project p ON p.ID = pra.PID
INNER JOIN app_user au on pra.roletypeparameter = au.user_key
INNER JOIN cwd_user cu on au.lower_user_name = cu.lower_user_name
INNER JOIN cwd_membership m
ON cu.id = m.child_id
AND cu.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 cu.active = '1'
AND license_role_name in ('jira-software','jira-servicedesk''jira-core')
order by p.pname;Was this helpful?