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
We are on a MSSQL database with net.sourceforge.jtds.jdbc.Driver
Thanks a lot in advance!
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
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.