I am looking to create a similar report to Site Group Membership.
However, I need to you a filter to enum all users in a certain group named "Bill To"
So I invesitated and found the Stored Procedure that Quest uses in their report
For your SQL Query Gurus, Can someone modify this into include Site Quota/Warning/Used
SELECT
CAST( VSs.Alias + ' (' + dbo.fn_GetSitesFromUrl(sites.FullUrl) + ')' AS nvarchar(256)) AS [VirtualServer],
CAST(isnull(webs.Title, '[No title]') + ' (' + dbo.fn_GetSitesFromUrl(sites.FullUrl) + webs.FullUrl + ')' AS nvarchar(256)) AS [Site],
wg.Title AS [Role],
wg.Description AS [Description],
u.Login AS [UserLogin],
u.Title AS [UserName]
FROM
QSPSWebGroups wg
INNER JOIN QSPSWebs webs ON webs.ID = wg.WebID
INNER JOIN QSPSSites as sites on sites.id = webs.siteid
INNER JOIN QSPSDatabases AS DBs ON sites.DatabaseID = DBs.ID
INNER JOIN QSPSVirtualServers VSs ON DBs.VirtualServerID = VSs.ID
LEFT OUTER JOIN QSPSWebGroupMembership wgmem ON wgmem.GroupID = wg.ID AND wg.WebID = wgmem.WebID
LEFT OUTER JOIN QSPSUsers u ON wgmem.MemberID = u.ID and u.siteID = sites.id
--WHERE cast(sites.ID as nvarchar(50)) LIKE @ID
UNION ALL
SELECT DISTINCT CAST( VSs.Alias + ' (' + dbo.fn_GetSitesFromUrl(sites.FullUrl) + ')' AS nvarchar(256)) AS [VirtualServer],
gr.Title AS [Role], gr.Description AS [Description],
QSPSGroups gr
INNER JOIN QSPSSites sites ON sites.ID = gr.SiteID
LEFT OUTER JOIN QSPSWebs webs ON gr.SiteID = webs.SiteID
LEFT OUTER JOIN QSPSGroupMembership gmem ON gmem.GroupID = gr.ID AND gr.SiteID = gmem.SiteID
LEFT OUTER JOIN QSPSUsers u ON gmem.MemberID = u.ID and u.siteID = sites.id
--WHERE webs.ParentWebID is null AND cast(sites.ID as nvarchar(50)) LIKE @ID
ORDER BY VirtualServer, Site, Role, UserName
I think I got it
SELECT DISTINCT
QSPSGroups.Title, QSPSSites.FullUrl,QSPSSites.DiskUsed, QSPSSites.DiskQuota, SUBSTRING(QSPSUsers.Login, CHARINDEX('\', QSPSUsers.Login)
+ 1, LEN(QSPSUsers.Login)) AS ACF2ID, QSPSUsers.Title, QSPSSites.LastContentChange
FROM QSPSGroups INNER JOIN
QSPSSites ON QSPSSites.ID = QSPSGroups.SiteID INNER JOIN
QSPSDatabases ON QSPSSites.DatabaseID = QSPSDatabases.ID INNER JOIN
QSPSVirtualServers ON QSPSDatabases.VirtualServerID = QSPSVirtualServers.ID LEFT OUTER JOIN
QSPSWebs ON QSPSGroups.SiteID = QSPSWebs.SiteID LEFT OUTER JOIN
QSPSGroupMembership ON QSPSGroupMembership.GroupID = QSPSGroups.ID AND
QSPSGroups.SiteID = QSPSGroupMembership.SiteID LEFT OUTER JOIN
QSPSUsers ON QSPSGroupMembership.MemberID = QSPSUsers.ID AND QSPSUsers.SiteID = QSPSSites.ID
WHERE (QSPSGroups.Title LIKE 'Bill To') AND (QSPSUsers.Login NOT LIKE '%system%')
ORDER BY QSPSSites.FullUrl
Important to note that the Distinct function on the group is important else the enumeration isn't accurate