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

[Manohar Venkataraman]

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.

[Manohar Venkataraman]

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.

[Nic Brough -Adaptavist-]

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.

[Manohar Venkataraman]

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.

[Nic Brough -Adaptavist-]

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.