Find idle/inactive users in jira 4.1.2

Alok Agnihotri

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!

João Palharini

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.

 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