Search code examples
powershellsccmwql

Returning multiple properties from different tables in WQL with powershell

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?

Solution

  • 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}}