Query to find all the inactive project admins in jira.

Question

Query to find inactive admins of all the projects in jira.

Robin Powell_pjvgk1 · Answer

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;

Hemanth Kumar_gphrv1 · Answer

#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);

Bhargav Tedla · Answer

@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