How do I determine actual Jira usage by users in order to delete inactive ones?

As Atlassian is moving from an unlimited licensing model to a usage based one we need to remove all users who are not using the system (initially I was asked to add about 1,500 users). We would like the following information in order to do this:

  • Determine the number of unique users who have accessed Jira Production this calendar year since 1/1/14. Additional helpful info:
    • The number of times they accessed the system
    • The last time they accessed the system
    • We can try to cross reference this info with Support to find out if any users have left the company and thus remove them
  • Determine the number of users in the system who have not accessed the system since 1/1/14. This will provide us with a user list that we can verify with our users and then remove for licensing purposes'

There is some information on the internet but none of it is clear enough to provide to our separate database team regarding queries (tables/fields etc.). I also want to be clear that we are not using 'inactive' in the way that Atlassian does. For us, we would define inactive as any user who has not accessed the system since 1/1/14. The knowledge base article on 'How to get a list of active users' doesn't help here because they define active user as one who is set up, not one who has actually used Jira.

4 answers

0 vote

They moved to a user-based model years ago.

The simple answer here is to look at the last-login date/time, it's supplied in the user maintenance screens.

Also, you could struggle to delete them - Jira won't let you remove users who have content (it destroys the audit data and breaks all sorts of things). You should be making these users inactive, then they won't count towards your licence count.

Thanks Nic. Regarding your comments:

  • I was asked to bulk add the 1,500 users via jelly scripts some time early last year. On hindsight I could have argued against it, but at the time you do what your boss says.
  • We are using Siteminder, so the last login date/time doesn't appear on the admin screen. Besides, even if it did trying to look up that information for 1,500 users isn't particular efficient. I was hoping someone would know where that information was stored within the database, because the 'User Sessions' is still accurate as is the information within the access logs. I'm trying to find out an efficient way to extract actual user usage information to proceed to the next step
  • My guess is that several hundred of these users have never accessed Jira and should be easy to delete once I know who they are. For those who have touched any content but haven't accessed the system this year I can make inactive, but I suspect that's a vastly smaller population.
0 vote

Completely understand, if the boss says do it, you do. I keep all the emails where I advise against things, but yep, they pay the bills. :-)

Siteminder. Hmm. Well, you can try the cwd attributes where it's normally stored, but if it's not appearing in user maintenance, it might not even be stored and you'd need to go back to Siteminder.

Try:

select cwd_user.user_name, from_unixtime(round(cwd_user_attributes.attribute_value/1000)) from cwd_user, cwd_user_attributes WHERE cwd_user_attributes.user_id = cwd_user.id AND cwd_user_attributes.attribute_name = 'lastAuthenticated';

(That's MySQL from the last time I dug through this sort of thing - might need adjusting for other databases, but it's not far off)

Also, to quickly identify users you cannot delete, then check the table jiraissue for Reporter and Assignee, and Jiraaction for Author of comments - if a user isn't named in any of those three, Jira will let you delete them becase they've not really interacted with it at all.

Thanks for the update Nic. I've passed the info along to our database team who hopefully can extract the info we need. Will update the question based on their findings. I also submitted a support ticket so hopefully will be able to summarize a solution here for others.

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Sunday in Agility

You asked for it, so we delivered: images on issues have arrived

A picture tells a thousand words. And agility boards have just released their latest feature: cover images on issues – so now your board can tell a story at first glance. Upload attachmen...

991 views 3 12
Read article

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