How to list inactive users by Project Role 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
For purpose of house-keeping, we way want to check if there are inactive users listed in Project Roles. If inactive, you may consider revoking their access to such projects.
Environment
All versions of Jira Core 7 and 8.
Solution
Solution 1 - Looking for inactive users who assigned a project role
The query below will list all users who have been granted a project role, but which Jira account was disabled.
1
2
3
4
5
6
7
8
9
10
11
12
select pra.PID, p.pname as Project_Name, pra.PROJECTROLEID, pr.NAME as Project_Role_Name, pra.ROLETYPE, pra.ROLETYPEPARAMETER, cu.display_name, cu.lower_user_name
from projectroleactor pra
join project p
on p.ID = pra.PID
join projectrole pr
on pr.ID = pra.PROJECTROLEID
join app_user au
on au.user_key=pra.roletypeparameter
join cwd_user cu
on cu.lower_user_name=au.lower_user_name
where cu.active = 0
order by Project_Name asc, Project_Role_Name asc, ROLETYPE asc, ROLETYPEPARAMETER asc;Sample output
1
2
3
pid | project_name | projectroleid | project_role_name | roletype | roletypeparameter | display_name | lower_user_name
-------+--------------+---------------+------------------------+---------------------------+-------------------+--------------------+--------------------
10000 | SDBASIC | 10100 | Service Desk Customers | atlassian-user-role-actor | JIRAUSER12401 | Test User | testuserSolution 2 - Looking for active users who are assigned a project role and who haven't logged for a while, or who never logged in
The query below will list all users, their respective project roles and the last login date. Empty means they never logged in Jira:
Postgres example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select distinct a.name, a.role_name, a.username, to_timestamp(CAST(a.last_login AS BIGINT)/1000) as last_login
from (select replace(p.pname,' ','_') as name, p.pkey, replace(r.name, ' ', '_'), ra.roletypeparameter as roleuser, u.user_name as username, ua.attribute_value as last_login, r.name as role_name
from projectrole r
join projectroleactor ra on ra.projectroleid = r.id and ra.roletype = 'atlassian-user-role-actor'
join project p on p.id = ra.pid
join app_user a on a.user_key = ra.roletypeparameter
join cwd_user u on u.lower_user_name = a.lower_user_name and u.active = 1 and u.id = (select u2.id from cwd_user u2 join cwd_directory d2 on d2.id = u2.directory_id and d2.active = 1 where u2.lower_user_name = u.lower_user_name order by d2.directory_position asc limit 1)
left join cwd_user_attributes ua on ua.user_id = u.id and ua.attribute_name = 'login.lastLoginMillis'
union
select replace(p.pname,' ','_') as name, p.pkey, replace(r.name,' ','_'), m.child_name as roleuser, u.user_name as username, ua.attribute_value as last_login, r.name as role_name
from project p
join projectroleactor ra on ra.pid = p.id and ra.roletype = 'atlassian-group-role-actor'
join projectrole r on r.id = ra.projectroleid
join cwd_membership m on m.parent_name = ra.roletypeparameter and m.membership_type='GROUP_USER'
join cwd_user u on u.lower_user_name = m.lower_child_name and u.active = 1 and u.id = (select u2.id from cwd_user u2 join cwd_directory d2 on d2.id = u2.directory_id and d2.active = 1 where u2.lower_user_name = u.lower_user_name order by d2.directory_position asc limit 1)
left join cwd_user_attributes ua on ua.user_id = u.id and ua.attribute_name = 'login.lastLoginMillis'
order by pkey) a;MySQL example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select distinct a.name, a.role_name, a.username, from_unixtime((cast(a.last_login AS UNSIGNED)/1000)) as last_login
from (select replace(p.pname,' ','_') as name, p.pkey, replace(r.name, ' ', '_'), ra.roletypeparameter as roleuser, u.user_name as username, ua.attribute_value as last_login, r.name as role_name
from projectrole r
join projectroleactor ra on ra.projectroleid = r.id and ra.roletype = 'atlassian-user-role-actor'
join project p on p.id = ra.pid
join app_user a on a.user_key = ra.roletypeparameter
join cwd_user u on u.lower_user_name = a.lower_user_name and u.active = 1 and u.id = (select u2.id from cwd_user u2 join cwd_directory d2 on d2.id = u2.directory_id and d2.active = 1 where u2.lower_user_name = u.lower_user_name order by d2.directory_position asc limit 1)
left join cwd_user_attributes ua on ua.user_id = u.id and ua.attribute_name = 'login.lastLoginMillis'
union
select replace(p.pname,' ','_') as name, p.pkey, replace(r.name,' ','_'), m.child_name as roleuser, u.user_name as username, ua.attribute_value as last_login, r.name as role_name
from project p
join projectroleactor ra on ra.pid = p.id and ra.roletype = 'atlassian-group-role-actor'
join projectrole r on r.id = ra.projectroleid
join cwd_membership m on m.parent_name = ra.roletypeparameter and m.membership_type='GROUP_USER'
join cwd_user u on u.lower_user_name = m.lower_child_name and u.active = 1 and u.id = (select u2.id from cwd_user u2 join cwd_directory d2 on d2.id = u2.directory_id and d2.active = 1 where u2.lower_user_name = u.lower_user_name order by d2.directory_position asc limit 1)
left join cwd_user_attributes ua on ua.user_id = u.id and ua.attribute_name = 'login.lastLoginMillis'
order by pkey) a;Sample output
1
2
3
4
5
6
7
8
9
10
11
12
name | role_name | username | last_login
------+------------------------+----------------+------------------------
DEV | Administrators | admin | 2021-07-30 15:41:42-03
DEV | Developers | admin | 2021-07-30 15:41:42-03
ITSD | Administrators | admin | 2021-07-30 15:41:42-03
ITSD | Developers | admin | 2021-07-30 15:41:42-03
ITSD | Developers | vwong-sd-demo |
ITSD | Service Desk Customers | agrant-sd-demo |
ITSD | Service Desk Customers | jevans-sd-demo |
ITSD | Service Desk Customers | mdavis-sd-demo |
ITSD | Service Desk Customers | rlee-sd-demo |
ITSD | Service Desk Team | vwong-sd-demo |
Updated on March 11, 2025
Was this helpful?
Still need help?
The Atlassian Community is here for you.