SQL Query help needed: List all spaces with details / create dynamic admin group

[Jan]

We are currently testing PocketQuery - and as I am no SQL Expert i am hoping to get some help here. I am trying to achieve two things 

1. Create a list of all Confluence Spaces with these details
   • Space Name
   • Space Key
   • Create Date
   • Space Status (active/archived)
   • Last modification in space  (any type)
   • Space Admin(s) 
2. Run a dynamic group with all space admins - this group should be  updated once daily at least based on current permissions

We are on a MSSQL database with net.sourceforge.jtds.jdbc.Driver

Thanks a lot in advance! 

[Davin Studer · Accepted solution]

This SQL will get you what you are asking in your first question. Not sure I understand the second question.

select
    S.SPACEID,
    S.SPACENAME,
    S.SPACEKEY,
    S.CREATIONDATE,
    S.SPACESTATUS,
    max(C.LASTMODDATE) LASTMODDATE,
    (
        select P.PERMGROUPNAME + ',' as [data()]
        from SPACEPERMISSIONS P
        where P.SPACEID = S.SPACEID
            and PERMTYPE = 'SETSPACEPERMISSIONS'
        group by P.PERMGROUPNAME
        order by P.PERMGROUPNAME
        for xml path('')
    ) SPACEADMINGROUPS,
    (
        select M.username + ',' as [data()]
        from SPACEPERMISSIONS P
        inner join user_mapping M on P.PERMUSERNAME = M.user_key
        where P.SPACEID = S.SPACEID
            and PERMTYPE = 'SETSPACEPERMISSIONS'
        group by M.username
        order by M.username
        for xml path('')
    ) SPACEADMINUSERS
from SPACES S
inner join CONTENT C on C.SPACEID = S.SPACEID
group by S.SPACEID, S.SPACENAME, S.SPACEKEY, S.CREATIONDATE, S.SPACESTATUS
order by S.SPACENAME

[Jan]

Davin,

Thanks so much, works perfectly!

I am not sure if my 2nd idea is possible: 

Running a group of users which is dynamically containing all users with space admin privs. A group "space admins" should initially contasin all space admins and update itself when users lose or get space access so that we always have a group available with all those users. I would like to use this group for communication with all space admins (> 500) in our environment... 

[Davin Studer]

I don't know of a great way to dynamically update a group, but here is a query to pull up a list of email addresses of people that are in groups assigned space admin access and people assigned space admin access directly.

with addresses as (
    select U.email_address
    from SPACEPERMISSIONS P
    inner join cwd_group G on P.PERMGROUPNAME = G.group_name and P.PERMTYPE = 'SETSPACEPERMISSIONS'
    inner join cwd_membership X on G.id = X.parent_id
    inner join cwd_user U on X.child_user_id = U.id
    where isnull(U.email_address, '') <> ''
    group by U.email_address
    union
    select U.email_address
    from SPACEPERMISSIONS P
    inner join user_mapping M on P.PERMUSERNAME = M.user_key and P.PERMTYPE = 'SETSPACEPERMISSIONS'
    inner join cwd_user U on M.lower_username = U.lower_user_name
    where isnull(U.email_address, '') <> ''
    group by U.email_address
)
select
    stuff (
        (
            select ',' + email_address -- change the ',' to ';' if you want it semicolon delimited
            from addresses
            order by email_address
            for xml path('')
        ), 1, 1, ''
    ) as email_addresses

[KPM]

Hello. I've been searching for just this sort of query, thanks for posting it! Unfortunately, I am getting an error:

"[" is not valid at this position, expecting FOR, LOCK, SELECT, TABLE, VALUES, WITH, ..."

I also get the same error for this section if I comment out the above error:
for xml path('')

I have tried double quotes, single quotes, and back tics, but no luck... I suspect this may be because I am working with an old MySQL version (v5.6.28) and perhaps some of the query is not supported on the older versions? If any MySQL experts out there know of similar queries for the older version of MySQL that would be super helpful! Thanks!