Sometimes you just stop and wonder: how DO you make a WQL query with joins and use it with the SCCM SDK in C#? It's that gnawing thought we all have right?

So after spending an hour reading through people saying: "It's NOT supported!" and some people who said it was (without any examples whatsoever), I managed to get a small sample working.

So if any of you should come across this challenge (which is of course the most of the world), then here is a code-example on how to do it:

It's a small console application that output all computers and their last boot time.

using CTMS.ServerBaseline.SCCM.ClassLibrary;
using Microsoft.ConfigurationManagement.ManagementProvider;
using Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine;

(...)

//Connect to SCCM Server (see below in the blogpost on how to make this Connect method)
var connection = SCCMMethods.Connect("<SCCM Server>");

//Define query string with joins etc.
var queryString = "Select * from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId";

try
{
    IResultObject query = connection.QueryProcessor.ExecuteQuery(queryString);

    foreach (WqlResultObject obj in query)
    {
                    
        var lastBootUpTime = obj.GetSingleItem("SMS_G_System_OPERATING_SYSTEM").PropertyList["LastBootUpTime"];
        var machine = obj.GetSingleItem("SMS_R_System").PropertyList["NetbiosName"];

        if (lastBootUpTime != null)
        {
            //Try parse string to date
            DateTime result;
            DateTime.TryParse(lastBootUpTime, out result);

            //Write output
            Console.WriteLine("Machine: " + machine);
            Console.WriteLine("Last Boot Time: " + result);

        }

    }
}
catch (SmsException ex)
{
    Console.WriteLine("Failed to execute query: '" + queryString + "': " + ex.Message + "\nInner Exception:" + ex.InnerException.Message);
    throw ex;
}

Example output:

Machine: INTERNAL-TEST01
Last Boot Time: 13-09-2017 10:51:00
Machine: INTERNAL-TEST02
Last Boot Time: 26-10-2017 02:34:00
Machine: INTERNAL-TEST03
Last Boot Time: 30-10-2017 13:12:00

 

You can read here on how to connect to the SCCM Server and other SDK code snippets. The SCCM Library I'm using has a connect method described in that article.

Let me know in the comments if you have any questions

Until next time