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

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?
 

3 answers

1 accepted

1 vote
Answer accepted

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;
0 votes

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

0 votes
Tarun Sapra Community Champion Aug 30, 2016

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Oct 09, 2018 in Jira Core

How to manage many similar workflows?

I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...

400 views 6 0
Join discussion

Atlassian User Groups

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!

Find my local user group

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

Groups near you