Working with API results in PowerShell

PowerShell Help
1

So I definitely still consider myself a beginner when it comes to PowerShell but I like to dive right in. I’m sure this has been asked before but I guess I don’t know enough to search for the right thing. I usually am able to find something someone has written and modify it to my needs but not this time. So lets get into what my question is… so initial info…

$lmiServ = "a.website.com"
$baseURL = "/public-api"
$companyID = "*private*"
$PSK = "*privatePSK*"
$header=@{"Authorization" = "Basic "+[system.convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes("$companyID" + ":" + "$PSK"))}
$url = "https://" + $lmiServ + $baseurl
$urlfield = $url + "/v1/hosts/custom-fields"
$type = "application/json"
$field = Invoke-RestMethod -uri $urlfield -Headers $header -Method get -ContentType $type

This is pretty much as far as I’ve gotten. I can get the results but I’m lost on how to work the results in meaningful way.

I can use $field.hosts and get results to the host screen but I can’t export/save them to a file or anything else.

an example of the results of $field.hosts

PS C:\Users\use> $field.hosts 
    id metadata                                                                                                                                   
    -- --------

111 {@{categoryId=4109c230-a90a-4b2c-a0c6-ae6b33e76ec1; valueId=9e05ac7c-0aa5-4521-bc15-b96044b52639}, @{categoryId=80955841-ea5c-4d12-b32e-…
112 {@{categoryId=4109c230-a90a-4b2c-a0c6-ae6b33e76ec1; valueId=b7dae04d-3792-49aa-b393-f5a36341f94f}, @{categoryId=83d42353-f657-47ce-9a9b-…
113 {@{categoryId=4109c230-a90a-4b2c-a0c6-ae6b33e76ec1; valueId=b47d11e8-46e5-4545-bdc1-7d232579de56}, @{categoryId=80955841-ea5c-4d12-b32e-…

I used

$test = Invoke-WebRequest -uri $urlfield -Headers $header -Method get -ContentType $type
$test | export-Clixml c:\test.xml
# to get 
<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>Microsoft.PowerShell.Commands.HtmlWebResponseObject</T>
      <T>Microsoft.PowerShell.Commands.WebResponseObject</T>
      <T>System.Object</T>
    </TN>
    <ToString>{"hosts":[{"id":111,"metadata":[{"categoryId":"4109c230-a90a-4b2c-a0c6-ae6b33e76ec1","valueId":"9e05ac7c-0aa5-4521-bc15-b96044b52639"},{"categoryId":"80955841-ea5c-4d12-b32e-af8c929ce00c","valueId":"53ea4b9e-81e6-4f8b-bf52-89c7af4a3b29"},{"categoryId":"83d42353-f657-47ce-9a9b-b492682186e4","valueId":"fe1cc1b1-bce8-48bb-a54c-2a67d19dccb8"}]},{"id":112,"metadata":[{"categoryId":"4109c230-a90a-4b2c-a0c6-ae6b33e76ec1","valueId":"b7dae04d-3792-49aa-b393-f5a36341f94f"},{"categoryId":"83d42353-f657-47ce-9a9b-b492682186e4","valueId":"71bb28bf-ce7f-40e3-866b-89c7d07978b0"}]},{"id":113,"metadata":[{"categoryId":"4109c230-a90a-4b2c-a0c6-ae6b33e76ec1","valueId":"b47d11e8-46e5-4545-bdc1-7d232579de56"},{"categoryId":"80955841-ea5c-4d12-b32e-af8c929ce00c","valueId":"9e0c057d-dbff-43ec-96c6-de5ac2b5cc24"},{"categoryId":"83d42353-f657-47ce-9a9b-b492682186e4","valueId":"863f6925-5768-4479-a88c-a17da358c1a5"}]},...

That should give you an idea of what it looks like.

In the end I’ll have to compare valueId info with another api result and then eventually export that valueId based on the overall ID to a new csv property/column of the associated ID. For right now I am just trying to figure out how to convert this to a usable format. Hope I made sense… Off to sleep.

2

Not really sure how you are going to compare the result. But you can use Import-CliXML cmdlet to read the exported object.
Can you elaborate the problem a little more ?

3

I’m not sure how I’m going to get there yet either. One step at a time. Right now I can’t work with the metadata. From what I read I think I need to convert metadata to a string. I’ve seen examples of how to do that if you are populating the pscustomobject yourself but not if you are populating it from another source(api).

Right now I can do

PS H:\> $field.hosts -match 1029836669

        id metadata                                                                                           
        -- --------                                                                                           
1029836669 {@{categoryId=4109c230-a90a-4b2c-a0c6-ae6b33e76ec1; valueId=d3493b32-c47b-4f6f-84a7-72e6fba7a537...

But if I try the same on a categoryId or ValueId, it returns nothing.

4

When you are using Invoke-RestMethod, Powershell will convert the JSON into a Powershell object. JSON is nested data, so the assumption is that you want to flatten the data. You have one id that has many metadata. If you want to make it flat, you could do something like this:

$flattened = foreach ($field in $fields) {
    foreach ($md in $field.metadata) {
        [pscustomobject]@{
            id         = $field.id
            categoryId = $md.categoryId
            valueId    = $md.valueId
        }
    }
}
5

Seems like it would be

$field.hosts.id

$field.hosts.metadata.categoryid
6

Thanks Rob, That is what I was looking for

7

Another method worth mentioning is calculated expressions based on dot notation that Doug suggested

$flattened = $fields |
             Select-Object -Property @{Name='id';Expression={$_.hosts.id}},
                                     @{Name='categoryId';Expression={$_.metadata.categoryId}},
                                     @{Name='valueId';Expression={$_.metadata.valueId}}
8

No no no, yours was much better. :slight_smile:

9

Thanks to both of you. That got me over the hump to a working script. I’m sure it could be written better but it does the job.

Here is the rest of it if you were curious.

$flatField = foreach ($f1 in $field2.hosts) {
    foreach ($f2 in $f1.metadata){
        [pscustomobject]@{
            id = $f1.id
            categoryId = $f2.categoryId
            ValueId = $f2.valueId
        }
    }
}

$flatCat = foreach ($c1 in $cat2.metadataCategories){
    foreach ($c2 in $c1.values){
        [pscustomobject]@{
            id = $c1.id
            name = $c1.name
            vId = $c2.id
            vname = $c2.name
        }
    }
}

import-csv $csvWOName -Header Computername,IMEI,SIM,Number | ForEach-Object {
    $a = $_
    $idNum = $host2.hosts -match $_.Computername | Select-Object -ExpandProperty id
    $fieldval = $flatField -match $idNum -match "80955841-ea5c-4d12-b32e-af8c929ce00c" | Select-Object -ExpandProperty ValueId
    $catval = $flatCat -match $fieldval | Select-Object -ExpandProperty vname
    $_ | Select-Object -Property @{name='Computername';expression={$a.Computername}},
                                @{name='IMEI';expression={$a.IMEI}},
                                @{name='SIM';expression={$a.SIM}},
                                @{name='Number';expression={$a.Number}},
                                @{name='Username';expression={$catval}} |export-csv $csvFinal -NoTypeInformation -append
}