Generating a List of User Names by Group

[Camden Coyle]

I would like to generate a list of User Names by their Confluence Group Membership. I want to use this list to send email alerts to the users for maintenance outages, etc. Is there a way to do this within Confluence, or can you provide a SQL query that I could use to get it directly from the database?

[GuilhermeA]

Hello Camden,

You have either two options, to go to your Group Browser, and there have a list, but you still need to click next if there is lots of users. Or you can use the following query who will have the users by Group :)

select u.lower_email_address,g.group_name from cwd_user u inner join cwd_membership m on u.id=m.child_user_id inner join cwd_group g on g.id=m.parent_id order by g.group_name;

Hope this helps you, also I have made the query to select only emails and group names.

Cheers,

Guilherme

[Glenn Rees]

For just one group

select DISTINCT u.lower_user_name, u.lower_email_address, g.group_name 
from cwd_user u 
inner join cwd_membership m 
on u.id=m.child_user_id inner join cwd_group g 
on g.id=m.parent_id 
where g.group_name = 'mygroup'
order by g.group_name;

[Phil Evans]

@Glenn Rees  this doesnt work

[Monique Khairuliana]

Tweaking Guilherme's sql query to add extra info of username, first and last name:

select u.user_name, u.first_name, u.last_name, u.lower_email_address, g.group_name from cwd_user u inner join cwd_membership m on u.id=m.child_user_id inner join cwd_group g on g.id=m.parent_id order by g.group_name;

[Tyler Anderson]

Why do I get this?

Msg 207, Level 16, State 1, Line 35
Invalid column name 'child_user_id'.

Completion time: 2021-05-26T13:47:30.0141037-04:00

When I change to just Child_ID I get a singular response.