Output of SCCM query

Hi All,

I have a simple query below that works fine when in the SCCM console but when I run this through powershell and export I don’t get the desired output:

I am using Invoke-CMQuery within powershell run the query.


select SMS_G_System_SYSTEM.Name, SMS_G_System_SYSTEM.SystemType, SMS_G_System_OPERATING_SYSTEM.BuildNumber from  SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId

excel CSV output:
instance of SMS_G_System_OPERATING_SYSTEM
BuildNumber = "“16299"”;
instance of SMS_G_System_SYSTEM
Name = ““HOSTNAME””;
SystemType = ““X64-based PC””;

Now I can only assume this is because of the join between the different classes in SCCM but I am not sure on how I can get this to export correctly as queries that have the same class criteria export just fine.

Can it be exported like below:?
System Name

To get round this I would have created 2 queries and joined after but the OPERATING_SYSTEM class does not have any common information I can join too.

Yeah, so you’re just getting things a bit more “raw” and less interpreted, which means you’d have to do the interpreting yourself. For example:

instance of SMS_G_System_OPERATING_SYSTEM
BuildNumber = ""16299"";

Means PowerShell is getting an array of one object, and it uses { and } to denote arrays. When Export-CSV tries to turn that into text, you get what you’re getting. So prior to that export, you’re going to have to manipulate the raw objects into whatever you want. There’s nothing native to do that for you, though. SCCM’s console is “translating” the “raw” class names into the ones you want, but PowerShell doesn’t have the knowledge to do that for you.

Frankly, the SCCM commands should have been written to do that, but they weren’t.