Returning multiple properties from different tables in WQL with powershell

Question

I'm trying to get a query from SCCM to return the same output in powershell. Running the query in SCCM returns two columns: SMS_G_SYSTEM_COMPUTER_SYSTEM.name and SMS_G_SYSTEM_SYSTEM_ENCLOSURE.chassistypes, for a given set of chassis types

Then I try pasting the query language directly into a WQL query in powershell;

$computername = "sccmserver"
$namespace = "root\sms\site_sitecode"
$query = @"select SMS_G_System_COMPUTER_SYSTEM.Name,SMS_G_System_SYSTEM_ENCLOSURE.chassistypes from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("1","2","3","4","6","7","9","10","15")
"@
Get-WMIObject -Query $query -computername $computername -namespace $namespace

This returns the expected objects, but without the properties. Instead I get:

SMS_G_System_COMPUTER_SYSTEM  : System.Management.ManagementBaseObject
SMS_G_System_SYSTEM_ENCLOSURE : System.Management.ManagementBaseObject

If I run the same query but tell it to select only one property, it works as intended: eg - ChassisTypes : 1

How do I get it to return both values correctly?

Try to convert it to json (| ConvertTo-Json) to better understand what is really returned by your query. I guess it isn't as flat as you expect it to be... — iRon

Bacon Bits Bacon Bits · Accepted Answer · 2018-02-02T15:10:18

First, I'd try:

Get-CimInstance -Query $query -computername $computername -namespace $namespace

That command often formats WMI/CIM output much better since it came along after PowerShell v1.0.

My guess for a direct answer would be:

Get-WMIObject -Query $query -computername $computername -namespace $namespace |
    Select-Object -Property @{n='Name';e={$_.SMS_G_System_COMPUTER_SYSTEM.Name}}, @{n='chassistypes';e={$_.SMS_G_System_SYSTEM_ENCLOSURE.chassistypes}}

Returning multiple properties from different tables in WQL with powershell

1 Answers