Powershell extract data from really weird JSON file

Question

I have a really weird json file and I am trying to extract data from it via powershell.

The issue is that the creator of this file is using json in a way I have never seen before:

(Cities are not in the Array, Continents are not in the array etc)

Shortened JSON File:

{
"zscloud.net": {
    "continent : EMEA": {
        "city : Abu Dhabi I": [
            {
                "range": "147.161.174.0/23",
                "vpn": "",
                "gre": "",
                "hostname": "",
                "latitude": "24.453884",
                "longitude": "54.3773438"
            }
        ],
        "city : Amsterdam II": [
            {
                "range": "185.46.212.0/23",
                "vpn": "amsterdam2-vpn.zscloud.net",
                "gre": "185.46.212.36",
                "hostname": "ams2.sme.zscloud.net",
                "latitude": "52",
                "longitude": "5"
            },
            {
                "range": "147.161.228.0/23",
                "vpn": "",
                "gre": "",
                "hostname": "",
                "latitude": "52",
                "longitude": "5"
            },
            {
                "range": "165.225.240.0/23",
                "vpn": "ams2-2-vpn.zscloud.net",
                "gre": "165.225.240.42",
                "hostname": "ams2-2.sme.zscloud.net",
                "latitude": "52",
                "longitude": "5"
            },
            {
                "range": "147.161.172.0/23",
                "vpn": "",
                "gre": "165.225.240.42",
                "hostname": "",
                "latitude": "52",
                "longitude": "5"
            },
            {
                "range": "147.161.230.0/23",
                "vpn": "",
                "gre": "",
                "hostname": "",
                "latitude": "52",
                "longitude": "5"
            },
            {
                "range": "147.161.232.0/23",
                "vpn": "",
                "gre": "",
                "hostname": "",
                "latitude": "52",
                "longitude": "5"
            },
            {
                "range": "147.161.234.0/23",
                "vpn": "",
                "gre": "",
                "hostname": "",
                "latitude": "52",
                "longitude": "5"
            },
            {
                "range": "147.161.224.0/23",
                "vpn": "",
                "gre": "",
                "hostname": "",
                "latitude": "52",
                "longitude": "5"
            },
            {
                "range": "147.161.226.0/23",
                "vpn": "",
                "gre": "",
                "hostname": "",
                "latitude": "52",
                "longitude": "5"
            }
        ]
    }
}

}

I am trying to get the name of the city + the nested hostname.

My Options:

I can either use Convertfrom-JSON and then somehow iterate over several unknown Keys and Properties.
Or I can use select-string and basically grep only the matching regex. Then put them into array etc.

Previously, I went with select-string against beautified JSON as it was easier.

Today, I have found out that not only Hostname but Hostname + City name may be required. Any idea how to extract them from the json?

I am on PS5.

Many Thanks, Aster

score 1 · Accepted Answer · answered Aug 05 '21 at 10:31

I can either use ConvertFrom-JSON and then somehow iterate over several unknown Keys and Properties.

Let me show you how!

PowerShell allows you to programmatically discover the properties of any object via the psobject hidden memberset:

PS ~> $someObject = [pscustomobject]@{ A = 123; B = "a string value" }
PS ~> $someObject.psobject.Properties


MemberType      : NoteProperty
IsSettable      : True
IsGettable      : True
Value           : 123
TypeNameOfValue : System.Int32
Name            : A
IsInstance      : True

MemberType      : NoteProperty
IsSettable      : True
IsGettable      : True
Value           : a string value
TypeNameOfValue : System.String
Name            : B
IsInstance      : True

Applied to your JSON input, we could do something like this:

# The following assumes you've loaded the JSON into a string like below
$json = @'
{
    "zscloud.net": {
        "continent : EMEA": {
            "city : Abu Dhabi I": [
                {
                    "range": "147.161.174.0/23",
                    "vpn": "",
                    "gre": "",
                    "hostname": "",
                    "latitude": "24.453884",
                    "longitude": "54.3773438"
                }
            ],
            "city : Amsterdam II": [
                {
                    "range": "185.46.212.0/23",
                    "vpn": "amsterdam2-vpn.zscloud.net",
                    "gre": "185.46.212.36",
                    "hostname": "ams2.sme.zscloud.net",
                    "latitude": "52",
                    "longitude": "5"
                },
                {
                    "range": "147.161.228.0/23",
                    "vpn": "",
                    "gre": "",
                    "hostname": "",
                    "latitude": "52",
                    "longitude": "5"
                },
                {
                    "range": "165.225.240.0/23",
                    "vpn": "ams2-2-vpn.zscloud.net",
                    "gre": "165.225.240.42",
                    "hostname": "ams2-2.sme.zscloud.net",
                    "latitude": "52",
                    "longitude": "5"
                },
                {
                    "range": "147.161.172.0/23",
                    "vpn": "",
                    "gre": "165.225.240.42",
                    "hostname": "",
                    "latitude": "52",
                    "longitude": "5"
                },
                {
                    "range": "147.161.230.0/23",
                    "vpn": "",
                    "gre": "",
                    "hostname": "",
                    "latitude": "52",
                    "longitude": "5"
                },
                {
                    "range": "147.161.232.0/23",
                    "vpn": "",
                    "gre": "",
                    "hostname": "",
                    "latitude": "52",
                    "longitude": "5"
                },
                {
                    "range": "147.161.234.0/23",
                    "vpn": "",
                    "gre": "",
                    "hostname": "",
                    "latitude": "52",
                    "longitude": "5"
                },
                {
                    "range": "147.161.224.0/23",
                    "vpn": "",
                    "gre": "",
                    "hostname": "",
                    "latitude": "52",
                    "longitude": "5"
                },
                {
                    "range": "147.161.226.0/23",
                    "vpn": "",
                    "gre": "",
                    "hostname": "",
                    "latitude": "52",
                    "longitude": "5"
                }
            ]
        }
    }
}
'@

Now for the actual code:

# Convert json to an object
$data = $json |ConvertFrom-Json

# Iterate over each property (cloud provider?) on the root object
foreach($provider in $data.psobject.Properties){
  # save the provider name for later
  $providerName = $provider.Name
  # Iterate over each property (geographic region?) on the provider object
  foreach($region in $provider.Value.psobject.Properties){
    # save the region name for later, remove the `continent : ` prefix
    $regionName = $region.Name -replace '^.*?:\s*'
    # Iterate over each property (datacenter location?) on the region object
    foreach($location in $region.Value.psobject.Properties){
      # save the location name for later, remove the `city : ` prefix
      $locationName = $location.Name -replace '^.*?:\s*'

      # Extract the network information, attach the parent details
      $location.Value |Select-Object *,@{Name='provider';Expression={$providerName}},@{Name='region';Expression={$regionName}},@{Name='location';Expression={$locationName}},
    }
  }
}

For which you'll get output like this, much easier to work with:

range     : 147.161.174.0/23
vpn       :
gre       :
hostname  :
latitude  : 24.453884
longitude : 54.3773438
provider  : zscloud.net
region    : EMEA
location  : Abu Dhabi I

range     : 185.46.212.0/23
vpn       : amsterdam2-vpn.zscloud.net
gre       : 185.46.212.36
hostname  : ams2.sme.zscloud.net
latitude  : 52
longitude : 5
provider  : zscloud.net
region    : EMEA
location  : Amsterdam II

range     : 147.161.228.0/23
vpn       :
gre       :
hostname  :
latitude  : 52
longitude : 5
provider  : zscloud.net
region    : EMEA
location  : Amsterdam II

range     : 165.225.240.0/23
vpn       : ams2-2-vpn.zscloud.net
gre       : 165.225.240.42
hostname  : ams2-2.sme.zscloud.net
latitude  : 52
longitude : 5
provider  : zscloud.net
region    : EMEA
location  : Amsterdam II

range     : 147.161.172.0/23
vpn       :
gre       : 165.225.240.42
hostname  :
latitude  : 52
longitude : 5
provider  : zscloud.net
region    : EMEA
location  : Amsterdam II

range     : 147.161.230.0/23
vpn       :
gre       :
hostname  :
latitude  : 52
longitude : 5
provider  : zscloud.net
region    : EMEA
location  : Amsterdam II

range     : 147.161.232.0/23
vpn       :
gre       :
hostname  :
latitude  : 52
longitude : 5
provider  : zscloud.net
region    : EMEA
location  : Amsterdam II

range     : 147.161.234.0/23
vpn       :
gre       :
hostname  :
latitude  : 52
longitude : 5
provider  : zscloud.net
region    : EMEA
location  : Amsterdam II

range     : 147.161.224.0/23
vpn       :
gre       :
hostname  :
latitude  : 52
longitude : 5
provider  : zscloud.net
region    : EMEA
location  : Amsterdam II

range     : 147.161.226.0/23
vpn       :
gre       :
hostname  :
latitude  : 52
longitude : 5
provider  : zscloud.net
region    : EMEA
location  : Amsterdam II

@aster007 That's great. If my answer solves your problem, consider marking it "accepted" by clicking the checkmark on the left :) — Mathias R. Jessen, Aug 05 '21 at 14:04

Powershell extract data from really weird JSON file

1 Answers1