Confluence SQL query

Thor Henrik Nordvik

Hi 

I am looking for the SQL for getting the names and emails to all our spaceadmins. 

Spesifikk. 

• Space 
• Space status (filtered on spaced with status CURRENT)  
• Space admin name 
• Email  

Matthias Gaiser _K15t_

Hi @Thor Henrik Nordvik,

I think, this Atlassian knowledge base article will help you. It contains various SQL queries for the different database dialects as well as a guide how to create a user macro so that you even could add that on a Confluence page and have an always up to date report.

Here's the generic SQL statement:

<span>SELECT</span> <span>DISTINCT</span> s<span>.</span>spaceid<span>,</span>
       s<span>.</span>spacekey<span>,</span>
       s<span>.</span>spacename<span>,</span>
       u<span>.</span>lower_username <span>AS</span> individual_lower_username<span>,</span>
       cu<span>.</span>lower_user_name <span>AS</span> group_member_lower_username<span>,</span>
       sp<span>.</span>permgroupname <span>AS</span> groupname<span>,</span>
       cg<span>.</span><span>local</span> <span>AS</span> local_group<span>,</span>
       <span>cd</span><span>.</span>directory_name <span>AS</span> group_directory_name
<span>FROM</span> spaces <span>AS</span> s
<span>JOIN</span> spacepermissions <span>AS</span> sp <span>ON</span> s<span>.</span>spaceid <span>=</span> sp<span>.</span>spaceid
<span>LEFT</span> <span>JOIN</span> user_mapping <span>AS</span> u <span>ON</span> sp<span>.</span>permusername <span>=</span> u<span>.</span>user_key
<span>LEFT</span> <span>JOIN</span> cwd_user <span>AS</span> <span>c</span> <span>ON</span> <span>c</span><span>.</span>lower_user_name <span>=</span> u<span>.</span>lower_username
<span>LEFT</span> <span>JOIN</span> cwd_group <span>AS</span> cg <span>ON</span> sp<span>.</span>permgroupname <span>=</span> cg<span>.</span>group_name
<span>LEFT</span> <span>JOIN</span> cwd_directory <span>AS</span> <span>cd</span> <span>ON</span> cg<span>.</span>directory_id <span>=</span> <span>cd</span><span>.</span>id
<span>LEFT</span> <span>JOIN</span> cwd_membership <span>AS</span> cm <span>ON</span> cg<span>.</span>id <span>=</span> cm<span>.</span>parent_id
<span>LEFT</span> <span>JOIN</span> cwd_user <span>AS</span> cu <span>ON</span> cu<span>.</span>id <span>=</span> cm<span>.</span>child_user_id
<span>WHERE</span> s<span>.</span>spacestatus <span>=</span> <span>'CURRENT'</span>
  <span>AND</span> sp<span>.</span>permtype <span>=</span> <span>'SETSPACEPERMISSIONS'</span>
<span>ORDER</span> <span>BY</span> spacekey<span>,</span>groupname<span>;</span>

Please note that this statement doesn't yet return emails - but I guess you can modify the select part so that it also returns it.

Cheers,
Matthias.