Get the list of users with no activity in Jira

[Sagar Mahajan]

Hi All,

We have a use case where we want to identify the users which don't have any activity in the Jira.

We want these accounts to be deleted from Jira as we are working the enterprise authentication, currently we have 3-4 SSO management tools with different usernames and email address so to consolidate them we want to identify the user accounts which does not have any data, activity associated with them so that we can delete those accounts and that will be available for the new users in the future.

for single user we can get that by checking users profiles.

https://<Jira-base-url>/secure/ViewProfile.jspa?name=username

But we want to check this for all users who does not have any data/activity in Jira.

I found the below document to check this from database but it's for only single user, we want this for all the users.

https://confluence.atlassian.com/jirakb/user-activity-sql-query-389780940.html

Does anyone know how get that or how you handle inactive users in your organization.

Let me know your thoughts on this.

[Sagar Mahajan]

HI @Vikrant Yadav Thanks for the database query but here I am looking for the users with no activity in Jira,

From the last login we can able to find the users which are not using Jira from last 1-2 year etc, but still they have some jira issues assigned, reported, comments associated with them and we do not want to delete such users.

We want users with no activity in Jira so that they don't I have objects associated with them and we can delete them without any issues.

[Sagar Mahajan]

Hi @Vikrant Yadav Yes, I am looking for users with no activity in Jira, last login does not confirm if that user as any activity associated with it or not.

I am looking for the database query to find out the details.

[Vikrant Yadav]

@Sagar Mahajan  Check it out to find user with last login :- 

https://confluence.atlassian.com/jirakb/find-the-last-login-date-for-a-user-in-jira-server-363364638.html

SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Last Login"
FROM dbo.cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM dbo.cwd_membership m
JOIN dbo.licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM dbo.cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.ID
JOIN dbo.cwd_directory d ON u.directory_id = d.ID
ORDER BY "Last Login" DESC;

[Vikrant Yadav]

Hi @Sagar Mahajan  Below query give you complete list users along with Login Count, if login count is zero it means users never logged in.

SELECT
u.user_name AS [USER NAME]
,m.parent_name AS [GROUP]
,ua.attribute_value AS [LOGIN COUNT]
,u.updated_date AS [LAST LOGIN]
,u.created_date AS [CREATED]
FROM cwd_user AS u LEFT JOIN
cwd_user_attributes AS ua ON u.ID = ua.user_id LEFT JOIN
cwd_membership AS m ON u.ID = m.child_id AND u.directory_id = m.directory_id LEFT JOIN
cwd_directory AS d ON m.directory_id = d.ID
WHERE (d.active = '1') AND (u.active = '1') AND (m.parent_name <> 'non-jira-users') AND (ua.attribute_name = 'login.count')

Thanks!

[Vikrant Yadav]

Hi @Sagar Mahajan For No activity means those users which never logged in right ? 

 User which not loggind into JIRA from last 1 -2 years, you can't delete those users you can only make them inactive. JIRA won't allow you to delete those users, if users is assginee, reporter , componet Lead or Project Lead.  For Clean up purpose we usually make users inactive whose last login is older then 90 days. 

 If no activity users, you can check JIRA Database schema and run SQL query.   

Thanks

[John Dunkelberg]

Agree on ScriptRunner

For Ex JQL: assignee in inactiveUsers()

https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html

[Vikrant Yadav]

Hi @Sagar Mahajan If you have Script Runner plugin , you can do it with the help of Script Console. Get complete user details along with last logins. 

Thanks!