Exchange/Powershell - Add data to new column rather than joining

user_14936823 0
Viewed 66

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:

DisplayName PrimarySmtpAddress Member
Test.SPO Test.SPO@test.com User1,User2,User3

This is what I am trying to achieve:

DisplayName PrimarySmtpAddress
Test.SPO Test.SPO@test.com User1 User2

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

powershell
2 Answers

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[1]; e.g.:

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

$Groups | ForEach-Object { ...

    # ...

    # Initialize an ordered hashtable with the static entries...
    $oht = [ordered] @{
        DisplayName                        = $DisplayName
        PrimarySmtpAddress                 = $PrimarySmtpAddress
    }
    # ... then iteratively add the Member1, Member2, ... entries
    foreach ($i in 1..$maxMembers) {
      $oht["Member$i"] = $Members[$i-1]
    }

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

} | Sort-Object DisplayName | Export-CSV -Path $Csvfile -NoTypeInformation -Encoding UTF8

If no max. member count can / should be assumed, consider denormalizing the data by using a single member column combined with creating a separate row for each member, as shown in Santiago's helpful answer, which is unquestionably the better approach for subsequent programmatic processing of the data vs. the multi-column approach you're seeking, which may be simpler to grasp for the human observer.

[1] That is, you need to know how many members a group can have at most. You could even try to determine that count programmatically, ahead of time, but either way the resulting number may be too large to be practical.

user_453750

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
            Member             = $member
        }
    }
} | Sort-Object DisplayName | Export-CSV -Path ....
user_153395440
Related