/
Finding User Login Information
Finding User Login Information
MySQL
-- User login information SELECT u.user_name, u.display_name, u.email_address, FROM_UNIXTIME(CONVERT(ua.attribute_value/1000, UNSIGNED INTEGER),'%T %m/%d/%Y') AS lastlogin, g.group_name FROM cwd_user u, cwd_group g, cwd_membership m, cwd_user_attributes ua WHERE u.id = m.child_id AND m.parent_id = g.id AND u.id = ua.user_id AND ua.attribute_name = 'login.lastLoginMillis' AND g.group_name LIKE 'jira-administrators' ORDER BY ua.attribute_value;
PostgreSQL
-- User login information SELECT u.user_name, u.display_name, u.email_address, TO_TIMESTAMP(ua.attribute_value::BIGINT/1000) AS lastlogin, g.group_name FROM cwd_user u, cwd_group g, cwd_membership m, cwd_user_attributes ua WHERE u.id = m.child_id AND m.parent_id = g.id AND u.id = ua.user_id AND ua.attribute_name = 'login.lastLoginMillis' AND g.group_name LIKE 'jira-administrators' ORDER BY ua.attribute_value;
Sample Result
user_name | display_name | email_address | lastlogin | group_name |
---|---|---|---|---|
jason | Jason | jason@akeles.com | 2020-05-18 09:06:05+08 | jira-administrators |
kate | Kate | kate@akeles.com | 2020-08-11 14:36:36+08 | jira-administrators |
emma | Emma | emma@akeles.com | 2020-10-08 09:16:21+08 | jira-administrators |