Active Directory hierarchy - getting all users inside a group including child groups

[Moof]

Hi,

I've been playing around with Active Directory as a data source on PowerBI for a while, trying to make reports.

One report I'm interested in is "Users in the Administrators Group"

My Administrators group has a number of users in it, as well as a Group - Domain Admins.

Domain Admins itself has a group in it too - Security administrators.

I'm trying to navigate the tree somehow either in M or DAX so that I can get a list of "In the Administrators Group, all the users are x, y, and z" where x is in Administrators, y is in Domain Admins, and z is in Security Administrators.

I've been thinking about seeing if I can get a list of "this group is a member of this group" (which I can do using top.memberOf) but I can't seem to work out how to navigate from Security Admins to Administrators, so I can see that Security Admins is a member of Domain admins and Administrators.

If I could do that, from there I could just say that since user z is in Security Administrators, it's also in Domain Administrators and Administrators. 

I'm specifically trying to find a generic solution here, rather than hard coding my tree into the programme.

Am I barking up the wrong tree? How do I do this sort of recursive query in M/DAX?

[v-yuezhe-msft]

@Moof,

Please help to post sample data of your tables and post expected result  based on sample data here.

Regards,
Lydia

[Moof]

Or, placing it a different way...

I have a hierachy of groups in ActiveDirectory, and a bunch of users that belong to those groups.

Security Administrators is a member of Domain Admins which is a member of Administrators. Each of these groups has users in it. I want to filter by group Administrators, and get users from all the subgroups.

Essentially, using sample date, the result I'm looking for is:

The code I'm using to get there is as follows:

let
    Source = ActiveDirectory.Domains("lapsang.example"),
    lapsang.example = Source{[Domain="lapsang.example"]}[#"Object Categories"],
    user1 = lapsang.example{[Category="user"]}[Objects],
    #"Expanded top" = Table.ExpandRecordColumn(user1, "top", {"cn", "memberOf"}, {"top.cn", "top.memberOf"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded top",{"top.cn", "top.memberOf", "distinguishedName"}),
    #"Expanded top.memberOf" = Table.ExpandListColumn(#"Removed Other Columns", "top.memberOf"),
    #"Expanded top.memberOf1" = Table.ExpandRecordColumn(#"Expanded top.memberOf", "top.memberOf", {"distinguishedName", "memberOf"}, {"top.memberOf.distinguishedName", "top.memberOf.memberOf"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Expanded top.memberOf1",{"top.cn", "distinguishedName", "top.memberOf.distinguishedName", "top.memberOf.memberOf"}),
    #"Expanded top.memberOf.memberOf" = Table.ExpandListColumn(#"Reordered Columns1", "top.memberOf.memberOf"),
    #"Expanded top.memberOf.memberOf1" = Table.ExpandRecordColumn(#"Expanded top.memberOf.memberOf", "top.memberOf.memberOf", {"distinguishedName", "memberOf"}, {"top.memberOf.memberOf.distinguishedName", "top.memberOf.memberOf.memberOf"}),
    #"Expanded top.memberOf.memberOf.memberOf" = Table.ExpandListColumn(#"Expanded top.memberOf.memberOf1", "top.memberOf.memberOf.memberOf"),
    #"Expanded top.memberOf.memberOf.memberOf1" = Table.ExpandRecordColumn(#"Expanded top.memberOf.memberOf.memberOf", "top.memberOf.memberOf.memberOf", {"distinguishedName", "memberOf"}, {"top.memberOf.memberOf.memberOf.distinguishedName", "top.memberOf.memberOf.memberOf.memberOf"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded top.memberOf.memberOf.memberOf1", {"top.cn", "distinguishedName", "top.memberOf.memberOf.memberOf.memberOf"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"top.cn", "distinguishedName", "Value"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Value", "InGroup"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Count"})
in
    #"Removed Columns"

Now, as you can see, I've used Table.ExpandListColumn, and Table.ExpandRecordColumn several times there, and I can't guarantee that if I query a different AD that I'll have managed to get all the levels of expanding. 

Is there a way to recurse through the table, such that it does that as many times as neccesary, and then leaves an unpivoted result?

[claitonjs]

Hi! I have the same question. Do you have some update?

[Moof]

OK, it's taken me a little while to sort out a test environment.

I have set up a fresh AD domain. The Built-in Administrators group has the following:

And then I populated the hierarchy as follows:

Domain Admins GroupSecurity Administrators Group

As you can see, a fairly simple hierarchy.

I am trying to set up an audit screen that shows the members of a group, including any sub groups that are in the group. So if I were to filter by the group Administrators, I get the following expected result for users:

• lapsangadmin
• Siuan Sanche (admin)
• Lean Sharif (admin)

So now I enter Power BI and use the AD connector to Users. This gives me a rather difficult to deal with table:

Unedited Users Query

When I filter it a bit, and expand the "top" column I get:

Again, I expand those lists into separate rows, and expand the records, and I get...

The code I use to get there is:

let
    Source = ActiveDirectory.Domains("lapsang.example"),
    lapsang.example = Source{[Domain="lapsang.example"]}[#"Object Categories"],
    user1 = lapsang.example{[Category="user"]}[Objects],
    #"Expanded top" = Table.ExpandRecordColumn(user1, "top", {"cn", "memberOf"}, {"top.cn", "top.memberOf"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded top",{"top.cn", "top.memberOf", "distinguishedName"}),
    #"Expanded top.memberOf" = Table.ExpandListColumn(#"Removed Other Columns", "top.memberOf"),
    #"Expanded top.memberOf1" = Table.ExpandRecordColumn(#"Expanded top.memberOf", "top.memberOf", {"distinguishedName"}, {"top.memberOf.distinguishedName"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded top.memberOf1",{"top.cn", "distinguishedName", "top.memberOf.distinguishedName"})
in
    #"Reordered Columns"

with this, I can tell that lapsangadmin is in Administrators and Domain Admins, that Siuan Sanche (admin) is in Domain Admins and that Leane Sharif (admin) is in Security Admins.

But I can't filter by Administrators and get all three.

So I start to look at the group object instead, which is an equally unappetising initial query:

In top, there is again a "memberOf" column, which has a list of other records:

And again, expanding that gives me:

The code I use to get here is:

let
    Source = ActiveDirectory.Domains("lapsang.example"),
    lapsang.example = Source{[Domain="lapsang.example"]}[#"Object Categories"],
    user1 = lapsang.example{[Category="user"]}[Objects],
    #"Expanded top" = Table.ExpandRecordColumn(user1, "top", {"cn", "memberOf"}, {"top.cn", "top.memberOf"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded top",{"top.cn", "top.memberOf", "distinguishedName"}),
    #"Expanded top.memberOf" = Table.ExpandListColumn(#"Removed Other Columns", "top.memberOf"),
    #"Expanded top.memberOf1" = Table.ExpandRecordColumn(#"Expanded top.memberOf", "top.memberOf", {"distinguishedName"}, {"top.memberOf.distinguishedName"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded top.memberOf1",{"top.cn", "distinguishedName", "top.memberOf.distinguishedName"})
in
    #"Reordered Columns"

So this is where I'm stuck. 

Eventually, I could just keep expanding memberOf until I run out of things, and then unpivot the resulting distinguishedName columns into one. The thing is, I'm trying to make this a generic solution that doesn't need to know how many times to expand, because whilst in this test case, I only have a simple hierarchy like this, in some of the Active Directories I deal with this hierarchy may go up to 6 or 8 groups deep.

So I suppose I'm asking: how do I do this sort of recursion in M, such that I can get a table of all the parents a group may have, withough needing to do it a specific number of times?

I suppose I'm trying to get a table like this:

Group	Contained in
Administrators	Administrators
Security Administrators	Security Administrators
Security Administrators	Domain Admins
Security Administrators	Administrators
Domain Admins	Domain Admins
Domain Admins	Administrators

 ...and so on

 From there, I can link my users to that Group Column, and then filter by a summary of the Contained In Column.

Any hints gratefully appreciated...