We have lot of users who are not logging since 6 months or 1 year, need to identify them as a part of cleaning activity, but i found below query from atlassian community which is giving invalid data while checking in front end. So let us know any query to find valid data for never logged in or not logged in since 1 year .
Query:
SELECT d.directory_name,
u.user_name,
TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM JIRA_HA.cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM JIRA_HA.cwd_membership m
JOIN JIRA_HA.licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
) m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM JIRA_HA.cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) a ON a.user_id = u.ID
JOIN JIRA_HA.cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc;
-------------------------
