nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

User Activity/Usage/Login Report

Sergeo Khomutet1

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

Accepted answers

Drew7

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.

All comments

Drew7

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 Khomutet1

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.

Drew7

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

Sergeo Khomutet1

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

Drew7

Please mark this answered, thanks.

Mario Prada Arr

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';

Donal O'Callagh

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