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
@Moses Ebrahimi if this was helpful please accept the answer to help others.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.