![使用 Power Query/数据资源管理器列出 AD 组成员身份](https://linux22.com/image/1371991/%E4%BD%BF%E7%94%A8%20Power%20Query%2F%E6%95%B0%E6%8D%AE%E8%B5%84%E6%BA%90%E7%AE%A1%E7%90%86%E5%99%A8%E5%88%97%E5%87%BA%20AD%20%E7%BB%84%E6%88%90%E5%91%98%E8%BA%AB%E4%BB%BD.png)
我正在尝试使用 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"