User Activity/Usage/Login Report

Sergeo Khomutetsky August 30, 2012

Hello,

We are trying to gather statistics/information on our JIRA users, and I haven't found the solution we need so I thought I'd ask to see what others might be doing. We are on JIRA 4.4.

I know that I can get the following information from the JIRA interface, but it is unreasonable to manually gather the data, and it doesn't provide all of the information we want.

Username: MS123
Full Name:Mike Smith
Email:
Mike@email.com
Login Count:3
Last Login:01/Aug/12 2:24 AM
Previous Login:01/Aug/12 2:19 AM
Last Failed Login:Not recorded
Current Failed Login Count:0
Total Failed Login Count:Not recorded

I see that there is a Activity Stream Report, but that is not what we need and it is unsupported by the vendor. I see several gadgets available related to issue types and statuses, but that is not exactly what we want either. Some of the information we want to report on are number of logins for a specific date range or for the past x number of days, when the user was added to the system, and only some of the information is provided by the JIRA interface.

I read somewhere else that querying the database may provide us that information, but we want to avoid that if possible. I also read somewhere that user information is not stored in the DB - rather, attributes are stored with the user object, and the attributes are dynamically updated with each login. Not sure if that is true or not. If the information is stored in the database and you have written a query, please share. Also, where is that information stored?

Thanks in advance for any information or tips you can provide.

Regards,

Sergeo

5 answers

1 accepted

1 vote
Answer accepted
Drew September 14, 2012

The DB might be the easiest way. Users are stored in the 'crowd' DB in jira if you use the standard internal directory. A couple example queries are listed on the schema page for crowd.

To get the last login date for your users you could do something like this:

SELECT cwd_user.user_name, from_unixtime(cwd_user_attribute.attribute_value/1000) FROM cwd_user, cwd_user_attribute WHERE cwd_user_attribute.user_id = cwd_user.id AND cwd_user_attribute.attribute_name = 'lastAuthenticated';

To get the last login date along with all users other information you could just change, 'cwd_user.user_name' to '*' above.

Sergeo Khomutetsky September 19, 2012

Hi, thanks for the response! We do not currently have Crowd installed. Do you know of another way? Or, is there a way to find this information without Crowd? Is this information stored in JIRA out-of-the-box? Thanks in advance.

Donal O'Callaghan June 18, 2013
Hi Drew, is it possible to create a confluence macro based on this SQL Query so that the last login date of the Jira users can be displayed within a confluence page? regards
0 votes
Mario Prada Arroyo May 22, 2013

If it helps, this is the query for MS SQLServer:

SELECT a.user_name, DATEADD(s, CONVERT(int,SUBSTRING ( b.attribute_value,1,10)), '1970-01-01 00:00:00')
FROM jiraschema.cwd_user a, jiraschema.cwd_user_attributes b 
WHERE b.user_id = a.id AND b.attribute_name = 'lastAuthenticated';

Michael Trostle May 13, 2020

This is an updated query for MS SQLServer

SELECT a.user_name, DATEADD(s, CONVERT(int,SUBSTRING (b.attribute_value,1,10)), '1970-01-01 00:00:00')

FROM cwd_user a, cwd_user_attributes b

WHERE b.user_id = a.id AND b.attribute_name = 'lastAuthenticated';

0 votes
Drew September 19, 2012

Please mark this answered, thanks.

0 votes
Drew September 19, 2012

It is in your jira db. Those cwd_ tables are located in there.

Sergeo Khomutetsky September 19, 2012

Thanks Drew, I see them now. I appreciate your response and this will work!!

Suggest an answer

Log in or Sign up to answer