Parse Invoke-RestMethod output to csv file

Question

I need to parse the output from an Invoke-RestMethod to extract elements into separate columns in a csv file so that I can import into a database. Any help would be appreciated.

Tried foreach and split and regular expressions

$trusted_facts = @{
query=’["from", "facts",
    ["extract", ["certname", "environment", "value"],
      ["=", "name", "trusted"]
    ]
  ]’
}

$jsonbody = $trusted_facts | ConvertTo-Json

$response = Invoke-RestMethod -Method POST -Uri $uri -Headers $headers - 
Body $jsonbody -ContentType 'application/JSON'

$response.value | Format-List

$response.value | Export-Csv -Path $csvFileName -NoTypeInformation

This is the output when I pipe it to Format-List

domain        : prci.com
certname      : xobqpupm.prci.com
hostname      : xobqpupm
extensions    : 
authenticated : remote

domain        : proghszq.com
certname      : scpupt03.proghszq.com
hostname      : scpupt03
extensions    : 
authenticated : remote

domain        : proghszq.com
certname      : scpupq13.proghszq.com
hostname      : scpupq13
extensions    : @{pp_role=test_server; pp_apptier=development; pp_project=corporate; pp_department=puppet_common}
authenticated : remote

domain        : proghszq.com
certname      : scchocot01.proghszq.com
hostname      : scchocot01
extensions    : @{pp_role=chocotest; pp_apptier=production; pp_project=puppet_ets; pp_department=compute}
authenticated : remote

This is the output when I pipe to Export-CSv

"prci.com","xobqpupm.prci.com","xobqpupm","","remote"
"proghszq.com","scpupt03.proghszq.com","scpupt03","","remote"
"proghszq.com","scpupq13.proghszq.com","scpupq13","@{pp_role=test_server; pp_apptier=development; pp_project=corporate; pp_department=puppet_common}","remote"
"proghszq.com","scchocot01.proghszq.com","scchocot01","@{pp_role=chocotest; pp_apptier=production; pp_project=puppet_ets; pp_department=compute}","remote"

This is the output I desire (ie. when extensions is populated extract the elements and put in separate columns)

"proghszq.com","scpupq13.proghszq.com","scpupq13","test_server","development","corporate","puppet_common"
"proghszq.com","scchocot01.proghszq.com","scchocot01","chocotest","production","puppet_ets","compute"

JosefZ · Accepted Answer · 2019-06-11 21:34:49Z

Export-Csv: converting to a CSV file assumes the same count of columns in all input. Use calculated properties:

$response.value | Select-Object -Property *,
    @{ n='pp_role';
       e={ if ('pp_role' -in $_.extensions.psobject.Properties.name) 
                {$_.extensions.pp_role} else {''} }},
    @{ n='pp_apptier';
       e={ if ('pp_apptier' -in $_.extensions.psobject.Properties.name) 
                {$_.extensions.pp_apptier} else {''} }},
    @{ n='pp_project';
       e={ if ('pp_project' -in $_.extensions.psobject.Properties.name) 
                {$_.extensions.pp_project} else {''} }},
    @{ n='pp_department';
       e={ if ('pp_department' -in $_.extensions.psobject.Properties.name) 
                {$_.extensions.pp_department} else {''} }}

Tested for $aux instead of $response.value where $aux is defined as follows:

$aux = @(
    [PSCustomObject]@{
        domain        = 'A.proghszq.com'
        certname      = 'A.scpupq13.proghszq.com'
        hostname      = 'A.scpupq13'
        extensions    = ''
        authenticated = 'A.remote'
    },
    [PSCustomObject]@{
        domain        = 'proghszq.com'
        certname      = 'scpupq13.proghszq.com'
        hostname      = 'scpupq13'
        extensions    = [PSCustomObject]@{ 
                            pp_role      ='test_server'
                            pp_apptier   ='development'
                            pp_project   ='corporate'
                            pp_department='puppet_common'
                        }
        authenticated = 'remote'
    }
)

Output:

domain        : A.proghszq.com
certname      : A.scpupq13.proghszq.com
hostname      : A.scpupq13
extensions    : 
authenticated : A.remote
pp_role       : 
pp_apptier    : 
pp_project    : 
pp_department : 

domain        : proghszq.com
certname      : scpupq13.proghszq.com
hostname      : scpupq13
extensions    : @{pp_role=test_server; pp_apptier=development; pp_project=corporate; pp_department=puppet_common}
authenticated : remote
pp_role       : test_server
pp_apptier    : development
pp_project    : corporate
pp_department : puppet_common

Robert Dyjas · Accepted Answer · 2019-06-12 09:03:38Z

0

Here's another approach that filters out the responses that do not have entries in the extensions field:

$aux | where {($_.extensions -ne $null) -and ($_.extensions -ne '')} |
    select domain, certname, hostname, 
    @{n='pp_role';e={$_.extensions.pp_role}},
    @{n='pp_apptier';e={$_.extensions.pp_apptier}}, 
    @{n='pp_project';e={$_.extensions.pp_project}},
    @{n='pp_department';e={$_.extensions.pp_department}} |
      ConvertTo-Csv -NoTypeInformation

edited Jun 12, 2019 at 9:03

Robert Dyjas

5,2373 gold badges22 silver badges35 bronze badges

answered Jun 11, 2019 at 22:13

jbsmith

1,66613 silver badges11 bronze badges

1 Comment

abrahavt Over a year ago

This solution worked. Only change I had to make was having to look at specific fields in the extensions. eg. ($_.extensions.pp_role -ne $null) . Thanks

Collectives™ on Stack Overflow

Parse Invoke-RestMethod output to csv file

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related