Search code examples
powershellselect-object

Powershell Expand System.Object[] Value and ConvertTo-Csv

I'm making a script which made a RestMethod call and export results to CSV On the output I have an object which contain the agentstatus and the last agentstatusmessage but for the last one i saw only System.Object[] How could i expand the value?

Below a snippet of simple code and an example of the output

$r = Invoke-RestMethod @param -Headers $headers -UseBasicParsing

$r.computers | Select-Object hostName,lastIPUsed,platform,computerStatus | ConvertTo-Csv -NoTypeInformation

Output:

"hostName","lastIPUsed","platform","computerStatus"

"srv01","10.1.1.2","Red Hat Enterprise 7 (64 bit)","@{agentStatus=active; agentStatusMessages=System.Object[]}"
"srv02","10.1.2.3","CentOS 7 (64 bit)","@{agentStatus=active; agentStatusMessages=System.Object[]}"
"srv03","10.1.2.4","Microsoft Windows Server 2019 (64 bit)","@{agentStatus=active; agentStatusMessages=System.Object[]}"

Expected:

"hostName","lastIPUsed","platform","AgentStatus","agentStatusMessages"

"srv01","10.1.1.2","Red Hat Enterprise 7 (64 bit)","active","Managed (Online)"
"srv02","10.1.2.3","CentOS 7 (64 bit)","active","Managed (Online)"
"srv03","10.1.2.4","Microsoft Windows Server 2019 (64 bit)","active","Managed (Online)"
Solution

  • Use calculated properties, one to create a separate agentStatus property (CSV column), and another to create an agentStatusMessages property, which additionally requires explicitly stringifying the array contained in the original property:

    $r.computers | 
  Select-Object hostName, lastIPUsed, platform,
                @{ Name='agentStatus'; Expression={ $_.computerStatus .agentStatus } },
                @{ Name='agentStatusMessages'; Expression={ $_.computerStatus.agentStatusMessages -join ';' } } | 
  ConvertTo-Csv -NoTypeInformation

    Note:

    • Because neither ConvertTo-Csv nor its file-based cousin, Export-Csv, meaningfully stringify array-valued properties, you must decide on how to represent arrays as a single string.

    • The above uses -join, the string joining operator, to form a single string from the array-valued .agentStatusMessages property, using self-chosen separator ; - adjust as needed; if there's only one element in a given array, the separator doesn't come into play.

    • Also, since .computerStatus.agentStatusMessages is of type System.Object[] ([object[]], in PowerShell terms) rather than an array of strings, applying -join relies on the implicit stringification of each element, which in essence means calling .ToString() on each element that isn't already a string.
      If the elements are complex objects rather than strings and their .ToString() stringification is unhelpful, you must explicitly stringify them, such as by choosing a representative / the property of interest:
      E.g., speaking hypothetically, if the elements of the .computerStatus.agentStatusMessages array had a .Text property, you could rely on member-access enumeration to extract the values and join them:
      $_.computerStatus.agentStatusMessages.Text -join ';'