Jira SQL query for inactive users

Chris Shepherd

I am trying to find a way of querying the Jira database to give me a list of users that are:

• In a specific user directory
• Have not logged in for 2 years
• Have not created an issue in 2 years
• have not commented on an issue for 2 years

 

Does anyone know how to get that?

 

Nic Brough -Adaptavist-

You'll need to plough through a lot of tables, but

• cwd_user gives you a directory
• cwd_user_attributes holds the last login
• jiraissue can give you the last created issue
• jiraaction has the comments

But you've missed "edit", and Community is a good example.  I don't think I've asked a question (i.e. "create issue") in over a year.  But I've sure as heck interacted with them (Edit, transition, label, comment, share and and and...). 

Changegroup can help you with updates after creation of the issue.