Listar uma associação do grupo AD usando o Power Query / Data Explorer

Question

Estou tentando recuperar os membros de um grupo de distribuição do AD usando a ferramenta Power Query do Excel.

Usando a fonte de dados do Active Directory, posso consultar todos os usuários em um domínio. A seguir, a fórmula gerada: = mydomain.mycompany.com{[Category="user"]}[Objects]

Espero encontrar uma maneira de refinar isso, atualizando a fórmula ou adicionando etapas, para permitir que a consulta filtre apenas os usuários que são membros de um determinado grupo de segurança (o ideal seria incluir associações recursivas ).

Estou usando o Power Query baixado de: link com o Excel 2013 .

Obrigado antecipadamente.

    

Answer 1

aqui é um exemplo:

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"

    

Answer 2

Resposta do ScaleOvenStove é um bom exemplo, mas há muita edição envolvida para que funcione. Eu criei um script PQ que usa um valor de parâmetro para simplificar o processo de consulta. Eu forneci o código abaixo, caso isso ajude alguém a precisar disso.

A primeira consulta, ListAllGroups_AD , retornará todos os grupos no domínio e também tenho uma função que retorna a contagem de membros em cada grupo.

A segunda consulta, AD_GroupUsers , retornará todos os usuários em um grupo selecionado. Para que esta consulta funcione, você precisará criar um parâmetro chamado paramADGroupName como tipo de dados 'Texto' e inserir o nome do grupo como o valor do parâmetro ( Dica: use a primeira consulta para encontrar um nome de grupo ).

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"