Output of SCCM query

Welcome Forums General PowerShell Q&A Output of SCCM query

This topic contains 1 reply, has 2 voices, and was last updated by

 
Keymaster
6 months, 2 weeks ago.

  • Author
    Posts
  • #101289

    Participant
    Points: 1
    Rank: Member

    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.

  • #101409

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    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.

The topic ‘Output of SCCM query’ is closed to new replies.