Versions Compared

Old Version 3

changes.mady.by.user Ferry China

Saved on

compared with

New Version 4

changes.mady.by.user Cao Liang

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
-- User login information
selectSELECT u.user_name, u.display_name, u.email_address, fromFROM_unixtimeUNIXTIME(convertCONVERT(ua.attribute_value/1000, unsignedUNSIGNED integerINTEGER),'%T %m/%d/%Y') as lastlogin
from 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

Code Block
-- 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 whereWHERE u.id = m.child_id andAND m.parent_id = g.id andAND u.id = ua.user_id andAND ua.attribute_name = 'login.lastLoginMillis' AND g.group_name likeLIKE 'jira-usersadministrators' orderORDER byBY 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