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
5
  • 1
    You may or may not get an answer here. If you don't, you might want to flag your own question and ask to migrate to StackOverflow as you're querying a database and it may be outside the scope of expertise here, depending on who sees your question. Jul 26 '13 at 14:56
  • 1
    Use VBA and loop over the results looking for the specific user(s). Jul 26 '13 at 15:27
  • Thanks @Raystafarian - will do if I don't get an answer here; I started off on this site as there's no "code" / it's Excel standard functionality, so thought this more appropriate (though it is border line).
    – JohnLBevan
    Jul 26 '13 at 17:45
  • Thanks @techie007 - I've actually already got a VBA solution; this is for me to play with some new toys in Office & also to try to move away from macros into using standard functionality which other less techie folk may be able to amend to their needs more easily.
    – JohnLBevan
    Jul 26 '13 at 17:47
  • Why would less techie folk have access to query the AD DB? They should just go to the distribution group, expand it, and copy the people in it. Jul 26 '13 at 21:05
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"
1
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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.