...
| 코드 블럭 |
|---|
SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
u.display_name AS "Full_Name",
u.lower_email_address AS "Email_Address",
u.active AS "Active",
from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) AS "Last Login"
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership m
JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM cwd_user_attributes
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC; |
유저 마지막로그인 및 active 상태 얻기(그룹추가 버전)
| 코드 블럭 |
|---|
SELECT m.parent_name AS "Group",
u.user_name AS "Username",
u.display_name AS "Full_Name",
u.active AS "Active",
from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) AS "Last Login"
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name, parent_name
FROM cwd_membership m
JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM cwd_user_attributes
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC; |