Help with formatting result

[gazza]

Hi Guys, I’m having a bit of trouble trying to get the output that I want.

I’ve created a function that does an API call and I am trying to gets its results in a format where I get the field name and field value in a table (like what you see when you do get-process (Name on left, Value on the right)

Below is the section on my script that is putting all the results into a var for me.

end{
$Result=""|Select-Object-Property JSONresult, StatusCode, Status, Message, FieldNames, FieldValues, Test
$Result.JSONresult=$JSONresult
$Result.StatusCode=$JSONresult.API.response.operation.result.statuscode
$Result.Status=$JSONresult.API.response.operation.result.status
$Result.Message=$JSONresult.API.response.operation.result.Message
$Result.FieldNames=$JSONresult.api.response.operation.details.'field-names'.name.content
$Result.FieldValues=$JSONresult.api.response.operation.Details.'field-values'.record.value
$Result.Test=@{Field=$JSONresult.api.response.operation.details.'field-names'.name.content;Answer=$JSONresult.api.response.operation.Details.'field-values'.record.value}

return$Result
}

$Result.FieldNames, $Result.FieldValues both give me the required content, but as the API is returning results in a different order each time, I want to match up the field name with the value when the result is collected.

The closest I can get is this

$Result.Test=@{Field=$JSONresult.api.response.operation.details.'field-names'.name.content;Answer=$JSONresult.api.response.operation.Details.'field-values'.record.value}

But that gives me this…

 

Name    Value
----    -----
Field   {CI Name, Model, Service Tag, Last Seen on Site}
Answer  {computername.domain.local, Surface Book, 16264754655157, AU-NSW}

What i want to see is something like this

Name      Value
----      -----
CI Name   computername.domain.local
Model     Surface Book

So if i do something like this

$Var = Run-Function -params etc

I can get the info like this.

$var.StatusCode

and can loop through results etc

Foreach($a in $Var.Test) {

$a.name (do something)

$a.value (do something)

}

Can someone please help?

Also, does anyone else have problems with this site? I can’t see my replies and edits, and when I try and reply\save again it says that I have already posted that content. If I modify my reply (just so it doesn’t match) it says that it’s uploaded, but, again I can’t see the replies (I don’t think anyone else can either). I was hoping the last update would have fixed it but I still get these problems.

[fredrik-kacsmarck]

Just a question, have you used ‘convertfrom-json’ somewhere not in the code above?
Should be a lot easier to navigate if the content of the result is in object format rather than raw text/json.

[gazza]

Yes.

$JSONresult = Invoke-WebRequest-Uri $URI-Method $METHOD-Body $postparams-UseBasicParsing | ConvertFrom-Json

[rob-simmers]

If that is how the API stores data, it is absolutely a horrible implementation. A lot can go wrong storing data in an index when it’s parsed because the sort order isn’t guaranteed. It’s bit complicated, but to parse it, you would need to do something like this:

$jsonResult = @()
$jsonResult += [pscustomobject]@{
    'StatusCode'='200'
    'Status'='Success'
    'Message'='Powershell is awesome'
    'Field' =  'CI Name', 'Model', 'Service Tag', 'Last Seen on Site'
    'Answer' =  'computername.domain.local', 'Surface Book', 16264754655157, 'AU-NSW'
}

$jsonResult += [pscustomobject]@{
    'StatusCode'='200'
    'Status'='Success'
    'Message'='Powershell is awesome'
    'Field' =  'CI Name', 'Model', 'Service Tag', 'Last Seen on Site'
    'Answer' =  'computername1.domain.local', 'Another Book', 16264754655157, 'EA-NSW'
}

$jsonResult += [pscustomobject]@{
    'StatusCode'='200'
    'Status'='Success'
    'Message'='Powershell is awesome'
    'Field' =  'CI Name', 'Model', 'Service Tag', 'Last Seen on Site', 'Not in other records'
    'Answer' =  'computername2.domain.local', 'Laptop', 16264754655157, 'NA-NSW', 'Cow'
}

#Hold all possible properties
$allProperties = @('StatusCode','Status','Message')
$results = foreach ($item in $jsonResult) {
    $fields = $item.Field #$item.api.response.operation.details.'field-names'.name.content
    $values = $item.Answer #$item.api.response.operation.Details.'field-values'.record.value

    #Reset the properties of the current object
    $props = @{}
    $props.Add('StatusCode',$item.StatusCode) #$item.API.response.operation.result.statuscode
    $props.Add('Status',$item.Status) #$item.API.response.operation.result.status
    $props.Add('Message',$item.Message) #$item.response.operation.result.Message

    #Loop thru the index
    for ($i=0;$i -le ($fields.Count - 1);$i++) {
        #Add the current object properties
        $props.Add($fields[$i],$values[$i])
        #Add the object properties
        $allProperties += $item.Field[$i]
    }

    #Create a object for the current item using the properties
    New-Object -TypeName PSObject -Property $props
}

#Get the results and get all objects with all available properties in all items
$results | Select-Object -Property ($allProperties | Select-Object -Unique)

Output:

StatusCode           : 200
Status               : Success
Message              : Powershell is awesome
CI Name              : computername.domain.local
Model                : Surface Book
Service Tag          : 16264754655157
Last Seen on Site    : AU-NSW
Not in other records : 

StatusCode           : 200
Status               : Success
Message              : Powershell is awesome
CI Name              : computername1.domain.local
Model                : Another Book
Service Tag          : 16264754655157
Last Seen on Site    : EA-NSW
Not in other records : 

StatusCode           : 200
Status               : Success
Message              : Powershell is awesome
CI Name              : computername2.domain.local
Model                : Laptop
Service Tag          : 16264754655157
Last Seen on Site    : NA-NSW
Not in other records : Cow

Basically, you should just need to replace the remarked lines with the full API path to be parsed to create the object.

[gazza]

I should have posted what comes back from the API call.

$Response.JSONresult| ConvertTo-Json -Depth 8

{
"API": {
"response": {
"operation": {
"result": {
"message": "Successfully fetched.",
"created-date": "07/11/2018 11:52 AM",
"status": "Success",
"statuscode": 200
},
"Details": {
"field-names": {
"name": [
{
"content": "CI Name",
"type": "String"
},
{
"content": "Model",
"type": "String"
},
{
"content": "Service Tag",
"type": "String"
},
{
"content": "Last Seen on Site",
"type": "String"
}
]
},
"field-values": {
"record": {
"value": [
"computer.domain.local",
"Surface Book",
16474644575157,
"AU-NSW"
]
},
"totalRecords": 1
}
},
"name": "read"
}
},
"version": 1
}
}

When i make my requst in this order, it comes back in the order above so i am trying to link the Field Name with the Field Value so that I can act on it. In this instance, its just a test so i am just pulling back some hardware info, but i am hoping i can use this for things like pulling back all computers on a site (hundreds).

<returnFields>
<name>Last Seen on Site</name>
<name>CI Name</name>
<name>Service Tag</name>
<name>Disk space</name>
<name>Model</name>
</returnFields>

I think that i can still use some of your code, i'm just trying to work it in with mine now.

[fredrik-kacsmarck]

Rob’s answer should work, so what is the problem with it?

He used one of your earlier examples but the solution is basically the same.
You just need to get the field-names and field-values into the $fields and $values variables in the foreach loop.
Since the field name and value are disconnected in the response (whoever thought that was a good idea) you need to go through some hoops to connect them again.

If you don’t want the statuscode etc. in each object just skip the $allProperties part.

[gazza]

I’m still trying to see if I can make it work. But the issue is that I am not getting an object back with 3 objects inside like his example data, but rather I get one object with multiple properties in 2 different locations as in the sample JSON I provided last.

so i get this part once

"API": {
"response": {
"operation": {
"result": {
"message": "Successfully fetched.",
"created-date": "07/11/2018 11:52 AM",
"status": "Success",
"statuscode": 200
},

and i am trying to match this

"field-names": {
"name": [
{
"content": "CI Name",
"type": "String"
},
{
"content": "Model",
"type": "String"
},
{
"content": "Service Tag",
"type": "String"
},
{
"content": "Last Seen on Site",
"type": "String"
}
]

with this

"field-values": {
"record": {
"value": [
"computer.domain.local",
"Surface Book",
16474644575157,
"AU-NSW"
]

while returning one object.

so I can’t do

$results = foreach ($item in $jsonResult)

Because there is only one result.

I thought I could do something like this

foreach($Namein$($JSONresult.api.response.operation.details.'field-names'.name.content))

but that's not giving me what I expected either. Right now I am trying to play around with how Rob was sort of creating 2 objects and then merging them but I haven't worked it out yet. I'm not sure if it will allow me to add an object (with 2 properties Name,Value) into another object that holds the rest of the info. (status, Message, OtherObjectAdded_as_prop etc)

[rob-simmers]

Just remove the outer loop if you are parsing a single record:

$jsonResult = @"
{
"API": {
"response": {
"operation": {
"result": {
"message": "Successfully fetched.",
"created-date": "07/11/2018 11:52 AM",
"status": "Success",
"statuscode": 200
},
"Details": {
"field-names": {
"name": [
{
"content": "CI Name",
"type": "String"
},
{
"content": "Model",
"type": "String"
},
{
"content": "Service Tag",
"type": "String"
},
{
"content": "Last Seen on Site",
"type": "String"
}
]
},
"field-values": {
"record": {
"value": [
"computer.domain.local",
"Surface Book",
16474644575157,
"AU-NSW"
]
},
"totalRecords": 1
}
},
"name": "read"
}
},
"version": 1
}
}
"@

$jsonResult = $jsonResult | ConvertFrom-Json

#Hold all possible properties
$allProperties = @('StatusCode','Status','Message')
$fields = $jsonResult.api.response.operation.details.'field-names'.name.content
$values = $jsonResult.api.response.operation.Details.'field-values'.record.value

#Reset the properties of the current object
$props = @{}
$props.Add('StatusCode',$jsonResult.API.response.operation.result.StatusCode)
$props.Add('Status',$jsonResult.API.response.operation.result.Status)
$props.Add('Message',$jsonResult.API.response.operation.result.Message)

#Loop thru the index
for ($i=0;$i -le ($fields.Count - 1);$i++) {
    #Add the current object properties
    $props.Add($fields[$i],$values[$i])
    #Add the object properties
    $allProperties += $item.Field[$i]
}

#Create a object for the current item using the properties
New-Object -TypeName PSObject -Property $props

#Get the results and get all objects with all available properties in all items
$results | Select-Object -Property ($allProperties | Select-Object -Unique)

[fredrik-kacsmarck]

@Gary,

In this case the problem with using foreach (while you can do it) is that you don’t have an easy way to use an index counter.
The names and values are disconnected, meaning, they don’t have the Key matching the Value.
To combine these it’s easier to use a for-loop since you have the counter built into the loop.
If you go with foreach you need to check the index number with .IndexOf() and thats clumsier than just use a for-loop to begin with.

I think this is as simple as I can make it, based on your example.
Just to display your example data in the way I think you want it.

This doesn’t take into account if you have multiple items comming or if the number of fields/values changes.
Then you need to use for-loops (or foreach if you want to go through with the extra steps described above).

$json = @"
{
"API": {
"response": {
"operation": {
"result": {
"message": "Successfully fetched.",
"created-date": "07/11/2018 11:52 AM",
"status": "Success",
"statuscode": 200
},
"Details": {
"field-names": {
"name": [
{
"content": "CI Name",
"type": "String"
},
{
"content": "Model",
"type": "String"
},
{
"content": "Service Tag",
"type": "String"
},
{
"content": "Last Seen on Site",
"type": "String"
}
]
},
"field-values": {
"record": {
"value": [
"computer.domain.local",
"Surface Book",
16474644575157,
"AU-NSW"
]
},
"totalRecords": 1
}
},
"name": "read"
}
},
"version": 1
}
}
"@

$data = $json | convertfrom-json

$names = $data.API.response.operation.Details.'field-names'.name
$values = $data.API.response.operation.Details.'field-values'.record.value

[PSCustomObject]@{
    $names[0].content = $values[0]
    $names[1].content = $values[1]
    $names[2].content = $values[2]
    $names[3].content = $values[3]
}

PSCustomObject is just an accelerator, it’s the same as if you would do ‘New-Object -TypeName PSCustomObject …’ and so forth.

[gazza]

Your right, your example does work. it’s just the API is returning results in such a stupid way.

So when I try and return all Computers for example. I get this output which then breaks it again because there is only one Name.

$Response.fullresult | ConvertTo-Json -Depth 8
{
"API": {
"response": {
"operation": {
"result": {
"message": "Successfully fetched.",
"created-date": "08/11/2018 08:46 AM",
"status": "Success",
"statuscode": 200
},
"Details": {
"field-names": {
"name": {
"content": "CI Name",
"type": "String"
}
},
"field-values": {
"record": [
{
"value": "1Computer.domain.local"
},
{
"value": "2Computer.domain.local"
},
{
"value": "3Computer.domain.local"
},
{
"value": "4Computer.domain.local"
},
{
"value": "5Computer.domain.local"
}
],
"totalRecords": 50
}
},
"name": "read"
}
},
"version": 1
}
}

I tried to add a If statement to fix this, but i’m only getting one result instead of an array. I should be able to work this out though…

#Loop thru the index
for($i=0;$i-le($fields.Count-1);$i++){
#Add the current object properties
if($fields.count-gt1){
$props.Add($fields[$i],$values[$i])
}
else{
$props.Add($fields,$values[$i])
}
#Add the object properties
$allProperties+=$fields[$i]
}

 

 

[fredrik-kacsmarck]

So I’m guessing that your new call to the API just asks for the computer names?

You’re going to have to do some checking yes, to see how many values there are in the ‘field names’ array.
You’re going to have to do even more if you have multiple ‘field names’ as well.

Just again for this particular scenario.
E.g.

$json = @"
{
"API": {
"response": {
"operation": {
"result": {
"message": "Successfully fetched.",
"created-date": "08/11/2018 08:46 AM",
"status": "Success",
"statuscode": 200
},
"Details": {
"field-names": {
"name": {
"content": "CI Name",
"type": "String"
}
},
"field-values": {
"record": [
{
"value": "1Computer.domain.local"
},
{
"value": "2Computer.domain.local"
},
{
"value": "3Computer.domain.local"
},
{
"value": "4Computer.domain.local"
},
{
"value": "5Computer.domain.local"
}
],
"totalRecords": 50
}
},
"name": "read"
}
},
"version": 1
}
}
"@

$data = $json | ConvertFrom-Json

$countNames = ($data.API.response.operation.Details.'field-names'.name | Measure-Object).Count
$countValues = ($data.API.response.operation.Details.'field-values'.record | Measure-Object).Count

$names = $data.API.response.operation.Details.'field-names'.name
$values = $data.API.response.operation.Details.'field-values'.record.value

$result = @()

if($countNames -eq 1)
{
    $nameCount = 0

    for ($i = 0; $i -lt $countValues; $i++)
    { 
        $result += [PSCustomObject]@{
            $names[$nameCount].content = $values[$i]
        }
    }
}
else
{
    # Even more convoluted scenario with nested for-loops.
}

I’m cheating here a bit since there is just 1 field-name to worry about.
But if there are multiple field names you’re going to need to rethink again.
Since then you have one or more Key’s (e.g. CI Name etc.) and one or more values.

It’s doable with e.g. nested for-loops.
One to create the Key’s and the other to populate the Values.

But this is one messed up API
If it were me and it’s not a huge dataset, I would probably use the same call every time no matter what you want from it.
Then sort the pieces you need down the line in Powershell or however you want to consume the data.

[rob-simmers]

@Fredrik-Kacsmarck Without a loop, you cannot dynamically account for the number of properties you could get from the API

The crappy API basically gives you a header row and then you have to glue everything together. There are 3 tests below, the single header + multiple records, multiple headers with a single result, multiple headers with multiple results:

$jsonResult1 = @"
{
"API": {
"response": {
"operation": {
"result": {
"message": "Successfully fetched.",
"created-date": "08/11/2018 08:46 AM",
"status": "Success",
"statuscode": 200
},
"Details": {
"field-names": {
"name": {
"content": "CI Name",
"type": "String"
}
},
"field-values": {
"record": [
{
"value": "1Computer.domain.local"
},
{
"value": "2Computer.domain.local"
},
{
"value": "3Computer.domain.local"
},
{
"value": "4Computer.domain.local"
},
{
"value": "5Computer.domain.local"
}
],
"totalRecords": 50
}
},
"name": "read"
}
},
"version": 1
}
}

"@

$jsonResult2 = @"
{
"API": {
"response": {
"operation": {
"result": {
"message": "Successfully fetched.",
"created-date": "07/11/2018 11:52 AM",
"status": "Success",
"statuscode": 200
},
"Details": {
"field-names": {
"name": [
{
"content": "CI Name",
"type": "String"
},
{
"content": "Model",
"type": "String"
},
{
"content": "Service Tag",
"type": "String"
},
{
"content": "Last Seen on Site",
"type": "String"
}
]
},
"field-values": {
"record": {
"value": [
"computer.domain.local",
"Surface Book",
16474644575157,
"AU-NSW"
]
},
"totalRecords": 1
}
},
"name": "read"
}
},
"version": 1
}
}
"@


$jsonResult3 = @"
{
"API": {
"response": {
"operation": {
"result": {
"message": "Successfully fetched.",
"created-date": "07/11/2018 11:52 AM",
"status": "Success",
"statuscode": 200
},
"Details": {
"field-names": {
"name": [
{
"content": "CI Name",
"type": "String"
},
{
"content": "Model",
"type": "String"
},
{
"content": "Service Tag",
"type": "String"
},
{
"content": "Last Seen on Site",
"type": "String"
}
]
},
"field-values": {
"record": [
{
"value": [
"computer.domain.local",
"Surface Book",
16474644575157,
"AU-NSW"
]
},
{
"value": [
"computer2.domain.local",
"Surface Book2",
44575157164746,
"EA-NSW"
]
}
]
,
"totalRecords": 1
}
},
"name": "read"
}
},
"version": 1
}
}
"@

foreach ($test in @($jsonResult1,$jsonResult2,$jsonResult3)) {
    '-- Start Test --'

$jsonResult = $test | ConvertFrom-Json

#Hold all possible properties
$header = $jsonResult.api.response.operation.details.'field-names'.name.content

$results = foreach ($record in $jsonResult.api.response.operation.Details.'field-values'.record) {
 
    #Reset the properties of the current object
    $props = @{}
    $props.Add('StatusCode',$jsonResult.API.response.operation.result.StatusCode)
    $props.Add('Status',$jsonResult.API.response.operation.result.Status)
    $props.Add('Message',$jsonResult.API.response.operation.result.Message)
    $props.Add('CreatedDate',$jsonResult.API.response.operation.result.'created-date')


    #Loop thru the index
    for ($i=0;$i -lt $header.Count;$i++) {
        #Add the current object properties
        $props.Add($fields[$i],@($record.value)[$i])
    }

    #Create a object for the current item using the properties
    New-Object -TypeName PSObject -Property $props
}


#Get the results and get all objects with all available properties in all items
$results
'-- End Test --'
} #$test

Output:

-- Start Test --


Status      : Success
CreatedDate : 08/11/2018 08:46 AM
Message     : Successfully fetched.
StatusCode  : 200
CI Name     : 1Computer.domain.local

Status      : Success
CreatedDate : 08/11/2018 08:46 AM
Message     : Successfully fetched.
StatusCode  : 200
CI Name     : 2Computer.domain.local

Status      : Success
CreatedDate : 08/11/2018 08:46 AM
Message     : Successfully fetched.
StatusCode  : 200
CI Name     : 3Computer.domain.local

Status      : Success
CreatedDate : 08/11/2018 08:46 AM
Message     : Successfully fetched.
StatusCode  : 200
CI Name     : 4Computer.domain.local

Status      : Success
CreatedDate : 08/11/2018 08:46 AM
Message     : Successfully fetched.
StatusCode  : 200
CI Name     : 5Computer.domain.local

-- End Test --
-- Start Test --
Message           : Successfully fetched.
CreatedDate       : 07/11/2018 11:52 AM
CI Name           : computer.domain.local
Service Tag       : 16474644575157
Model             : Surface Book
Status            : Success
Last Seen on Site : AU-NSW
StatusCode        : 200

-- End Test --
-- Start Test --
Message           : Successfully fetched.
CreatedDate       : 07/11/2018 11:52 AM
CI Name           : computer.domain.local
Service Tag       : 16474644575157
Model             : Surface Book
Status            : Success
Last Seen on Site : AU-NSW
StatusCode        : 200

Message           : Successfully fetched.
CreatedDate       : 07/11/2018 11:52 AM
CI Name           : computer2.domain.local
Service Tag       : 44575157164746
Model             : Surface Book2
Status            : Success
Last Seen on Site : EA-NSW
StatusCode        : 200

-- End Test --

[fredrik-kacsmarck]

@Rob,

Yes, I know you need to run through it with a loop or multiple loops to count for each scenario.
Have already stated that your first example should work, with some modification based on the examples given later.

[gazza]

Hi guys, I haven’t forgotten about this, I’ve just been crazy busy at work and haven’t had a chance to come back to it yet.

I have one of the employees of this application coming to my site today so you can bet I’ll be asking WTF is with there API, so this should be fun

I’ll let you know what they say to that, but I probably won’t be able to get back to this script for a few weeks until some people come back from leave and it calms down here.