Postgresql query to get list of all confluence-users with their last logon time

[Moses Ebrahimi]

Hi all,

Well need to pull list of users within confluence-group from confluence database (PostgreSQL) and using the below query with some minor tweaks

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
     , c.lower_user_name
     , c.email_address
     , c.display_name
     , c.last_name 
     , g.group_name
     , l.last_login
  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)
 WHERE g.group_name = 'confluence-users'
 ORDER BY last_login DESC;

but for some reason it only returns 4150 users with the last login time and date.

Then made a little change to line 15 where is says INNER JOIN last_login_date

full outer JOIN last_login_date l ON (c.id = l.user_id)
then it returns all users within the "confluence-group" ~12000 users.

but the other ~7850 users last login date is showing NULL but when I check a couple of them as an example they had last login date in UI.

So I dont know if the "last_login_date" fetch from different tables? or how does it work? and more importantly I need to get the query to return all ~12000 users with their last login date.

Appreciate it if someone could point me to the right direction please.

Thank you

Moses

[Brant Schroeder · Accepted solution]

@Moses Ebrahimi Did you see this https://confluence.atlassian.com/jirakb/find-the-last-login-date-for-a-user-in-jira-server-363364638.html 

[Brant Schroeder]

@Moses Ebrahimi if this was helpful please accept the answer to help others.