I think you can combine these two queries, but there are some considerations when doing so. Each of these articles is seeking something slightly different so the SQL select statements are specifically geared for the results in question. That said, I think I have found a way for you to see the last login time of specific users in a licensing role. For Service Desk Agents:
SELECT d.directory_name AS "Directory", u.user_name AS "Username",
to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login", lrg.license_role_name
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership cm
JOIN licenserolesgroup gp ON cm.parent_name = gp.GROUP_ID
) AS cm ON cm.child_name = u.user_name
JOIN (
SELECT *
FROM cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN licenserolesgroup lrg
ON Lower(m.parent_name) = Lower(lrg.group_id)
WHERE d.active = '1'
AND u.active = '1'
AND license_role_name = 'jira-servicedesk'
ORDER BY "Last Login" DESC;
And for Jira Software users:
SELECT d.directory_name AS "Directory", u.user_name AS "Username",
to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login", lrg.license_role_name
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership cm
JOIN licenserolesgroup gp ON cm.parent_name = gp.GROUP_ID
) AS cm ON cm.child_name = u.user_name
JOIN (
SELECT *
FROM cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN licenserolesgroup lrg
ON Lower(m.parent_name) = Lower(lrg.group_id)
WHERE d.active = '1'
AND u.active = '1'
AND license_role_name = 'jira-software'
ORDER BY "Last Login" DESC;
Neither of these queries should return inactive users
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.