How can I get a list of Jira users that have not login the last year

[Iro Karavasili]

Hello all,

I need to find a mySQL query in order to get a list of Jira users that have not login the last year. 
Jira version: 7.9.2

Can someone help me with this?

Thanks in advance,
Iro

[Elifcan Cakmak]

Hello,

Here is a sample query for last 90 days. 

https://confluence.atlassian.com/jirakb/jira-accounts-not-used-in-the-last-90-days-695241569.html

You can edit it for the time you like. This one is for mysql. You need to change the interval according to your need.

MySQL

SELECT cwd_user.user_name, from_unixtime(round(cwd_user_attributes.attribute_value/1000))
FROM cwd_user, cwd_user_attributes
WHERE cwd_user_attributes.user_id = cwd_user.id
AND cwd_user_attributes.attribute_name = 'lastAuthenticated'
AND (from_unixtime(round(cwd_user_attributes.attribute_value/1000))) < (current_date - interval 90 day);

Regards,

Elifcan