Useful SQL Queries for finding licensed users in Jira
- Hua Soon SIM [Akeles]
Owned by Hua Soon SIM [Akeles]
Oct 05, 2021
2 min read
Loading data...
Introduction
Sometimes there is a need to generate the list of users that are taking up licenses.
SQL Queries
This queries are for Jira 7 and above
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';
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 Core
the Jira Core license will only count users that are not present in any of the other two applications
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'));References