Get the list of users with no activity in Jira

Sagar
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 12, 2021

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.

7 answers

3 votes
Sagar
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 13, 2021

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.

2 votes
Sagar
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 14, 2021

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.

2 votes
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 13, 2021

@Sagar  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;

1 vote
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 14, 2021

Hi @Sagar  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!

1 vote
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 14, 2021

Hi @Sagar 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

0 votes
John Dunkelberg May 13, 2021

Agree on ScriptRunner

For Ex JQL: assignee in inactiveUsers()

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

0 votes
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 12, 2021

Hi @Sagar 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!

Suggest an answer

Log in or Sign up to answer