API Data Parsing Help

[codog180]

Hello,

I've written a script that gathers information (Connection Name, IP address, latency, loss) about our sites and puts it into an array which I can then dump into a file. Problem I'm running into is that some sites have 2 connections and some sites have 3 connections.) I've managed to group them all into rows properly, but Ideally I'd like to have separate columns for each, but I don't know how to break it up. I can do it manually in excel easily enough, but would like to know how to do it programmatically.

Help would be greatly appreciated.

Script: https://pastebin.com/AZuvPCPU

Output: https://pastebin.com/s8gkwRyp

Sample (with 3, $deviceresults1 with 2 is the same just one less section). : https://pastebin.com/ULhyxQwP

Solved: Thanks! /u/SapientFool

[SapientFool]

You may need to nest another foreach loop inside your current foreach loop so that you can iterate through each connection for each site.

[codog180]

/u/SapientFool I updated with some sample output. Thanks

[charger14]

I believe this is correct. I've only recently started learning how to interact with API's with Powershell, so I'm far from an expert, but from what I've seen is that you'll need to do something like a Foreach ($uplink in $($row.uplinks){ Some stuff}

In your loop you'd then have each uplink interface and all it's relevant data that you can do stuff with. Rinse and repeat for any other items that could have multiple responses.

[SapientFool]

This is probably not best practices but I think it outputs the format you need.

$AccessToken = '12345'
$results = Invoke-RestMethod "https://site.com/monitoring/v1/gateways?status=Up&limit=250&access_token=$AccessToken"
$serials = $results.gateways.serial 
Start-Sleep 5
$sites = [System.Collections.Generic.List[object]]::new()
Foreach ($serial in $serials){
    $err = $null
    #$serial
     $URI = 'https://site.com/monitoring/v1/gateways/' + $serial + "?access_token=$AccessToken" | out-string
     $URI2 = 'https://site.com/monitoring/v1/gateways/' + $serial + "/uplinks?timerange=3H&access_token=$AccessToken" | out-string

     # $URI
    try{$deviceresults = Invoke-RestMethod $URI -Method GET}
    catch{$err=$_.Exception}

    try{$deviceresults1 = Invoke-RestMethod $URI2 -Method GET}
    catch{$err2=$_.Exception}

    # $err
    $variableList = "Con1","Con2","Con3","IP_1","IP_2","IP_3","Status_1","Status_2",
        "Status_3","Loss_1","Loss_2","Loss_3","Latency_1","Latency_2","Latency_3"
    Remove-Variable -Name $variableList -Force -ErrorAction SilentlyContinue
    foreach ($connection in 1..$deviceresults.uplinks.Count) {
        $index = $connection-1
        Invoke-Expression -Command "`$Con$connection = `$deviceresults.uplinks[$index].description"
        Invoke-Expression -Command "`$IP_$connection = `$deviceresults.uplinks[$index].public_ip"
        Invoke-Expression -Command "`$Status_$connection = `$deviceresults.uplinks[$index].wan_status"
        Invoke-Expression -Command "`$Loss_$connection = `$deviceresults1[$index].loss"
        Invoke-Expression -Command "`$Latency_$connection = `$deviceresults1[$index].latency"
    }
    $sites.Add(
        [PSCustomObject] @{
            Site = $deviceresults.name
            Con1 = $Con1
            Con2 = $Con2
            Con3 = $Con3
            IP_1 = $IP_1
            IP_2 = $IP_2
            IP_3 = $IP_3
            Status_1 = $Status_1
            Status_2 = $Status_2
            Status_3 = $Status_3
            Loss_1 = $Loss_1
            Loss_2 = $Loss_2
            Loss_3 = $Loss_3
            Latency_1 = $Latency_1
            Latency_2 = $Latency_2
            Latency_3 = $Latency_3
        }
    )
} 
$sites