Query to find all the inactive project admins in jira.

Bhargav Tedla
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
February 20, 2019

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

3 answers

0 votes
Robin Powell December 10, 2024

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;

 

0 votes
Hemanth Kumar March 22, 2023

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

0 votes
Grigory Salnikov
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 20, 2019

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.

Bhargav Tedla
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
February 21, 2019

@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

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events