We wanted a report to take a group and see who ultimately is a member (due to a direct or nested membership), or vice-versa – take a user account, and get all the groups of which that account is ultimately a member. The report uses the data from AD group discovery in the ConfigMgr database.
I have three parameters, a object type (user, group), domain name, and from those generate the list of groups/users:
select distinct ObjectName from ( select Unique_Usergroup_Name0 ObjectName from v_R_UserGroup where @type = 'group' and AD_Domain_Name0 = @Domain union all select Unique_User_Name0 ObjectName from v_R_User where @type = 'user' and Full_Domain_Name0 = @Domain ) a order by 1
Then take the @type (group or user) and @ObjectName and use the following:
select UPPER (g.AD_Domain_Name0) AD_Domain_Name0 , g.Unique_Usergroup_Name0 , UPPER (u.Full_Domain_Name0) Full_Domain_Name0 , u.Unique_User_Name0 , u.User_Principal_Name0 , u.Full_User_Name0 , u.Mail0 , u.Name0 , case when n.User_ObjectGUID is null then 'Nested' else 'Direct' end Direct from v_R_User u inner join Flat_Group_User_Relationship r on r.User_ObjectGUID = u.Object_GUID0 inner join v_R_UserGroup g on g.Object_GUID0 = r.Group_ObjectGUID left join Group_User_Relationship n on n.User_ObjectGUID = u.Object_GUID0 and n.Group_ObjectGUID = g.Object_GUID0 where (g.Unique_Usergroup_Name0 = @ObjectName and @type = 'group') or (u.Unique_User_Name0 = @ObjectName and @type = 'user') order by 1, 2, 3, 4