Managing inactive user accounts

Looking for an interactive way to establish if accounts are just dormant or actually RIP.

Ideally (a plugin?!) would email the inactive accounts, reminding them to login, if no response in #days then the account is marked inactive.

Have a situation that external users have been registered as local JIRA accounts with no consideration for compliance, leavers/joiners.  Trying to see if theres a way to manage that.

As I write & read this back I'm pretty sure I know the answer but putting it out there anyway.

3 answers

1 votes

Hi Paul,

I'm not sure there's any plugin to do this, but you could get the list of users that haven't logged into JIRA for the last 6 months (or at all) with the following query.

SELECT  u.user_name as "Username", 
	u.display_name as "Full Name", 
	to_timestamp(CAST(a.attribute_value as bigint)/1000) as "Last Login" 
FROM cwd_user u
	LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis' 
WHERE to_timestamp(CAST(a.attribute_value as bigint)/1000) <= current_date - 180 OR a.attribute_value IS NULL
ORDER BY a.attribute_value;

Hope this helps.

0 votes
Pedro Cora Atlassian Team May 29, 2015

A 'hack' that you can do is run this query:

SELECT u.user_name,u.first_name,u.last_name,u.email_address,d.directory_name from cwd_user u join cwd_directory d on u.directory_id = d.id where u.active = 0;

That will return all inactive users and e-mail them.

Another option is to check for their last login and choose a cut period. To retrieve that, follow this KB: https://confluence.atlassian.com/display/JIRAKB/Retrieve+last+login+dates+for+users+from+the+database

wink

0 votes
Andy Brook Community Champion May 29, 2015

I have two addons, one for JIRA, one for Confluence, to manage user auto inactivation and group manipulation, I should release them!  They can be driven from several sources, eg LDAP, where users are moved to a nominated OU typically for 'not deleted just gone for now' users, it does things like email, hipchat notify on termination.  Its designed to automate user management.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,760 views 11 18
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot