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

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

Regards,

Elifcan