Query the number of user of a given space

[Ramy]

Hi All,

Is there a way to get the mount of user(s) of a given space within one SQL query? some of the spaces they have only users and some of them they have user plus group.

Thanks

[Ramy · Accepted solution]

Here is my solution in case if someone facing the same problem. Any improvement are very welcome!

This query will return a list of all spaces and relevant information...

SELECT DISTINCT
c.SPACEID AS ID
, s.SPACENAME AS 'Space Name'
, ISNULL(cus.first_name, 'UnkownUser') AS 'Admin [First Name]'
, ISNULL(cus.last_name, ums.username) AS 'Admin [Last Name]'
, ISNULL(cus.lower_email_address, 'UnkownEmail') AS 'Admin [Email]'
, CONVERT(varchar, s.CREATIONDATE, 104) AS 'Space Created'
, CONVERT(varchar, s.LASTMODDATE, 104) AS 'Space Last Update'
, c.TITLE AS 'Last Mod [Content]'
, ISNULL(cuc.first_name, 'UnkownUser') AS 'Last Mod [First Name]'
, ISNULL(cuc.last_name, umc.username) AS 'Last Mod [Last Name]'
, cuc.lower_email_address AS 'Last Mod [Email]'
, CONVERT(varchar, clmd.contentLastMod, 104) AS 'Last Mod [Content]'
, YEAR(clmd.contentLastMod) AS 'Last Mod [Year]'
, spuc.MemeberCount AS 'No Memeber'
, ISNULL(gmc.GroupMemberCount, '0') AS 'No Group Memeber'
FROM dbo.CONTENT AS c
LEFT JOIN dbo.SPACES AS s ON c.SPACEID = s.SPACEID
LEFT JOIN dbo.user_mapping AS ums ON s.CREATOR = ums.user_key
LEFT JOIN dbo.cwd_user AS cus ON ums.lower_username = cus.lower_user_name
LEFT JOIN dbo.user_mapping AS umc ON c.LASTMODIFIER = umc.user_key
LEFT JOIN dbo.cwd_user AS cuc ON umc.lower_username = cuc.lower_user_name
INNER JOIN (
SELECT MAX(ca.LASTMODDATE) AS contentLastMod, ca.SPACEID
FROM dbo.CONTENT AS ca
GROUP BY ca.SPACEID
) AS clmd ON c.SPACEID = clmd.SPACEID AND c.LASTMODDATE = clmd.contentLastMod
INNER JOIN (
SELECT COUNT(spuc.PERMUSERNAME) AS MemeberCount, spuc.SPACEID
FROM (
SELECT DISTINCT PERMUSERNAME, sp.SPACEID
FROM dbo.SPACEPERMISSIONS AS sp
WHERE sp.PERMUSERNAME IS NOT NULL
) AS spuc
GROUP BY spuc.SPACEID
) AS spuc ON c.SPACEID = spuc.SPACEID
LEFT JOIN (
SELECT ccms.GroupMemberCount, sp.SPACEID
FROM dbo.cwd_group AS cg
LEFT JOIN SPACEPERMISSIONS AS sp ON sp.PERMGROUPNAME = cg.group_name
LEFT JOIN (
SELECT COUNT(*) AS GroupMemberCount, parent_id
FROM dbo.cwd_membership
GROUP BY parent_id
) AS ccms ON cg.id = ccms.parent_id
WHERE cg.directory_id = <directory_id>
) AS gmc ON c.SPACEID = gmc.SPACEID
WHERE s.SPACETYPE = 'global'
AND s.SPACESTATUS = 'CURRENT'
ORDER BY c.SPACEID;

[Manisha Kharga _Appfire_]

Hi @Ramy 

The following will list all the spaces that the user has been individually granted permissions:

 SELECT s.SPACEKEY FROM SPACEPERMISSIONS sp JOIN SPACES s ON s.SPACEID = sp.SPACEID JOIN user_mapping um ON um.user_key = sp.PERMUSERNAME WHERE um.lower_username = '<username>' GROUP BY s.SPACEKEY ORDER BY s.SPACEKEY;

You can view the restrictions on pages assigned to the user with the following (this will show pages that grant viewing/editing to this username but limits it otherwise). 

SELECT p.cp_type,u.username,c.title
FROM content_perm p
JOIN user_mapping u
ON p.username = u.user_key
JOIN content_perm_set s
ON p.cps_id = s.id
JOIN content c
ON s.content_id = c.contentid
WHERE c.contenttype = 'PAGE'
AND u.username = '<user_name>'

Hope it helps you!

Thanks,
Manisha

[Ramy]

Hi @Manisha Kharga _Appfire_ 

Thank you for you reply. But you didn't answer my question! Im looking for a SQL query, which returns me the number of user by given a space name....

Thank you anyway and cheers

Ramy