Group model type of nodes by a custom property

ansongdk

I have a swql script that groups all the nodes by model type, and uses the machine type when model type is not found

SELECT
CASE
WHEN (n.HardwareHealthInfos.Model IS NULL OR n.HardwareHealthInfos.Model = '') THEN n.MachineType
ELSE n.HardwareHealthInfos.Model
END AS HardwareHealthInfos_Model,
COUNT(*) AS TotalCount
FROM Orion.Nodes n
LEFT JOIN Orion.NPM.SwitchStackMember s ON n.NodeID = s.NodeID
GROUP BY
CASE
WHEN (n.HardwareHealthInfos.Model IS NULL OR n.HardwareHealthInfos.Model = '') THEN n.MachineType
ELSE n.HardwareHealthInfos.Model
END

and produces the table:

But i want to then group these results by the city value in Orion.NodesCustomProperties

and get the following results below for example:

or

Thanks for you help

Daniel