How to list users which are part of confluence-users group and an other group

Question

Dear all,

I could not find out the proper to do it hpe anyone can point out to correct querry.

I have a need to list all users from confluence-users goup which belongs also to an other group A

Any idea of the correct SQL querry ?

regards

Raghavendra Reddy · Answer

Hi @serge calderara ,

We use the below query in oracle DB to get the data.

SELECT cu.DISPLAY_NAME, cu.USER_NAME, cu.EMAIL_ADDRESS, g.group_name
FROM cwd_user cu
JOIN cwd_membership m ON cu.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN cwd_directory d on cu.directory_id = d.id
WHERE g.group_name = 'confluence-users'

if you want to get the active users you can use the below one

SELECT cu.DISPLAY_NAME, cu.USER_NAME, cu.EMAIL_ADDRESS, g.group_name
FROM cwd_user cu
JOIN cwd_membership m ON cu.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN cwd_directory d on cu.directory_id = d.id
WHERE g.group_name = 'confluence-users' AND cu.active = 'T'

Sushant Verma · Answer

Hi @serge calderara

Welcome to the community!

To achieve this, you can use a query that joins the relevant tables in your Confluence database. Assuming you have tables named user_group and group_membership, here is an example of how you might structure your query.

SELECT u.username FROM user_group ug1 JOIN group_membership gm1 ON ug1.user_id = gm1.user_id JOIN user_group ug2 ON gm1.user_id = ug2.user_id WHERE ug1.group_name = 'confluence-users' AND ug2.group_name = 'Group A';

Regards,
Sushant Verma