☰

使用 Power Query/数据资源管理器列出 AD 组成员身份

tag-icon • tag-icon microsoft-excel active-directory power-query security-groups
使用 Power Query/数据资源管理器列出 AD 组成员身份

我正在尝试使用 Excel 的 Power Query 工具来拉回 AD 通讯​​组的成员。

使用 Active Directory 数据源，我可以查询域中的所有用户。以下是生成的公式：= mydomain.mycompany.com{[Category="user"]}[Objects]

我希望找到某种方法来改进这一点，无论是通过更新公式还是添加步骤，以允许查询仅筛选出属于给定安全组成员的用户（理想情况下这将包括递归成员资格）。

我正在使用从以下位置下载的 Power Query：http://www.microsoft.com/en-gb/download/details.aspx?id=39379使用 Excel 2013。

提前致谢。

2021 年更新

一位同事最近遇到了同样的问题，我们重新发现了这篇旧帖子......这是他认为有用的已接受答案的调整版本（我们的参数在查询中是硬编码的，因此该示例很容易使用；实际上这些参数是从外部传入的）。 更多信息。

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

答案1

以下是一个例子：

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"

答案2

ScaleOvenStove 的回答是一个不错的例子，但要让它工作起来需要进行大量的编辑。我创建了一个 PQ 脚本，它使用参数值来简化查询过程。我在下面提供了代码，以防它对其他需要它的人有所帮助。

第一个查询，ListAllGroups_AD，将返回域上的所有组，并且我还有一个返回每个组中成员数量的函数。

第二个查询，AD_GroupUsers，将返回选定组内的所有用户。为了使此查询正常工作，您需要创建一个名为paramADGroupName数据类型为“文本”的参数，并输入您的组名称作为参数值（提示：使用第一个查询查找组名称）。

注意：在这两个查询中，您都需要用您的域名替换文本 YourDomainHere。总共需要进行 4 处更改，这应该是脚本提取正确数据之前所需的所有更改。

列出所有群组_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"

相关内容