Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,293,761
Community Members
 
Community Events
165
Community Groups

Get the list of users with no activity in Jira

Sagar Mahajan Community Leader 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.

2 comments

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!

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

Like # people like this
Sagar Mahajan Community Leader 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.

Like # people like this

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

Like Abhijeet Kale likes this
Sagar Mahajan Community Leader 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.

Like # people like this

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!

Like Ravina Mahajan likes this

Comment

Log in or Sign up to comment
TAGS
Community showcase
Published in Jira Software

Upcoming changes to epic fields in company-managed projects

👋 Hi there Jira Community! A few months ago we shared with you plans around renaming epics in your company-managed projects. As part of these changes, we highlighted upcoming changes to epics on...

14,123 views 34 44
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you