How could I get users not using Jira for a period of time?

[Jose Lezana]

I would like know if there is a way to get all user not using Jira for more than 18 month. Is there any query or methood to get it?

[Jose Lezana · Accepted solution]

Hi Alexey, I´m using below query 

SELECT d.directory_name AS "Directory",
u.user_name AS "USER",
from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) AS "Last Login"
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership m
JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM cwd_user_attributes
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC;

But I´m also getting results for people who has login today, What should I change to get only people not having login for last 18 month?

[Alexey Matveev · Accepted solution]

Hello,

You can get users last login date and select only users, who logged in to Jira for more than 18 months ago.

You could find more info here:

https://confluence.atlassian.com/jirakb/retrieve-last-login-dates-for-users-from-the-database-363364638.html

[Alexey Matveev]

Have a look at the Last Login value type. And add a where clause to your query