2

I'm trying to pull back the members of an AD distribution group using Excel's Power Query tool.

Using the Active Directory data source I can query for all users on a domain. Following is the generated formula: = mydomain.mycompany.com{[Category="user"]}[Objects]

I'm hoping to find some way to refine this, either by updating the formula or adding steps, to allow the query to filter for only those users who are a member of a given security group (ideally this would include recursive memberships).

I'm using Power Query downloaded from: http://www.microsoft.com/en-gb/download/details.aspx?id=39379 with Excel 2013.

Thanks in advance.

Update 2021

A colleague recently hit the same issue and we rediscovered this old post... Here's a tweaked version of the accepted answer which he found useful (this has our parameters hardcoded in the query so the example's easily usable; in reality those would be passed in from outside). More info.

let
    parmDomainFqdn = "-put the domain's FQDN here-",
    parmGroupSAMAccountName = "-put the group's SAMAccountName here-",
    Source = ActiveDirectory.Domains(parmDomainFqdn),
    selectedDomain = Source{[Domain=parmDomainFqdn]}[Object Categories],
    groups = selectedDomain{[Category="group"]}[Objects],
    groupSAMAccountNameExpanded = Table.ExpandRecordColumn(groups, "securityPrincipal", {"sAMAccountName"}, {"groupSAMAccountName"}),
    ourGroup = Table.SelectRows(groupSAMAccountNameExpanded, each ([groupSAMAccountName] = parmGroupSAMAccountName)),
    ourGroupRecordExpanded = Table.ExpandRecordColumn(ourGroup, "group", {"member"}, {"ourGroupMembersList"}),
    ourMemberListExpanded = Table.ExpandListColumn(ourGroupRecordExpanded, "ourGroupMembersList"),
    ourGroupMembersList = ourMemberListExpanded{0}[ourGroupMembersList],
    membersWithFields = Table.ExpandRecordColumn(ourMemberListExpanded, "ourGroupMembersList", {"displayName", "givenName", "sn", "userPrincipalName"}, {"Display Name", "Given Name", "Surname", "UPN"}),
    removeSuperfluous = Table.SelectColumns(membersWithFields, {"Display Name", "Given Name", "Surname", "UPN"})
in
    removeSuperfluous
JohnLBevan
  • 930

2 Answers2

3

here is an example:

let
      Source = ActiveDirectory.Domains(),
      <domain name> = Source{[Domain="YourDomain"]}[#"Object Categories"],
      group = <domain name>{[Category="group"]}[Objects],
      FilteredRows = Table.SelectRows(group, each Text.Contains([distinguishedName], "SomeGroupNameFilter")),
      #"CN=SomeGroupName,OU=SomeOU,OU=All,DC=SomeDC,DC=net" = FilteredRows{[distinguishedName="CN=SomeGroupName,OU=SomeOU,OU=All,DC=SomeDC,DC=net"]}[group],
      member = #"CN=SomeGroupName,OU=SomeOU,OU=All,DC=SomeDC,DC=net"[member],
      TableFromList = Table.FromList(member, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #"Expand Column1" = Table.ExpandRecordColumn(TableFromList, "Column1", {"department", "title", "sAMAccountName"}, {"Column1.department", "Column1.title", "Column1.sAMAccountName"})
in
    #"Expand Column1"
Alex
  • 546
ScaleOvenStove
  • 146
1

Answer from ScaleOvenStove is an okay example, but there is a lot of editing involved to get it working. I created a PQ script that uses a parameter value to simplify the query process. I have provided code below, in case it helps anyone else needing this.

The first query, ListAllGroups_AD, will return all groups on the domain and I also have a function that returns the count of members in each group.

The second query, AD_GroupUsers, will return all the users within a selected group. In order for this query to work you will need to create a parameter named paramADGroupName as datatype 'Text', and enter your group name as the parameter value (Hint: Use the first query to find a group name).

NOTE: In both queries you will need to replace the text YourDomainHere with your domain name. That is a total of 4 changes, and that should be all the changes required before the script is pulling the correct data.

ListAllGroups_AD

let

   Source = ActiveDirectory.Domains("`YourDomainHere`"),

   MyDomainName = Source{[Domain="`YourDomainHere`]}[#"Object Categories"],

   group1 = MyDomainName{[Category="group"]}[Objects],

   #"Expanded securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}),

   #"Sorted Rows" = Table.Sort(#"Expanded securityPrincipal",{{"securityPrincipal.sAMAccountName", Order.Ascending}}),

   #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"securityPrincipal.sAMAccountName", "displayName", "group", "top", "msExchMailStorage", "posixGroup", "msExchIMRecipient", "msExchBaseClass", "msExchCustomAttributes", "mailRecipient", "distinguishedName"}),

   #"Expanded group" = Table.ExpandRecordColumn(#"Reordered Columns", "group", {"member"}, {"group.member"}),

   fxGroupMember_Count = Table.AddColumn(#"Expanded group", "GroupMember_Count", each List.Count([group.member] as list) as number),

   #"fxCount_Replaced Errors" = Table.ReplaceErrorValues(fxGroupMember_Count, {{"GroupMember_Count", 0}})

in

   #"fxCount_Replaced Errors"

AD_GroupUsers

let

     Source = ActiveDirectory.Domains("`YourDomainHere`"),

     MyDomainName = Source{[Domain="`YourDomainHere`"]}[#"Object Categories"],

     group = MyDomainName{[Category="group"]}[Objects],

    #"Expanded securityPrincipal" = Table.ExpandRecordColumn(group, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}),

    #"Filtered Rows" = Table.SelectRows(#"Expanded securityPrincipal", each [securityPrincipal.sAMAccountName] = paramADGroupName),

    #"Filtered Rows_Group" = #"Filtered Rows"{[securityPrincipal.sAMAccountName= paramADGroupName]}[group],

     MembersList = #"Filtered Rows_Group"[member],

     TableFromList = Table.FromList(MembersList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

     #"Expand Column" = Table.ExpandRecordColumn(TableFromList, "Column1", {paramADGroupName, "displayName", "sAMAccountName", "userPrincipalName", "department"},

 {"GroupName", "MembersDisplayName", "sAMAccountName", "userPrincipleName", "department"}),

    #"Replaced Value" = Table.ReplaceValue(#"Expand Column",null,paramADGroupName,Replacer.ReplaceValue,{"GroupName"}),

    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"GroupName", Order.Ascending}, {"MembersDisplayName", Order.Ascending}})

in

    #"Sorted Rows"
SherlockSpreadsheets
  • 181