SQL Query to find the projects with inactive leads

Question

Hello,

I need SQL query to find the list of the projects which have inactive project leads in it.

Thanks

Andy Heinzer · Answer

@Morten Stensgaard Not exactly.  This original post was in regards to Jira Server, which implies that the administrators potentially have direct access to the SQL database that Jira uses.

Jira Cloud doesn't provide SQL access for customer, so the answers above don't really apply to Cloud.

Automation might be able to help, but it's not well suited for doing a lookup on project settings.  Instead, you might have more success just having it do a Scheduled trigger that runs a JQL query, such as

assignee in inactiveUsers()

Which should then return to you all the issues in Jira that have an assignee of any issue.  If that's too broad, then perhaps something more like:

assignee in inactiveUsers() and statuscategory!=done

to find assigned issues to inactive users where the issue is not in a done category status.

Morten Stensgaard · Answer

@Andy Heinzer Is there any way to do this with either Groovy or Automation for Jira.....or just plain REST API?

We are spending quite some support time on reviewing for inactive Project Leads on a monthly basis, hence we do not allow Projects without an ownership and have numerous other side effects to it(Dead projects/issues, Cannot delete the user, Archived Projects etc.)

Would really like an Autoamtion rulr or script that just:

* Loop over all Projects in Jira
* Check for Project Leads = Inactive
* Inform Support, that they need to contact Project/Project Admins

An alternative method, is to loop over all inactive users and check if they are Project Leads on any projects.

Andy Heinzer · Answer

You could modify that query to eliminate the projects without a lead and only show you ones where the lead user account is inactive in Jira with something like:

SELECT * FROM project WHERE lead IN (SELECT user_name FROM cwd_user WHERE active = 0);

But I can also see a problem with this query.  It's returning the user_name field form the cwd_user table.  That will probably work for most cases, but it's possible that user accounts in jira could have been renamed at some point.  If that happens, the lead field in the project table won't match the user_name field in the cwd_user table.  Which is why we should look at the app_user table's user_key field to see which account corresponds correctly to that project lead field.

So I think a more accurate SQL query for what you want would be:

SELECT * FROM project WHERE lead IN (
select user_key from app_user au
join cwd_user cu on cu.lower_user_name=au.lower_user_name
where cu.active=0;)