Query to find inactive admins of all the projects in jira.
I know this is a really old thread, but I just figured this out so I thought I'd share in case someone else was looking. This isn't pretty, but it works on Postgres.
WITH archived_projects AS (
SELECT entity_id FROM propertyentry
WHERE entity_name = 'Project' AND property_key LIKE 'jira.archiving%'
)
SELECT p.id AS project_id, p.pkey AS project_key, p.pname AS project_name,
CASE WHEN p.id IN (SELECT entity_id FROM archived_projects)
THEN 'ARCHIVED' ELSE 'ACTIVE' END AS project_state,
au.lower_user_name AS account_name, cu.display_name AS display_name,
cu.email_address AS email_address
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 au ON au.user_key = ra.roletypeparameter
JOIN cwd_user cu ON cu.lower_user_name = au.lower_user_name
WHERE r.name = 'Administrators' AND cu.active = 0
ORDER BY p.pkey;
#Project where project lead is not assigned
SELECT * FROM project WHERE lead NOT IN (SELECT user_name FROM cwd_user WHERE active = 1);
#Project where project lead is inactive
SELECT * FROM project WHERE lead IN (SELECT user_name FROM cwd_user WHERE active = 0);
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Bhargav Tedla and welcome to our Community!
As an option you can try the following marketplace app:
Manage Inactive Users For Jira
I haven't tried it myself, but it seems promising.
Hope it helps.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Grigory Salnikov Thanks for your response. But it would be helpful if there is an SQL query for this. Currently am using the below query for getting project admins from all projects. i need to get only the admins who are inactive
select prjra.roletypeparameter PROJADMINS, prj.pname PROJNAME, prj.pkey PROJKEY from PROJECTROLEACTOR prjra, PROJECT prj, PROJECTROLE prjr where prjra.pid = prj.id and prjra.projectroleid = prjr.id and prjr.name = 'Administrators' order by prj.pname
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.