List all users in Confluence by space name and group name SQL server

[Marine Tm]

Hello,

how can i list all users of confluence with the group which he belongs and name of the space and key using SQL server

i want to obtain a table like :

Space Key | Space name | Username | Group name |

|                |                      |                  |                      |

[Alexis Robert · Accepted solution]

Hi @Marine Tm , 

this query is almost exactly what you need : 

SELECT distinct s.SPACENAME, s.SPACEKEY, cg.group_name, cu.lower_email_address
FROM SPACES AS s
JOIN SPACEPERMISSIONS AS sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping AS u ON sp.permusername = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE cu.lower_email_address is not null 
ORDER BY s.SPACENAME, cu.lower_email_address;

Let me know if this helps,

--Alexis

[Marine Tm]

Hello @Alexis Robert 

Thank you so much, the query works exactly as i wish.

[Marine Tm]

Hello @Alexis Robert  do you have any sql query for adding a user into jira group or something that could help me to do it please?

[Alexis Robert]

Hi @Marine Tm , I'm not sure, it depends what exactly you need.

Can I suggest that you create a new topic and tag me ? I will then be able to look at your question.

Thanks,

Alexis

[Mugilan Sukumar]

Hi Alexis,

Shall i get  the same qurey for oracle?

Thanks

Mugilan Sukumr

[Mugilan Sukumar]

@Alexis Robert