Trying to return AD Group members with extended info, but output CSV is showing unexpected, repeated data

Question

My goal is to dump a CSV of our AD groups, their members, and whether those member objects are enabled, but I'm running into a strange (probably self-inflicted) issue, wherein a Foreach-Object loop is behaving unexpectedly.

The output almost works. It dumps a CSV file. The file has rows for each group, populated with the correct group-related data, and the right number of rows, following the number of group members. However, group member properties on those rows is repeated, showing the same user data over and over for each groupmember result, apparently following the properties of the last returned object from Get-ADGroupMember.

To try to diagnose the issue, I added the line Write-Host $GroupMember.Name -ForegroundColor Gray. This is how I knew the entries in the CSV were the last-returned results for each group. Confusingly, the console correctly echoes each group member's display name.

I'm assuming there's some kind of logic error at work here, but I have had no luck finding it. Any help would be appreciated!

clear
Import-Module ActiveDirectory

# CONFIG ========================================
# Plant Number OU to scan. Used in $CSV and in Get-ADComputer's search base.
$PlantNumber = "1234"
# FQDN of DC you want to query against. Used by the Get-AD* commands.
$ServerName = "server.com"
# Output directory for the CSV. Default is [Environment]::GetFolderPath("Desktop"). Used in $CSV. NOTE: If setting up as an automated task, change this to a more sensible place!
$OutputDir = [Environment]::GetFolderPath("Desktop")
# CSV Output string. Default is "$OutputDir\$PlantNumber"+"-ComputersByOS_"+"$(get-date -f yyyy-MM-dd).csv" (+'s used due to underscores in name)
$CSV = "$OutputDir\$PlantNumber"+"GroupMembers_"+"$(get-date -f yyyy-MM-dd).csv"

# Create empty array for storing collated results
$collectionTable = @()

# Get AD groups, return limited properties
Get-AdGroup -filter * -Property Name, SamAccountName, Description, GroupScope -SearchBase "OU=Security Groups,OU=$PlantNumber,OU=Plants,DC=SERVER,DC=COM" -server $ServerName | Select SamAccountName, Description, GroupScope | Foreach-Object {
    Write-Host "Querying" $_.SamAccountName "..."
    #Initialize $collectionRow, providing the columns we want to collate
    $collectionRow = "" | Select GroupName, GroupScope, GroupDesc, MemberObjectClass, MemberName, MemberDisplayName, Enabled

    # Populate Group-level collectionRow properties
    $collectionRow.GroupName = $_.SamAccountName
    $collectionRow.GroupDesc = $_.Description
    $collectionRow.GroupScope = $_.GroupScope

    # Process group members
    Get-ADGroupMember -Identity ($collectionRow.GroupName) -Server $ServerName -Recursive | ForEach-Object {
        $GroupMember = $_
        # Echo member name to console
        Write-Host $GroupMember.Name -ForegroundColor Gray

        $collectionRow.MemberName = $GroupMember.SamAccountName
        $collectionRow.MemberDisplayName = $GroupMember.name
        $collectionRow.MemberObjectClass = $GroupMember.ObjectClass

        # If the member object is a user, collect some additional data
        If ($collectionRow.MemberObjectClass -eq "user") {
            Try {
                $collectionRow.Enabled = (Get-ADUser $GroupMember.SamAccountName -Property Enabled -ErrorAction Stop).Enabled
                If ($collectionRow.Enabled -eq "TRUE") {$collectionTable += $collectionRow}
            }
            Catch {
                $collectionRow.Enabled = "ERROR"
                $collectionTable += $collectionRow
            }
            
        }
        
        
    }
}

Write-Host "`n"

# Attempt to save results to CSV. If an error occurs, alert the user and try again.
$ExportSuccess = 'false'
while ($ExportSuccess -eq 'false') {
    Try 
    {
        # Export results to $CSV
        $collectionTable| Export-csv $CSV -NoTypeInformation -ErrorAction Stop
        # If the above command is successful, the rest of the Try section will execute. If not, Catch is triggered instead.
        $ExportSuccess = 'true'
        Write-Host "`nProcessing complete. Results output to"$CSV
    }
    Catch
    {
        Write-Host "Error writing to"$CSV"!" -ForegroundColor Yellow
        Read-Host -Prompt "Ensure the file is not open, then press any key to try again"
    }

}

.... looks for me like this question belongs more to [code review](https://codereview.stackexchange.com/) than to SO. — Olaf, Dec 28 '21 at 21:40
@Olaf, I don't think it meets their posting rules, so it probably isn't a good fit for there. — Adam B, Dec 28 '21 at 21:45
hmmm ... ok, to me your code looks quite confusing. But maybe some general tips could help you anyway. Depending on your AD structure you might end up querying the same users multiple times. Instead you could collect ALL users in a temporary variable once and work with that. For your output I'd recommend using a `PSCustomObject`. — Olaf, Dec 28 '21 at 22:01

Santiago Squarzon · Answer 1 · 2021-12-29T13:16:20.687

There are many things from your code you need to fix, I'll just point out the most important ones:

Don't use @() and +=
You keep using 'True' and 'False' which are strings, PowerShell booleans are $true and $false.

There is also too much redundant code. Also ForEach-Object is slow, if your groups have many members and since you're using -Recursive it's better to use a fast loop instead.

$PlantNumber = "1234"
$ServerName = "server.com"
$OutputDir = [Environment]::GetFolderPath("Desktop")
$fileName = "${PlantNumber}GroupMembers_$(Get-Date -f yyyy-MM-dd).csv"
$CSV = Join-Path $OutputDir -ChildPath $fileName

# $collectionTable = @() => Don't do this to collect results, ever

$adGroupParams = @{
    # Name and SAM are default, no need to add them
    Properties = 'Description', 'GroupScope'
    SearchBase = "OU=Security Groups,OU=$PlantNumber,OU=Plants,DC=SERVER,DC=COM"
    Server     = $ServerName
    Filter     = '*'
}

# Get AD groups, return limited properties
$collectionTable = foreach($group in Get-AdGroup @adGroupParams)
{
    Write-Host "Querying $($group.samAccountName)..."
    foreach($member in Get-ADGroupMember $group -Server $ServerName -Recursive)
    {
        # if this member is 'user' the Enabled property
        # will be a bool ($true / $false) else it will be $null
        $enabled = if($member.ObjectClass -eq 'User')
        {
            (Get-ADUser $member).Enabled
        }
    
        [pscustomobject]@{
            GroupName         = $group.SamAccountName
            GroupDesc         = $group.Description
            GroupScope        = $group.GroupScope
            MemberName        = $member.SamAccountName
            MemberDisplayName = $member.Name
            MemberObjectClass = $member.ObjectClass
            Enabled           = $enabled
        }
    }
}

Olaf · Answer 2 · 2021-12-28T22:52:21.350

I am explicitly NOT refering your code. I'd just like to show how I would approach this task. I hope it'll help you anyway.

$Server = 'Server01.contoso.com'
$SearchBase = 'OU=BaseOU,DC=contoso,DC=com'
$CSVOutputPath = '... CSV path '

$ADGroupList = Get-ADGroup -Filter * -Properties Description -SearchBase $SearchBase -Server $Server
$ADUserList  = Get-ADUser  -Filter * -Properties Description -SearchBase $SearchBase -Server $Server
$Result = 
foreach ($ADGroup in $ADGroupList) {
    $ADGroupMemberList = Get-ADGroupMember -Identity $ADGroup.sAMAccountName -Recursive
    foreach ($ADGroupmember in $ADGroupMemberList) {
        $ADUser = $ADUserList | Where-Object -Property sAMAccountName -EQ -Value $ADGroupmember.sAMAccountName
        [PSCustomObject]@{
            ADGroupName                 = $ADGroup.Name
            ADGroupDescription          = $ADGroup.Description
            ADGroupMemberName           = $ADUser.Name
            ADGroupMemberSamAccountName = $ADUser.sAMAccountName
            ADGroupMemberDescription    = $ADUser.Description
            ADGroupMemberStatus         = if ($ADUser.Enabled) { 'enabled' }else { 'diabled' }
        }
    }
}
$Result |
    Export-Csv -Path $CSVOutputPath -NoTypeInformation -Delimiter ',' -Encoding utf8

It'll output only the a few properties but I hope you get the idea.

BTW: The properties DistinguishedName, Enabled, GivenName, Name, ObjectClass, ObjectGUID, SamAccountName, SID, Surname, UserPrincipalName are included in the default return set of Get-ADUser and the properties DistinguishedName, GroupCategory, GroupScope, Name, ObjectClass, ObjectGUID, SamAccountName, SID are included in the default return set of Get-ADGroup. You don't need to query them explicitly with the parameter -Properties.

Thanks all for your responses! In the end, I used PSCustomObject as Olaf suggested, following all your examples. It works fine now! However, I'm still confused as to why certain values return what they do in Powershell, and will submit a separate question with a less convoluted example. Thanks again! — Adam B, Dec 30 '21 at 15:43

score 0 · Answer 3 · answered Dec 28 '21 at 22:42

as i understand, you need to export list of groups with members to a csv file and know if member accounts are enabled or not, if this what you want, you can check the below code

$output = @()
Import-Module ActiveDirectory
$ServerName = "server.com"
$PlantNumber = "1234"
$OutputDir = [Environment]::GetFolderPath("Desktop")
$CSV = "$OutputDir\$PlantNumber"+"GroupMembers_"+"$(get-date -f yyyy-MM-dd).csv"


$groups = Get-AdGroup -filter * -Property Description -SearchBase "OU=Security Groups,OU=$PlantNumber,OU=Plants,DC=SERVER,DC=COM" -server $ServerName

foreach ($group in $groups){
$members = Get-ADGroupMember -Identity $group.SamAccountName -Recursive
    foreach ($member in $members){
        $output += [pscustomobject]@{
        GroupName = $group.SamAccountName
        GroupDesc = $group.Description
        GroupScope = $group.GroupScope
        MemberName = $member.samaccountname
        MemberDisplayName = $member.Name
        MemberObjectClass = $member.ObjectClass
        Enabled = $(Get-ADUser -Identity $member.samaccountname).enabled

        }
    }
}
$output | Export-Csv $CSV -NoTypeInformation

Trying to return AD Group members with extended info, but output CSV is showing unexpected, repeated data

3 Answers3