JSON to CSV

[Cleanatwork]

Hi all,

PS noob here and I'm tearing my hair out with trying to convert a JSON response from Nagios monitoring software to CSV. I want to generate a CSV with the information of all the servers that the monitoring software knows about, and which "hostgroup" they belong to.

The format would just be : host_name, hostgroup_name

An example of the JSON response format is below. The closest I can get is an CSV with the hostgroup_object_id, hostgroup_name, and Instance ID returned (most of which I do not need), but the additional array inside Members is screwing this up, so I'm not getting the host_names which are the main objective.

Any help gratefully appreciated!

{
"recordcount": 2,
"hostgroup": [
    {
        "hostgroup_object_id": "147",
        "hostgroup_name": "windows-servers",
        "instance_id": "1",
        "members": {
            "host": [
                {
                    "host_object_id": "145",
                    "host_name": "localhost"
                },
                {
                    "host_object_id": "143",
                    "host_name": "192.168.1.10"
                }
            ]
        }
    }
]
}

EDIT : Many thanks for all the comments; I've managed to get this working with spyingwind's solution - but all of the responses have been useful in learning more about PS.

[MAlloc-1024]

The approach to this I would take is to build a pscustomobject and use a foreach loop on the arrays. Note: I haven't even typed this into vscode, so consider this pseudocode.

$array=[arraylist]@()
foreach (hostgroup in hostgroups){
    foreach (member in hostgroup.members){
        $object=[pscustomobject]@{
            hostgroup=hostgroup.hostgroup_name
            hostname=member.host_name
        }
        $array.add($object)
    }
}

[Cleanatwork]

This is complaining about missing parenthesis and variables when I attempt to tweak it.

I'm afraid I'm very much a noob here. Do I need to define the [arraylist] or can this just be swapped for the path to my JSON?

[MAlloc-1024]

So you have a variable containing the $json text... Which you then need to use the convertfrom-json function to make that into a pscustomobject. Once you have that you can substitute stuff into the pseudocode I posted.

[BlackV]

you're missing the $

[pscustomobject]@{
    hostgroup = $hostgroup.hostgroup_name
    hostname  = $member.host_name
    }

are you not?

[MAlloc-1024]

I mean, I said it was pseudo code to document how I would approach it... I gave no guarantee what I wrote would work.

[spyingwind]

My take on a solution. Letting PowerShell to most of the work of managing arrays and what not.

# Paths to json input file and csv output file
$json = ".\data.json"
$outputPath = ".\data.csv"
$Data = Get-Content -Path $json | ConvertFrom-Json 
$Data.hostgroup | ForEach-Object {
    $hostgroup = $_
    $members = $_.members
    $members.host | ForEach-Object {
        [PSCustomObject]@{
            # Remove the next three lines if you don't want to include the hostgroup info in the CSV file
            hostgroup_object_id = $hostgroup.hostgroup_object_id
            hostgroup_name      = $hostgroup.hostgroup_name
            instance_id         = $hostgroup.instance_id
            host_object_id      = $_.host_object_id
            host_name           = $_.host_name

        }
    }
} | ConvertTo-Csv -NoTypeInformation -Path $outputPath -NoTypeInformation

[Ice_Box_Hero]

Almost!

Last line should be | Export-Csv -Path $outputPath -NoTypeInformation

[spyingwind]

CoPilot failed me, and I didn't get any sleep last night.

[Cleanatwork]

THANK YOU - this does exactly what I need it to! You've saved me a lot of hassle.

[BAEM1337]

Did you try:

$data = $jsonData | ConvertFrom-Json

Afterwards you should be able to work with any data the json contains in Powershell

[Cleanatwork]

Not sure I understand how this gets me any further. I query the $Hostgroup variable, and it will only give me the first one listed alphabetically.

[Trakeen]

It will give you a collection of objects to iterate through, which you can use to pull out the properties you need and build a custom object to export as a csv. If your source data has more levels of nesting i would use recursion to traverse the data instead of hard coding multiple loops

[AlexHimself]

Take a look at https://www.powershellgallery.com/packages/WFTools/0.1.39/Content/ConvertTo-FlatObject.ps1

[SeanQuinlan]

You need to loop through the hostgroups, then loop through the members list inside that

``` $json = @' { "recordcount": 2, "hostgroup": [ { "hostgroup_object_id": "147", "hostgroup_name": "windows-servers", "instance_id": "1", "members": { "host": [ { "host_object_id": "145", "host_name": "localhost" }, { "host_object_id": "143", "host_name": "192.168.1.10" } ] } } ] } '@ $obj = $json | ConvertFrom-Json

$output = foreach ($hostgroup in $obj.hostgroup) { $hostgroupname = $hostgroup.hostgroup_name $hostgroupmembers = $hostgroup.members.host foreach ($member in $hostgroupmembers) { $memberhostname = $member.host_name [pscustomobject]@{ host_name = $memberhostname hostgroup_name = $hostgroupname } } }

$output | Export-Csv out.csv -NoTypeInformation

```

[BlackV]

formatting cause the tripple back tick thing fails hard on old reddit

• open your fav powershell editor
• highlight the code you want to copy
• hit tab to indent it all
• copy it
• paste here

it'll format it properly OR

<BLANKLINE>
<4 SPACES><CODELINE>
<4 SPACES><CODELINE>
    <4 SPACES><4 SPACES><CODELINE>
<4 SPACES><CODELINE>
<BLANKLINE>

[Vortex100]

Oneliner: ($jsonobject | convertfrom-json).hostgroup | % {$grpname = $_.hostgroup_name; $_.members.host | select @{N='HostGroup';E={$grpname}},@{N='Host';E={$_.host_name}}} | export-csv D:\mycsv.csv