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?
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.
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.
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:
You can also disable this feature as describe here:
Hope this helps. Spend alot of time to verify this information.
Hey admins! I’m Dave, Principal Product Manager here at Atlassian working on our cloud platform and security products. Cloud security is a moving target. As you adopt more products, employees consta...
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!
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