Unable to export system.object in a readable form using PowerShell-CodePudding

I've looked through other answers and I am still struggling. I have some data that comes from a RESTAPI and I get that data using Invoke-RestMethod so it is already converted from the returned JSON

$scans = Invoke-RestMethod -URI "https://<url>/api/v1/<api key>/scans"

I get back an object with two fields

Message:
Data:

$Scans.Data contains a hash table converted from the JSON output with each entry in the table having the following key:value pairs

ScanID
Username
Targets
Name

This is the output of $scans | Get-Member

$scans | gm

   TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition
----        ----------   ----------
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()
GetType     Method       type GetType()
ToString    Method       string ToString()
name        NoteProperty string name=<redacted>
scan_id     NoteProperty string scan_id=<redacted>
targets     NoteProperty Object[] targets=System.Object[]
user_name   NoteProperty string user_name=<redacted>

I need to export all data into a CSV, but $scan.data.targets only shows as System.Object[]

I can't use -expandproperty because I am selecting multiple fields.

How do I convert $scans.data.targets into a readable form for the export, whilst keeping it linked with the other fields for that entry?

something equivalent to:

$scans.data | export-CSV <filepath>

but exporting in a format that is readable when I open the CSV

CodePudding user response：

As commented, you could opt for a combined field of targets in the output:

# demo
$scan = [PsCustomObject]@{data = [PsCustomObject]@{name = 'blah'; scan_id = 123; targets = 'abc','def','ghi'; user_name = 'ItIsMe'}}

$scan.data | Select-Object *, @{Name = 'targets'; Expression = {$_.targets -join '; '}} -ExcludeProperty targets | 
             Export-Csv -Path 'D:\Test\blah.csv' -NoTypeInformation

which (when displayed in the console) looks like this:

name scan_id user_name targets      
---- ------- --------- -------      
blah     123 ItIsMe    abc; def; ghi

Or create output where every target gets its own row in the CSV:

$result = foreach ($item in $scan.data) {
    foreach ($target in $item.targets) {
        [PsCustomObject]@{
            Name     = $item.name
            ScanID   = $item.scan_id
            Target   = $target
            UserName = $item.user_name
        }
    }
}

# output on screen
$result

# output to CSV file
$result | Export-Csv -Path 'D:\Test\blah.csv' -NoTypeInformation

which gives you this:

Name ScanID Target UserName
---- ------ ------ --------
blah    123 abc    ItIsMe  
blah    123 def    ItIsMe  
blah    123 ghi    ItIsMe