The Atlassian Community can help you and your team get more value out of Atlassian products and practices.
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 Check it out to find user with last login :-
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;
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.
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
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.
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!
Agree on ScriptRunner
For Ex JQL: assignee in inactiveUsers()
https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html
👋 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...
Connect with like-minded Atlassian users at free events near you!
Find an eventConnect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.
Host an eventYou're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events