I am trying to export list of users in mail enabled security groups to csv but want to have each member in a separate column rather than joining the existing column.

$Csvfile = "C:\SPOgroupmembers.csv"

$Groups = Get-DistributionGroup -Filter "Alias -like '*.spo'" -ResultSize Unlimited

$Groups | ForEach-Object {

    $GroupDN = $_.DistinguishedName
    $DisplayName = $_.DisplayName
    $PrimarySmtpAddress = $_.PrimarySmtpAddress
    $Members = Get-DistributionGroupMember $GroupDN -ResultSize Unlimited
   

    
    [PSCustomObject]@{
        DisplayName                        = $DisplayName
        PrimarySmtpAddress                 = $PrimarySmtpAddress
        Members                            = ($Members.Name -join ',')

    }


} | Sort-Object DisplayName | Export-CSV -Path $Csvfile -NoTypeInformation -Encoding UTF8 #-Delimiter ";"

This is how it currently outputs:

This is what I am trying to achieve:

I may be missing something simple but any help would be appreciated

CodePudding user response：

The simplest way to construct a [pscustomobject] dynamically is to construct an ordered hashtable first - which is easy to extend iteratively - and cast it to [pscustomobject] when done.

However, in the context of creating CSV output, you need to commit to a fixed number of properties (columns) ahead of time - if feasible; e.g.:

$maxMembers = 10 # <- adjust this number to the max. count of members you expect

# $Groups | ForEach-Object { ...

    $oht = [ordered] @{
        DisplayName                        = $DisplayName
        PrimarySmtpAddress                 = $PrimarySmtpAddress
    }
    # Add Member1, Member2, ... entries
    foreach ($i in 1..$maxMembers) {
      $oht["Member$i"] = $Members[$i-1]
    }

    # Convert to a [pscustomobject] and output
    [pscustomobject] $oht

CodePudding user response：

If you want to export the data where each member has it's own row, which in my opinion, would be the proper way to do it, you can have an inner loop to create a new pscustomobject per member of the Group:

Get-DistributionGroup -Filter "Alias -like '*.spo'" -ResultSize Unlimited | ForEach-Object {
    foreach($member in Get-DistributionGroupMember $_.DistinguishedName -ResultSize Unlimited) {
        [PSCustomObject]@{
            DisplayName        = $_.DisplayName
            PrimarySmtpAddress = $_.PrimarySmtpAddress
            Members            = $member
        }
    }
} | Sort-Object DisplayName | Export-CSV -Path ....