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
If you spend enough time as a Jira admin - whether you are managing a single, mid-sized instance, a large enterprise one or juggling multiple instances at once - you will eventually find yourself in ...
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
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot