Hi,
I'm trying to fetch active licensed users details from Confluence using Postgres DB query. I'm trying to execute below query which gives me exact license user details but I'm missing last login information.
SELECT u.lower_user_name, u.email_address, d.directory_name
FROM cwd_user u
JOIN cwd_membership m ON u.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
JOIN cwd_directory d on u.directory_id = d.id
WHERE sp.PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T'
GROUP BY u.lower_user_name, u.email_address, d.directory_name
ORDER BY d.directory_name;
In order to get last login information I've edited the query to map to logininfo table and the updated query is below. But what I noticed is there is a difference of user data in the results of both queries.
SELECT u.lower_user_name, u.email_address, d.directory_name, li.successdate
FROM cwd_user u
JOIN cwd_membership m ON u.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
JOIN cwd_directory d on u.directory_id = d.id
JOIN user_mapping um ON u.user_name = um.username
JOIN logininfo li ON um.user_key = li.username
WHERE sp.PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T';
Can you please help me to get the exact details with last login information. Thank you in advance.
I think this KB might be something that you are looking for
https://support.atlassian.com/confluence/kb/user-base-audit-queries/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.