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?
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
Badges are a great way to show off community activity, whether you’re a newbie or a Champion.Learn more
Every time you release software, there's a bit of risk – that there's a bug, that something breaks, or that the feature doesn't resonate with customers. Feature flagging helps make high stakes s...
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