I want to identify which users have not logged in past 12 months (JIRA)

[Rahul Aich [Nagra]]

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

[Vijay Khacharia · Accepted solution]

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

[Rahul Aich [Nagra]]

thanks Vijay that helps

[Matheus Fernandes]

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;

^{I've created this using PostgreSQL, so it may need changes if using different DBMS.}

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! :)

[Rudy Holtkamp]

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;