Forum Discussion
Active Directory hierarchy - getting all users inside a group including child groups
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?