Author Posts

May 28, 2018 at 9:22 am

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.

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:
SmsProviderObjectPath
__GENERIC
SMS_G_System_OPERATING_SYSTEM
"
instance of SMS_G_System_OPERATING_SYSTEM
{
BuildNumber = ""16299"";
};
"
SMS_G_System_SYSTEM
"
instance of SMS_G_System_SYSTEM
{
Name = ""HOSTNAME"";
SystemType = ""X64-based PC"";
};
"
PSComputerName
SCCMSERVERNAME
PSShowComputerName
FALSE

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
HOSTNAME
SystemType
X64
BuildNumber
16299

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.

May 30, 2018 at 2:55 am

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.