Get nested groups in a group with members in an excel file

%3CLINGO-SUB%20id%3D%22lingo-sub-3545370%22%20slang%3D%22en-US%22%3EGet%20nested%20groups%20in%20a%20group%20with%20members%20in%20an%20excel%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3545370%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20I%20have%20the%20following%20quest%20powershell%20script%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFunction%20Get-NestedGroupMember(%24group)%20%7B%20%0AGet-QADGroupMember%20%24group%20%7C%20foreach%7B%20%0Aif(%24_.type%20-eq%20%22group%22)%7BGet-NestedGroupMember(%24_)%7D%20%0Aelse%20%7B%24_%7D%7D%20%0A%7D%20%0AGet-NestedGroupMember(%22domain%20admins%22)%20%7C%20Select%20Name%20%7C%20Export-CSV%20.%5Ctest.csv%20-NoType%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20It%20does%20is%20to%20bring%20all%20nested%20groups%20within%20%22domain%20admins%22%20group%20which%20is%20fine%2C%20but%20when%20I%20open%20the%20CSV%20file%20I%20can%20see%20the%20members%20of%20all%20nested%20groups%2C%20however%20what%20I%20need%20is%20to%20have%20another%20column%20stating%20which%20group%20the%20user%20is%20a%20member%20of.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20help%3F%3C%2FP%3E%3CP%3EMany%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3545370%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3546109%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20nested%20groups%20in%20a%20group%20with%20members%20in%20an%20excel%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3546109%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1434490%22%20target%3D%22_blank%22%3E%40tincho1984%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirstly%2C%20you%20can%20pull%20the%20transitive%20membership%20a%20little%20more%20easily.%20Here's%20an%20example%20using%20the%20default%20Microsoft%20commandlets%20as%20I%20don't%20run%20the%20Quest%20module.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3EGet-ADObject%20-Filter%20%7B%20objectClass%20-eq%20%22group%22%20%7D%20-SearchBase%20((Get-ADObject%20-Filter%20%7B%20(objectClass%20-eq%20%22group%22)%20-and%20(cn%20-eq%20%22Domain%20Admins%22)%20%7D).distinguishedName)%20-SearchScope%20Base%20-Properties%20msds-memberTransitive%20%7C%20Select-Object%20name%2C%20msds-memberTransitive%3B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20requires%20a%20maximum%20of%20only%20two%20calls%20to%20Active%20Directory%2C%20rather%20than%20a%20call%20for%20every%20nested%20group.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20produce%20the%201%3A1%20listing%20between%20group%20name%20and%20member%20name%20that%20you%20asked%20for%2C%20you%20expand%20upon%20this%20statement%20above%20by%20using%20a%20loop%20construct%2C%20as%20shown%20below.%20Technically%2C%20you%20actually%20need%20two%20loops%3A%20one%20to%20handle%20the%20groups%20returned%20from%20Get-ADObject%20and%20a%20second%20to%20handle%20the%20members%20within%20msds-memberTransitive.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20basic%20idea%20is%20that%20you%20iterate%20through%20each%20member%20of%20the%20group%2C%20adding%20in%20the%20group%20name%20next%20to%20the%20member's%20distinguishedName.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3E%24OutputFile%20%3D%20%22.%5CsomeCsvFile.Csv%22%3B%0A%24GroupName%20%3D%20%22Domain%20Admins%22%3B%0AGet-ADObject%20-Filter%20%7B%20objectClass%20-eq%20%22group%22%20%7D%20-SearchBase%20((Get-ADObject%20-Filter%20%7B%20(objectClass%20-eq%20%22group%22)%20-and%20(cn%20-eq%20%24GroupName)%20%7D).distinguishedName)%20-SearchScope%20Base%20-Properties%20msds-memberTransitive%20%7C%0A%20%20%20%20ForEach-Object%20%7B%0A%20%20%20%20%20%20%20%20%24Group%20%3D%20%24_%3B%0A%0A%20%20%20%20%20%20%20%20%24Group.'msds-memberTransitive'%20%7C%0A%20%20%20%20%20%20%20%20%20%20%20%20ForEach-Object%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5BPSCustomObject%5D%20%40%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20GroupName%20%3D%20%24Group.name%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Member%20%3D%20%24_%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%20%7C%20Export-Csv%20-NoTypeInformation%20-Path%20%24OutputFile%3B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20produces%20the%20output%20you%20were%20after.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3ELain%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi everyone, I have the following quest powershell script:

 

 

Function Get-NestedGroupMember($group) { 
Get-QADGroupMember $group | foreach{ 
if($_.type -eq "group"){Get-NestedGroupMember($_)} 
else {$_}} 
} 
Get-NestedGroupMember("domain admins") | Select Name | Export-CSV .\test.csv -NoType

 

 

What It does is to bring all nested groups within "domain admins" group which is fine, but when I open the CSV file I can see the members of all nested groups, however what I need is to have another column stating which group the user is a member of.

 

Could you please help?

Many thanks!

1 Reply

@tincho1984 

 

Hi.

 

Firstly, you can pull the transitive membership a little more easily. Here's an example using the default Microsoft commandlets as I don't run the Quest module.

 

Get-ADObject -Filter { objectClass -eq "group" } -SearchBase ((Get-ADObject -Filter { (objectClass -eq "group") -and (cn -eq "Domain Admins") }).distinguishedName) -SearchScope Base -Properties msds-memberTransitive | Select-Object name, msds-memberTransitive;

 

This requires a maximum of only two calls to Active Directory, rather than a call for every nested group.

 

To produce the 1:1 listing between group name and member name that you asked for, you expand upon this statement above by using a loop construct, as shown below. Technically, you actually need two loops: one to handle the groups returned from Get-ADObject and a second to handle the members within msds-memberTransitive.

 

The basic idea is that you iterate through each member of the group, adding in the group name next to the member's distinguishedName.

 

$OutputFile = ".\someCsvFile.Csv";
$GroupName = "Domain Admins";
Get-ADObject -Filter { objectClass -eq "group" } -SearchBase ((Get-ADObject -Filter { (objectClass -eq "group") -and (cn -eq $GroupName) }).distinguishedName) -SearchScope Base -Properties msds-memberTransitive |
    ForEach-Object {
        $Group = $_;

        $Group.'msds-memberTransitive' |
            ForEach-Object {
                [PSCustomObject] @{
                    GroupName = $Group.name;
                    Member = $_;
                }
            }
    } | Export-Csv -NoTypeInformation -Path $OutputFile;

 

Which produces the output you were after.

 

Cheers,

Lain