User Activity/Usage/Login Report

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

4 answers

1 accepted

Accepted Answer
1 vote

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.

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.

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

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

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

Please mark this answered, thanks.

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Featured Groups

Tuesday tips & tricks: What is the Atlassian Community?

It's officially Tuesday, which means it's officially time for another tip to help you better navigate this space we call the Atlassian Community. 😄 I got a great question from community member, Sa...

100 views 3 7
View post

Atlassian User Groups

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!

Find my local user group

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

Groups near you