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;
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
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
I'm John Allspaw, co-founder of Adaptive Capacity Labs, where we help teams use their incidents to learn and improve. We bring research-driven methods and approaches to drive effective inciden...
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
You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs