get last login date of users in confluence (postgres)

[me]

hi

i want to get last login date of confluence users. try this but it return for some users "last update" or " last failed login date" instead of last login date.

any idea?

thanks

[me · Accepted solution]

WITH last_login_date AS
(SELECT user_id
      , to_timestamp(CAST(cua.attribute_value AS double precision)/1000) AS last_login
   FROM cwd_user_attribute cua
  WHERE cua.attribute_name = 'lastAuthenticated'
    AND to_timestamp(CAST(cua.attribute_value AS double precision)/1000) < (CURRENT_DATE))
SELECT c.user_name
     , li.successdate
     , g.group_name
  FROM cwd_user c
 INNER JOIN last_login_date l ON (c.id = l.user_id)
 INNER JOIN cwd_membership m  ON (c.id = m.child_user_id)
 INNER JOIN cwd_group g       ON (m.parent_id = g.id)
 INNER JOIN user_mapping um ON (c.user_name = um.username)
 INNER JOIN logininfo li ON (um.user_key = li.username)
 WHERE g.group_name LIKE 'CEO-%' ;

[kdickason]

@me  The link you specified above this answer indicates you must specify a timeframe.  How is that done?  Thank you for any help in advance!

[Fadoua]

@me 

Are you using Postgres or SQL?

Because on your title it says Postgres but for the tags you selected SQL

Best,

Fadoua

[me]

Postgres

[me]

@Fadoua any update?

[Fadoua]

@me I am still looking 

[Fadoua]

@me I checked every single document online. The only one I found is the same one you shared. You will need to specify the timeframe interval

https://confluence.atlassian.com/confkb/how-to-get-a-list-of-users-with-their-last-logon-times-985499701.html

Best of luck!

Fadoua