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.
Full Name:Mike Smith
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.
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.
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';
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