Last Login Date SQL Query

Jeffrey Howard_sz9lqa

I'm running the following Query to find users who have not logged in Jira within the last 90 days.  If I change it to 91 days the number shrinks.  Shouldn't it increase?

SELECT u.user_name as "Username",
u.display_name as "Full Name",
to_timestamp(CAST(a.attribute_value as bigint)/1000) as "Last Login"
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
AND m.lower_parent_name in ('jira.global.agents','jira.global.users','jira.global.admins','jira-software-users','jira-servicedesk-users')
JOIN cwd_directory d
ON m.directory_id = d.id
LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis'
WHERE to_timestamp(CAST(a.attribute_value as bigint)/1000) <= current_date - 90
ORDER BY a.attribute_value;

Marcin Krasicki_bsi1pv

Your query is using a LEFT JOIN on cwd_user_attributes, which means that it will return all users, even if they don’t have a login.lastLoginMillis attribute. If a user doesn’t have this attribute, it means they’ve never logged in, and they should be included when you’re looking for users who haven’t logged in for at least 90 or 91 days. However, because you’re using a.attribute_value in your WHERE clause and in your ORDER BY clause, these users with null lastLoginMillis values might be getting excluded from the results.

 

NOT TESTED

SELECT u.user_name as "Username",
u.display_name as "Full Name",
CASE
WHEN a.attribute_value IS NULL THEN 'Never logged in'
ELSE to_timestamp(CAST(a.attribute_value as bigint)/1000)
END as "Last Login"
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
AND m.lower_parent_name in ('jira.global.agents','jira.global.users','jira.global.admins','jira-software-users','jira-servicedesk-users')
JOIN cwd_directory d
ON m.directory_id = d.id
LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis'
WHERE a.attribute_value IS NULL OR to_timestamp(CAST(a.attribute_value as bigint)/1000) <= current_date - 90
ORDER BY a.attribute_value;

 

I’ve added a CASE statement in the SELECT clause that checks if a.attribute_value is null. If it is, it returns ‘Never logged in’. Otherwise, it converts a.attribute_value to a timestamp as before.

In the WHERE clause, I’ve added a.attribute_value IS NULL OR before the existing condition. This means the query will now return users who have never logged in (where a.attribute_value is null) or who haven’t logged in for at least 90 days