Splitting selected data and formatting correctly in PowerShell

Question

I have created below PowerShell script so far.

$myEsxis = Get-VMHost | Select Name,DatastoreIdList,ConnectionState -First 5
 $myEsxiHosts = @()
 foreach ($myEsxi in $myEsxis)
 { Try { $obj = New-Object psobject -Property @{
          "ESXiName" = ($myEsxi).Name
          "ClusterName" = (Get-Cluster -VMHost ($myEsxi).Name).Name
          "ConnectionState" = ($myEsxi).ConnectionState
          "PingState" = Test-Connection ($myEsxi).Name -ErrorAction 0 -Quiet -Count 1
          "PhyNIC" = ((Get-EsxCli -VMHost ($myEsxi).Name).network.nic.list() | Select @{ Name = 'NIC';  Expression = {"$($_.Name)/$($_.Speed)/$($_.LinkStatus)"}} ) -split(";")
          "NumVMs" = (Get-VM -Location ($myEsxi).Name).count
          "ConnectedDatastores" = foreach ($obj in ($myEsxi).DatastoreIdList) {Get-Datastore | Where {($_.Id -eq $obj) -and ($_.Name -notlike '*local*')} | Select-Object -ExpandProperty Name}
          "ESXiVersion" = (Get-VMHost -Name ($myEsxi).Name).ExtensionData.Config.Product.FullName
         }
       }

   Catch { $obj = New-Object psobject -Property @{
           "ESXiName" = ($myEsxi).Name
           "ClusterName" = $_.Exception.Message
           "ConnectionState" = $_.Exception.Message
           "PingState" = Test-Connection ($myEsxi).Name -ErrorAction 0 -Quiet -Count 1
           "NumVMs" = $_.Exception.Message
           "PhyNIC" = $_.Exception.Message
           "ConnectedDatastores" = $_.Exception.Message
           "ESXiVersion" = $_.Exception.Message
           }
         }
 $myEsxiHosts += $obj | Sort-Object -Property ESXiName | Select ESXiName,ClusterName,PingState,ConnectionState,NumVMs,@{Name='ConnectedDatastores';Expression={[string]::join(";", ($_.ConnectedDatastores))}},@{Name='PhysicalNIC';Expression={[string]::join(";", ($_.PhyNIC))}},ESXiVersion
 }
 $myEsxiHosts

I have below problems related to it:

1. The column ConnectedDatastores gives continuous output data separated by a semicolon and I have tried Split() in various ways but didn't succeed.
2. The column PhysicalNIC also gives same type of continuous output data with @{ and that also I couldn't eliminate.

I need ConnectedDatastores column to display each item in new line.
I need same with the PhysicalNIC output but without @{

Answer 1

Try this out, it should work:

$myEsxis = Get-VMHost | Select Name,DatastoreIdList,ConnectionState -First 5

# Using Generic List for efficiency
$myEsxiHosts = [system.collections.generic.list[pscustomobject]]::new()

# Wrapping the oneliners inside scriptblocks for readability
$phyNIC={
        ((Get-EsxCli -VMHost $myEsxi.Name).Network.NIC.List() |
        Select @{
            Name = 'NIC'
            Expression = {"$($_.Name)/$($_.Speed)/$($_.LinkStatus)"}
        }).NIC
}

$dataStores={
    foreach ($obj in $myEsxi.DatastoreIdList)
    {
        (Get-Datastore | Where {($_.Id -eq $obj) -and ($_.Name -notlike '*local*')}).Name
    }
}

foreach ($myEsxi in $myEsxis)
{
    Try
    {
        $myEsxiHosts.Add(
            [pscustomobject]@{
                ESXiName = $myEsxi.Name
                ClusterName = (Get-Cluster -VMHost $myEsxi.Name).Name
                ConnectionState = $myEsxi.ConnectionState
                PingState = Test-Connection $myEsxi.Name -ErrorAction 0 -Quiet -Count 1
                PhyNIC = & $phyNIC | Out-String # Executing the scriptblock with '&' and piping the result to Out-String to get our multiline string
                NumVMs = (Get-VM -Location $myEsxi.Name).count
                ConnectedDatastores = & $dataStores | Out-String
                ESXiVersion = (Get-VMHost -Name $myEsxi.Name).ExtensionData.Config.Product.FullName
        })
    }
    Catch
    {
        $myEsxiHosts.Add(
            [pscustomobject]@{
                ESXiName = $myEsxi.Name
                ClusterName = $_.Exception.Message
                ConnectionState = $_.Exception.Message
                PingState = Test-Connection $myEsxi.Name -ErrorAction 0 -Quiet -Count 1
                NumVMs = $_.Exception.Message
                PhyNIC = $_.Exception.Message
                ConnectedDatastores = $_.Exception.Message
                ESXiVersion = $_.Exception.Message
        })
    }
}

$myEsxiHosts | Sort-Object -Property ESXiName 

Edit: I might be wrong, but most of the times when I see this question, it is related to how the data is exported (CSV, Xlsx, etc). The problem of having an array property is that when we want to export the object, instead of displaying the contents of said property you will get the type as displayed below. There are several workarounds for this, i.e. -join if you want a single string with all the values and Out-String if you want your array to be displayed as multiline string.

There are a lot better explanation than I can give you around the internet, i.e.:

• Getting "System.Collections.Generic.List`1[System.String]" in CSV File export when data is okay on screen
• https://www.millersystems.com/powershell-exporting-multi-valued-attributes-via-export-csv-cmdlet/

Array Property:

Array converted to multiline String:

Edit:

If the object is converted to HTML, instead of using Out-String you need to use -join '<br>' and then after the object is converted to HTML you need to replace '<br>' for '<br>'. This is very well explained here: Break lines in powershell array and then convert it to html?

As a test example you can try this:

(@(
[PSCustomObject]@{
    Name = 'Value'
    Arr = 1,2,3,4,5,6|Out-String
}

[PSCustomObject]@{
    Name = 'Value2'
    Arr = 1,2,3,4,5,6|Out-String
}

[PSCustomObject]@{
    Name = 'Value'
    Arr = 1,2,3,4,5,6 -join "<br>"
}

[PSCustomObject]@{
    Name = 'Value2'
    Arr = 1,2,3,4,5,6 -join "<br>"
})|ConvertTo-Html) -replace '&lt;br&gt;','<br>' > ./test.html

Which will look like this:

Answer 2

@santiago-squarzon helped me to remove @, { and ; from the report.
However, I was able to break the line using CSS white-space: break-spaces;.