JIRA is it possible to get user login history?

[Tam Nguyen]

Hi geeks,

In JIRA we can get login count and last login information but is there any way to retrieve login history for a particular user ?

Cheers,

Tam

[tier-0 grump · Accepted solution]

Not through the UI, but you do have a log file atlassian-jira-security.log that logs this information, along the form:

2011-12-16 13:10:41,450 http-2990-5 anonymous 790x245x1 1pj2kdu 127.0.0.1 /rest/gadget/1.0/login HttpSession created [1pj2kdu]

2011-12-16 13:10:41,469 http-2990-5 admin 790x245x1 1pj2kdu 127.0.0.1 /rest/gadget/1.0/login The user 'admin' has PASSED authentication.

[Tam Nguyen]

Thank you very much James!

[Tam Nguyen]

Thank you very much James,

I'm still wondering does Jira record login event into it's database? if so we can get this data by selecting directly from the database. But i guess it is not a good idea, just want to confirm.

[tier-0 grump]

We don't record it into the db I'm afraid

[Renjith Pillai]

The user browser (in administration) shows the count as well as the last login time, from where does it pull out the information from?

[tier-0 grump]

We store attributes with the user object - the following attributes are stored

"lastAuthenticated"
"login.currentFailedCount"
"login.lastLoginMillis"
"login.previousLoginMillis"
"login.count"

This is dynamically updated with each login, so is not much use for history.

[muqsithirfan]

Hi James,

Thanks for the above information, but how do I read these values ?

Regards,

Muqsith

[Tam Nguyen]

you can see them through GUI (User Brower) or directly query Jira database. Find some useful queries from here

[srinivasp]

Hi James,

Is it possible to get the details using SOAP api?

Thanks,

Srinivas

[srinivasp]

Hi James,

Is it possible to get the details using api?

Thanks,

Srinivas

[Tam Nguyen]

i'm not James but here is the answer for your question :) , use com.atlassian.jira.bc.security.login.LoginInfo to get what you want, it's implementation is LoginInfoImpl

Enjoy,

[anujjha_kpmg]

Hi,

If you are using Jira 7.0.* and SQL server as your database, you can use the below SQL query to get the user's directory, user name, user's last login, his/her previous login and number of login attempts information in a table.

SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
DATEADD(second, cast(a.attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Last Login",
DATEADD(second, cast(b.attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Previous login",
c.attribute_value AS "Login_Counts"
FROM [JIRASchema].cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM [JIRASchema].cwd_membership m
JOIN [JIRASchema].licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM [JIRASchema].cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.ID
JOIN (
SELECT *
FROM [JIRASchema].cwd_user_attributes cb
WHERE attribute_name = 'login.previousLoginMillis'
) AS b ON b.user_id = u.ID
JOIN (
SELECT *
FROM [JIRASchema].cwd_user_attributes cc
WHERE attribute_name = 'login.count'
) AS c ON c.user_id = u.ID
JOIN [JIRASchema].cwd_directory d ON u.directory_id = d.ID

Order by cast(c.attribute_value as INT) desc

Thanks,

Anuj

[Krzysztof Skoropada [Deviniti]]

@James Winters [Atlassian]

HI, is there any way that those attributes was deleted during migration to higher JIRA or some operations on directory in JIRA? Users exists in JIRA but password checking is made by ActiveDirectory.

We have users without lastLoginMillis but they exists as an author in some changegroups.

[Dieter]

You could also implement an eventListener that listens on login events. Please check this http://docs.atlassian.com/software/jira/docs/api/latest/com/atlassian/jira/event/user/LoginEvent.html The listener would have to record the events in database table.