Database query to get all users who haven't logged within a year

[Tayyab Bashir]

Hi,
I'm trying to find all the users who haven't logged within a year.
I need the mySQL query for JIRA to get all such users? 

Does anyone know what the query is?
 

[Tayyab Bashir · Accepted solution]

This worked for me: 

SELECT  u.user_name as Username,
    u.display_name as Fullname,
    from_unixtime(round(a.attribute_value/1000)) as LastLogin
FROM cwd_user u
    LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis'
WHERE from_unixtime(round(a.attribute_value/1000)) <= current_date - interval 365 day OR a.attribute_value IS NULL
ORDER BY a.attribute_value;

[Tarun Sapra]

Here's the query for users not logged in since Jan 2016

SELECT cwd_user.display_name as [Name],cwd_user.user_name as [Username], dateadd(second,cast(cast(cwd_user_attributes.attribute_value as nvarchar(255)) as bigint)/1000,'19700101 00:00:00:000') as [Last Logged in on]
FROM cwd_user, cwd_user_attributes
WHERE cwd_user.id = cwd_user_attributes.user_id AND cwd_user_attributes.attribute_name = 'login.lastLoginMillis'
AND  dateadd(second,cast(cast(cwd_user_attributes.attribute_value as nvarchar(255)) as bigint)/1000,'19700101 00:00:00:000') < '2016-01-01 00:00:00.0'
order by cwd_user_attributes.attribute_value DESC

[Tayyab Bashir]

Which MySQL server is this for? 
I'm using version 5.5 and when I ran this it gave the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Name],cwd_user.user_name as [Username], dateadd(second,cast(cast(cwd_user_attri' at line 1

[Nic Brough -Adaptavist-]

Have a look in the cwd_user table, that records the last login date/time