SELECT
    d.directory_name AS "Directory",
    u.user_name AS "Username",
    u.display_name AS "Display Name",
    u.email_address AS "Email",
    to_timestamp(CAST(ca.attribute_value AS BIGINT)/1000) 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 EXISTS (
        SELECT 1
        FROM cwd_membership m
        JOIN licenserolesgroup gp ON m.lower_parent_name = LOWER(gp.GROUP_ID)
        WHERE m.lower_child_name = u.lower_user_name
    )
    AND (
        ca.attribute_value IS NULL
        OR to_timestamp(CAST(ca.attribute_value AS BIGINT)/1000) <= current_date - INTERVAL '90 days'
    )
ORDER BY "Last Login" DESC NULLS LAST;

SELECT
    d.directory_name AS "Directory",
    u.user_name AS "Username",
    u.display_name AS "Display Name",
    u.email_address AS "Email",
    FROM_UNIXTIME(CAST(ca.attribute_value AS UNSIGNED)/1000) 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 EXISTS (
        SELECT 1
        FROM cwd_membership m
        JOIN licenserolesgroup gp ON LOWER(m.parent_name) = LOWER(gp.GROUP_ID)
        WHERE m.lower_child_name = u.lower_user_name
    )
    AND (
        ca.attribute_value IS NULL
        OR FROM_UNIXTIME(CAST(ca.attribute_value AS UNSIGNED)/1000) <= (CURRENT_DATE - INTERVAL 90 DAY)
    )
ORDER BY "Last Login" IS NULL, "Last Login" DESC;