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

[JIRA] How to retrive last login timestamp of a user from database?

Using SQL, I want to retrieve the last logged in time of a set of users. I think last login time is recorded in CWD_USER_ATTRIBUTES table.

When querying this table I found two two attribute names for each user, that looks like last login time; that are 'lastAuthenticated' and 'login.lastLoginMillis'.

SELECT DISTINCT ATTRIBUTE_NAME FROM CWD_USER_ATTRIBUTES ORDER BY 1;
com.atlassian.jira.dvcs.invite.groups
invalidPasswordAttempts
lastAuthenticated
login.count
login.currentFailedCount
login.lastFailedLoginMillis
login.lastLoginMillis
login.previousLoginMillis
login.totalFailedCount
password.reset.request.expiry
password.reset.request.token
passwordLastChanged
requiresPasswordChange

In JIRA, is there a difference between the terms 'Login' and 'Authentication'? For all users, these two attribute values were different.

Which attribute should I take as correct login timestamp of the user?

2 answers

1 accepted

1 vote
Answer accepted

I found it!

Authentication = user enters username/ password and JIRA performs authentication

Login = If user had set 'Remember Me' during authentication, and then closed the browser; when he returns to JIRA after reopening the browser JIRA will automatically log him in. This is last login.

During authentication, last login and last authentication attributes will be updated. But during login, only the last login attribute will be updated.

Azfar Masut Apr 06, 2017

Unfortunately, for JIRA 7 and above, its no longer the case for login.lastLoginMillis now.  When user have 'Remember Me' option enabled for his account, there is not a database field that is updated on access .Only after his token expired(which will trigger the relogin window), the login.lastLoginMillis value will be updated.

https://jira.atlassian.com/browse/JRASERVER-60508

If you are trying to get an active user list for the last few months, you can configure the timeout for the token as described here: 

JIRA application cookies - Atlassian Documentation

You can also disable this feature as describe here:

Prevent automatic login - Atlassian Documentation

 

Hope this helps. Spend alot of time to verify this information.

1 vote
Tiago Comasseto Feb 05, 2014

Hi Sameera, we have a KB article with the query to retrieve this information in this link.

I hope it helps

Cheers

I'm checking the resolution under JIRA 4.3+

For MySQL and Postgres it is 'lastAuthenticated' attribute, but for MSSQL it is 'login.lastLoginMillis' attribute.

That's confusing. I'll post a comment to that article too.

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Jira Software

Early Access: If you use Jenkins and Jira Software Cloud, you need to read this!

The Jira Software Cloud Team has been busy working on a simple, secure, and reliable way to integrate your build and deployment information from Jenkins with Jira Software Cloud. This means you don’t...

260 views 0 8
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