PowerShell - Add variables with multiple values to CSV column-CodePudding

I have a weird scenario where I am trying to populate a .CSV file using PowerShell, but it's not a traditional .CSV file. I want to have two columns with multiple values in a single cell.

Desired CSV Out:

Team Name           Team Users           Team Access
Team 1              User 1               App 1
                    User 2               App 2
                    User 3               App 3 
Team 2              User 4               App 1
                    User 5               App 2
                    User 6               App 3

So essentially, for the above, I would only want two rows with all that information.

When I try to use Export-CSV, with defined variables for the Users and Apps, my CSV looks like this:

Team Name           Team Users           Team Access
Team 1              System.Object[]      System.Object[] 
Team 2              System.Object[]      System.Object[]

So I'm wondering how I can either take the object values as literal values or use something like Add-Content / Add-Member to add values based on the CSV headers?

This is my current code:

$RBAC_Groups = Get-ADGroup -Filter {Name -like "RBAC*"} -SearchBase "DC=example,DC=com"


ForEach ($Group in $RBAC_Groups) {
    
    #Get AD Group Members
    $Users = Get-ADGroupMember -Identity $Group.Name

    #Get AD Group Member Of 
    $Membership = Get-ADPrincipalGroupMembership $Group.Name

    $Final = New-Object psobject

    $Final | Add-Member -MemberType NoteProperty -Name "RBAC Group" -Value $Group.Name
    $Final | Add-Member -MemberType NoteProperty -Name "RBAC Users" -Value $Users.Name
    $Final | Add-Member -MemberType NoteProperty -Name "RBAC Access" -Value $Membership.Name

    $Final | Export-Csv -Path C:\Temp\RBAC_Weekly_Audit.csv -Append -NoTypeInformation
}

Thanks,

CodePudding user response：

You just need to convert the array properties Team Users and Team Access into multi-line strings (being arrays is also why you see System.Object[] when you export your object).

How can to convert the array into multi-line string?

$array -join [environment]::NewLine
$array -join "`r`n"
($array | Out-String).TrimEnd()

And others...

$RBAC_Groups = Get-ADGroup -Filter "Name -like 'RBAC*'" -SearchBase "DC=example,DC=com"
$export = foreach($group in $RBAC_Groups)
{
    $members = @(Get-ADGroupMember -Identity $Group.Name).Where({
        $_.ObjectClass -eq 'user'
    })
    $principals = Get-ADPrincipalGroupMembership $Group.Name

    [pscustomobject]@{
        'RBAC Group'  = $group.Name
        'RBAC Users'  = $members -join [System.Environment]::NewLine
        'RBAC Access' = $principals -join [System.Environment]::NewLine
    }
}
$export | Export-Csv -Path C:\Temp\RBAC_Weekly_Audit.csv -NoTypeInformation

Using script block on the -Filter parameter for the AD cmdlets is not recommended and can bring problems.

How do I know if my property is an array?

When displaying the object in the console the values of your array property will be displayed between brackets {...}. .GetType() method and Get-Member can be used too to identify them:

PS /> $obj[0]

Team Name Team Users               Team Access
--------- ----------               -----------
Team 1    {User 1, User 2, User 3} {App 1, App 2, App 3}

PS /> $obj[0].'Team Users'.GetType()

IsPublic IsSerial Name             BaseType
-------- -------- ----             --------
True     True     Object[]         System.Array

PS /> $obj[0] | Get-Member -MemberType Properties

   TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition
----        ----------   ----------
Team Access NoteProperty Object[] Team Access=System.Object[]
Team Name   NoteProperty string Team Name=Team 1
Team Users  NoteProperty Object[] Team Users=System.Object[]