Find idle/inactive users in jira 4.1.2

Hi,

We need to find the number of users in JIRA 4.1.2 who are not connected to any issue and only belong to group "jira-users". Later we will delete these users to reduce the number of users.

Please suggest a efficient method to achieve this.

Thank You!

2 answers

1 accepted

0 vote

Hey Alok!

I came up with some queries for the JIRA database that can help you on that. See below:

 

SELECT
	m.user_name AS "Username",
 	(count(distinct c.id) + count(distinct a.sink_node_id) + count(distinct j.id) + count(distinct w.id)) AS "Activity Count"
FROM
	membershipbase m
 	FULL OUTER JOIN changegroup c ON c.author = m.user_name
   	FULL OUTER JOIN worklog w ON w.author = m.user_name
   	FULL OUTER JOIN userassociation a ON a.source_name = m.user_name
	FULL OUTER JOIN jiraaction j ON j.author = m.user_name
WHERE
	m.group_name IN ('jira-users')
GROUP BY m.user_name
ORDER BY 2 DESC

Considers how many of the following activities the user has performed:

  • Changes to issues (edits to any field and workflow transitions) > changegroup
  • Work logs > worklog
  • Votes and watches > userassociation
  • Comments > jiraaction

 

SELECT
	m.user_name AS "Username",
	count(i.id) AS "Issues Assigned"
FROM 
	membershipbase m
	JOIN jiraissue i ON i.assignee = m.user_name
WHERE
	m.group_name IN ('jira-users')
GROUP BY m.user_name
ORDER BY 2 DESC;

Counts how many issues were assigned to that user.

warning Be aware that those queries were designed for a PostgreSQL database and may need syntax changes in order to be used on other database types.

Cheers!

Joao

Hi Joao, Thank you so much for the response....It's quite helpful. One more query, We have logon info(login records), which is displayed in user browser(Jira 4.1.2) that can be used to estimate user's involvement? So can you suggest where to look for this login records in DB if they are not encrypted. I have seen many users with no login records on user browser. Appreciate your help!

0 vote
Chris Fuller Atlassian Team Feb 10, 2015

"not connected to any issue" That's a very nebulous requirement. 4.1.2 is before my time, and it also seems to be before change history was recorded, so this makes things a little bit simpler. However, it did already have user custom fields. Do those count? Do comments? Do worklogs? Do mentions?

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Posted Wednesday in Jira

Join our webinar: How 1B+ feature flag events helped us build the new Jira

Every time you release software, there's a bit of risk – that there's a bug, that something breaks, or that the feature doesn't resonate with customers. Feature flagging helps make high stakes s...

101 views 0 1
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you