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.
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:
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.
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!
"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?
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot