Help with formatting result

Welcome Forums General PowerShell Q&A Help with formatting result

This topic contains 13 replies, has 3 voices, and was last updated by

 
Participant
4 weeks ago.

  • Author
    Posts
  • #122330

    Participant
    Points: 45
    Rank: Member

    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.

  • #122336

    Participant
    Points: 190
    Helping Hand
    Rank: Participant

    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.

    • #122342

      Participant
      Points: 45
      Rank: Member

      Yes.

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

    Participant
    Points: 304
    Helping Hand
    Rank: Contributor

    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.

  • #122345

    Participant
    Points: 45
    Rank: Member

    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).

    Last Seen on Site
    CI Name
    Service Tag
    Disk space
    Model
    I think that i can still use some of your code, i'm just trying to work it in with mine now.
  • #122403

    Participant
    Points: 190
    Helping Hand
    Rank: Participant

    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.

    • #122565

      Participant
      Points: 45
      Rank: Member

      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)
  • #122583

    Participant
    Points: 304
    Helping Hand
    Rank: Contributor

    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)
    
  • #122597

    Participant
    Points: 190
    Helping Hand
    Rank: Participant

    @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.

  • #122604

    Participant
    Points: 45
    Rank: Member

    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]
    }

     

     

  • #122628

    Participant
    Points: 190
    Helping Hand
    Rank: Participant

    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.

  • #122639

    Participant
    Points: 304
    Helping Hand
    Rank: Contributor

    @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 --
    
  • #122733

    Participant
    Points: 190
    Helping Hand
    Rank: Participant

    @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.

  • #124241

    Participant
    Points: 45
    Rank: Member

    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.

You must be logged in to reply to this topic.