How would I export a JSON file to excel CSV properly?

0
votes

JSON FILE:

"jwick": {
    "VPN1": {
        "username": "jwick",
        "2FA_STATUS": "2FA Registered and Active",
        "ad_account_status": "AD Account Enabled",
        "ADMIN_BOUNDARY": "United States"
    },
    "VPN2": {
        "username": "jwick",
        "2FA_STATUS": "2FA Registered and Active",
        "ad_account_status": "AD Account Enabled",
        "role_rdp_hosts": {
            "[email protected]": {
                "computerhostname12": "ipaddress 192.168.0.1"
            }
        },
        "ADMIN_BOUNDARY": "United States"
    }
},
"jbond": {
    "VPN1": {
        "username": "jbond",
        "2FA_STATUS": "2FA Registered and Active",
        "ad_account_status": "AD Account Enabled",
        "ADMIN_BOUNDARY": "England"
    },
    "VPN2": {
        "username": "jbond",
        "2FA_STATUS": "2FA Registered and Active",
        "ad_account_status": "AD Account Enabled",
        "role_rdp_hosts": {
            "[email protected]": {
                "hostname23": "ipaddress 10.0.0.1"
            }
        },
        "ADMIN_BOUNDARY": "England"
    }
},
"JIJOE": {
    "VPN1": {
        "username": "JIJOE",
        "2FA_STATUS": "2FA Locked",
        "attempted_login": "358 days, 20 hours, 3 min",
        "last_login": "489 days, 18 hours, 12 min",
        "created": null,
        "ad_account_status": "AD Account Enabled",
        "ADMIN_BOUNDARY": "United States"
    },
    "VPN2": {
        "username": "JIJOE",
        "2FA_STATUS": "2FA Locked",
        "ad_account_status": "AD Account Enabled",
        "role_rdp_hosts": {
            "[email protected]": {
                "computername34": "ipaddress 172.1.0.1"
            }
        },
        "ADMIN_BOUNDARY": "United States"
    }
}

How would I export this JSON file to reflect the user at the root, and the role_rdp_hosts key under each user, if they are within the "United States" Admin_Boundary, into a CSV or some kind of list?

POWERSHELL SCRIPT

$json = (Get-Content "C:\Users\Tofu\Downloads\VPNUsers.json" -raw | ConvertFrom-Json)
$Output = @()

$Output = ($json.psobject.Properties.
    Where({ $_.Value.VPN2.ADMIN_BOUNDARY -eq "United States." }).
    ForEach({ [pscustomobject] @{ $_.Name = $_.Value.VPN2.role_rdp_hosts } }) | 
        ConvertTo-Json)

$Output | Export-Csv -path "C:\Users\Tofu\Desktop\List.csv" -NoTypeInformation

Trying this PowerShell script returns "Length 29666" in excel. If I take away the $Output variable and just pipe out ConvertToJson, I can view it in the terminal fine.

jsonpowershell