asked on

More Complex JSON in PowerShell

I have a PowerShell script which generates JSON output of VM objects in a vSphere environment. Each VM has a path property which represents the location of the VM in the folder structure. Rather than the folders be represented in a property, how can I modify the script to use the path value to generate folder objects with the VM objects inside?

PowerShell Example:

$SQL = "USE $SQLDatabase SELECT top 2 * FROM VMs"
$computers = Invoke-sqlcmd -query $SQL -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword

$connections = @()
$customProperties = @()

ForEach ($computer in $computers) {

    $connection = New-Object pscustomobject -Property @{
        "Type" = $computer.Type;
        "Name" = $computer.Name
        "ComputerName" = $computer.ComputerName
        "Path" = $computer.$path

    }
    $connections += $connection
}

@{
    Objects = $connections
} |
ConvertTo-Json -Depth 100 |
Write-Host]

Open in new window

Current Output Example:

{
  "Objects": [
    {
      "Path": "Clients A-K/Client 1 (ACC)",
      "ComputerName": "VM1",
      "Name": "VM1",
      "Type": "TerminalConnection"
    },
    {
      "Path": "Clients A-K/Client 1(ACC)",
      "ComputerName": "VM2",
      "Name": "VM2",
      "Type": "RemoteDesktopConnection"
    }
  ]
}

Open in new window

Goal Output Example:

{
  "Objects": [
    {
      "Type": "Folder",
      "Name": "Clients A-K",
      "Objects": [
        {
          "Type": "Folder",
          "Name": "Client 1 (ACC)",
          "Objects": [
            {
              "Type": "TerminalConnection",
              "Name": "VM1",
              "ComputerName": "VM1",
            },
            {
              "Type": "RemoteDesktopConnection",
              "Name": "VM2",
              "ComputerName": "VM2",
            }
          ]
        }
      ]
    }
  ]
}

Open in new window

oBdA

Try it like this:

$SQL = "USE $SQLDatabase SELECT top 2 * FROM VMs"
$computers = Invoke-sqlcmd -query $SQL -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword

$collection = [PSCustomObject][ordered]@{'Objects' = New-Object -TypeName System.Collections.ArrayList}
Function New-ObjectRecurse($Computer, $Objects) {
	If ($Computer.Path) {
		$name, $Computer.Path = $Computer.Path.Split('/', 2)
		If (-not ($child = $Objects | Where-Object {$_.Name -eq $name})) {
			$child = [PSCustomobject][ordered]@{
				'Type' = 'Folder'
				'Name' = $name
				'Objects' = New-Object -TypeName System.Collections.ArrayList
			}
			[void]$Objects.Add($child)
		}
		New-ObjectRecurse -Computer $Computer -Objects $child.Objects
	} Else {
		[void]$Objects.Add(($Computer | Select-Object -Property * -ExcludeProperty Path))
	}
}

ForEach ($computer in $computers) {
	New-ObjectRecurse -Computer $computer -Object $collection.Objects
}
$collection | ConvertTo-Json -Depth 100

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.