Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

Need help on fetching active licensed users from Confluence.

dhanusha maddukuru
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
April 30, 2025

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.

 

 

1 answer

0 votes
Mirek
Community Champion
April 30, 2025

Hi @dhanusha maddukuru 

I think this KB might be something that you are looking for 

https://support.atlassian.com/confluence/kb/user-base-audit-queries/

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events