Hi there!
I'm trying to identify Jira users who haven't logged in for a number of days. Therefore I use https://confluence.atlassian.com/jirakb/identify-users-in-jira-who-haven-t-logged-in-for-the-past-90-days-695241569.html to query our Oracle DB:
====
SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
to_date('01-JAN-1970', 'DD-MM-YYYY') + ( 1 / 24 / 60 / 60) * ca.attribute_value AS "Last Login"
FROM cwd_user u
JOIN cwd_directory d ON u.directory_id = d.id
LEFT JOIN cwd_user_attributes ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
WHERE u.active = 1
AND d.active = 1
AND u.lower_user_name IN (
SELECT DISTINCT lower_child_name
FROM cwd_membership m
JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID)
AND (u.id IN (
SELECT ca.user_id
FROM cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
AND to_date('01-JAN-1970', 'DD/MM/YYYY') + ( 1 / 24 / 60 / 60) * ca.attribute_value
<= to_date(SYSDATE - 90, 'DD/MM/YYYY')
OR u.id NOT IN (
SELECT ca.user_id
FROM cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'))
)
ORDER BY "Last Login" DESC;
===
(I need this information outside of Jira, so this seems a proper way. For Confluence (https://confluence.atlassian.com/confkb/how-to-identify-inactive-users-in-confluence-214335880.html) this way works just fine!)
But the information I get is not accurate!
I only get SOME of the users who never logged in! To compare the result I use <JIRA instance>/secure/admin/user/UserBrowser.jspa
Can anybody help me with this?
Thanks a lot!
