Unable to export custom objects to CSV in powershell

Question

I have written powershell scirpt to generate IIS information to CSV.

$bindingip = Get-WmiObject -Namespace root\webadministration -Class sslbinding2 | Select-Object - 
Property PSComputerName,IPAddress
$sitedetails = Get-Website | Select-Object -Property Name,ID,State
$report = New-Object psobject
$report | Add-Member -MemberType NoteProperty -Name "Hostname" -Value $bindingip.PSComputerName
$report | Add-Member -MemberType NoteProperty -Name "IPAddress" -Value $bindingip.IPAddress
$report | Add-Member -MemberType NoteProperty -Name "Site Name" -Value $sitedetails.name
$report | Add-Member -MemberType NoteProperty -Name "ID" -Value $sitedetails.id
$report | Add-Member -MemberType NoteProperty -Name "State" -Value $sitedetails.state
$report | Export-Csv C:\content.csv -NoTypeInformation

Output of CSV:

Hostname         IPAddress        Site Name       ID               State
System.Object[]  System.Object[]  System.Object[] System.Object[]  System.Object[]

Do i need to add anything to the code to get exact output, Can anyone help on this.

Answer 1

As Abraham and Ash pointed out in their comments, the values of the properties on your variables $bindingip and $sitedetails are an array instead of a string. This is why when you export the report you get the object type instead of it's actual value.

You could see this by doing for example this:

$bindingip.PSComputerName.GetType()

Which would return something like this:

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

However if you select just the first element on the array PSComputerName

$bindingip.PSComputerName[0].GetType()

You would see:

IsPublic IsSerial Name        BaseType     
-------- -------- ----        --------     
True     True     String      System.Object

A workaround for this, is to either convert the values to a multiline string by the use of Out-String or by joining the elements of the arrays with a delimiter i.e. -join '//'.

$bindingip = Get-WmiObject -Namespace root\webadministration -Class sslbinding2
$sitedetails = Get-Website

# Like this (thanks mklement0):
$report = [pscustomobject]@{
    Hostname = "$($bindingip.PSComputerName)"
    IPAddress = "$($bindingip.IPAddress)"
    'Site Name' = "$($sitedetails.name)"
    ID = "$($sitedetails.id)"
    State = "$($sitedetails.state)"
}

# Or like this:
$report = [pscustomobject]@{
    Hostname = ($bindingip.PSComputerName | Out-String).trim()
    IPAddress = ($bindingip.IPAddress | Out-String).trim()
    'Site Name' = ($sitedetails.name | Out-String).trim()
    ID = ($sitedetails.id | Out-String).trim()
    State = ($sitedetails.state | Out-String).trim()
}

# Or like this:
$report = [pscustomobject]@{
    Hostname = $bindingip.PSComputerName -join '//'
    IPAddress = $bindingip.IPAddress -join '//'
    'Site Name' = $sitedetails.name -join '//'
    ID = $sitedetails.id -join '//'
    State = $sitedetails.state -join '//'
}

$report | Export-Csv C:\content.csv -NoTypeInformation

Edit

This can also work, which is what I think mklement0 suggested. It will create a new object for each element on the values of the properties:

$compName = $bindingip.PSComputerName
$ipAddr = $bindingip.IPAddress
$name = $sitedetails.name
$id = $sitedetails.id
$state = $sitedetails.state

$top = ($compName.Count,$ipAddr.Count,$name.Count,$id.Count,$state.Count | Measure-Object -Maximum).Maximum

$report = for($i = 0;$i -lt $top;$i++)
{
    [pscustomobject]@{
        Hostname = $compName[$i]
        IPAddress = $ipAddr[$i]
        'Site Name' = $name[$i]
        ID = $id[$i]
        State = $state[$i]
}

$report | Export-Csv...

Answer 2

In addition to Santiago's very thorough and excellent answer, it appears to me that we are just combining the objects of one array with another to produce all possible combinations. I might do something like the following to accomplish this.

$bindingip = @(
    [PSCustomObject]@{
        PSComputerName = 'Public1'
        IPAddress      = '192.168.0.1'
    },
    [PSCustomObject]@{
        PSComputerName = 'Public1'
        IPAddress      = '127.0.0.1'
    }
)

$siteDetails = @(
    [PSCustomObject]@{
        Name  = 'site 1'
        Id    = 'site1'
        State = 'up'
    },
    [PSCustomObject]@{
        Name  = 'site 2'
        Id    = 'site2'
        State = 'down'
    }
)    

$combined = foreach ($ip in $bindingip) {
    foreach ($details in $siteDetails) {
        $out = [ordered]@{}
        $ip.psobject.properties | ForEach-Object {
            $out[$_.Name] = $_.Value
        }
        $details.psobject.properties | ForEach-Object {
            $out[$_.Name] = $_.Value
        }

        [pscustomobject]$out
    }
}

$combined | Format-Table

Output

PSComputerName IPAddress   Name   Id    State
-------------- ---------   ----   --    -----
Public1        192.168.0.1 site 1 site1 up
Public1        192.168.0.1 site 2 site2 down
Public1        127.0.0.1   site 1 site1 up
Public1        127.0.0.1   site 2 site2 down

One might wrap this in a function for reusability

function Combine-ObjectsFromTwoArrays {
    param (
        [array]$array1,
        [array]$array2
    )

    foreach ($obj1 in $array1) {
        foreach ($obj2  in $array2) {
            $out = [ordered]@{}
            $obj1.psobject.properties | ForEach-Object {
                $out[$_.Name] = $_.Value
            }
            $obj2.psobject.properties | ForEach-Object {
                $out[$_.Name] = $_.Value
            }

            [pscustomobject]$out
        }
    }
}