Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Get the list of users with no activity in Jira

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

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

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

⏰ Day in the life of a Jira Admin!

Hello Community! We thoroughly enjoyed this just-for-fun conversation in the Jira Admin Group about what it's like to be a Jira Admin. For #JiraJuly, our talented designers created these graphics t...

825 views 2 22
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