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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Have a look in the cwd_user table, that records the last login date/time
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.