User to Groups Relationships

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:

  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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s