I want to identify which users have not logged in past 12 months .
Which database table stores the last logged in date/time information?
I am on jira 6.1
Rahul
Hi,
Here is the JIRA KB that you help you to do it.
https://confluence.atlassian.com/display/JIRAKB/Retrieve+last+login+dates+for+users+from+the+database
Vijay
thanks Vijay that helps
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Rahul
I've been trying this locally, and the following query should return you a list of users who have either not logged into JIRA at all, or have not logged in after Jan 1st:
SELECT u.user_name as "Username", u.display_name as "Full Name", to_timestamp(CAST(a.attribute_value as bigint)/1000) as "Last Login" FROM cwd_user u LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis' WHERE a.attribute_value >= '1388541600000' OR a.attribute_value IS NULL ORDER BY a.attribute_value;
<sup>I've created this using PostgreSQL, so it may need changes if using different DBMS.</sup>
If you want to change the date, simply change the value in the WHERE clause to a different date (in milliseconds). The following website come in handy to convert the date to milliseconds: http://www.ruddwire.com/handy-code/date-to-millisecond-calculators/.
I hope this helps! :)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
For reference, the MYSQL query looks like this:
SELECT u.user_name as 'Username',
u.display_name as 'Full Name',
from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) as 'Last Login'
FROM cwd_user u
LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis'
WHERE a.attribute_value >= '1388541600000' OR a.attribute_value IS NULL
ORDER BY a.attribute_value;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.