Goverlan and SCCM integration Part II – Querying the SCCM Site Server

Home is where the data is…

In this second part of our SCCM\Goverlan Integration series, we look at how we can query the SCCM Site server directly to access a treasure trove of information. SCCM stores loads of information about computer configurations in WMI. We can simply use our WMI Swiss Army Knife – WMIX – to quickly tap into this data source.

SCCM Data Queries

Without knowing WQL you may find it difficult to write custom queries to find the exact data you need. SCCM does provide a UI for creating queries, but unless you know the exact WMI schema it may prove difficult to use.

Using WMI to query SCCM Data

Let’s take WMIX and point it to your SCCM Site Server. You will notice you have different WMI repositories available. The one we are interested in is Root\SMS\Site_(SiteCode)\SMS_Resource.

This class contains SMS_R_System, which anyone familiar with SMS will understand, is the main table for computer information. In this class you will find information such as IP addresses, CPU Types, OU Paths, and much more. You can take this class and add it to your browser view in WMIX for easy querying and reporting.

From WMIX to the Config Manager Console

In SCCM you have a section called Queries that lets you run WQL queries and report that information in the UI, or create collections based on specific criteria, such as OS type or memory information. Using WMIX’s WQL Query generator we can now make a query easily and simply copy and paste it in a Config Manager Console query.

Here is an easy example:

Let’s design a query that gives us all computers where the host name begins with “pjlab” and shows IP addresses and OU.

In WMIX, connect to the site server and go to the Query WMI tab.
Click the WQL Wizard button.

CREATE WQL QUERIES WITH AN EASY TO USE WIZARD — WMIX WQL WIZARD

3. Select Other Classes… from the drop down list. This will open the search dialogue.

ADD CUSTOM CLASSES TO THE WQL WIZARD FOR ADVANCED QUERIES — SEARCH FOR THE SMS_R_SYSTEM CLASS

4. Click the Query In link and select “site (Site code)” under SMS.

5. Search for SMS_R_System and click OK. This will focus the query on SMS_R_System.

6. Click the Next button and select “One or more properties”.

7. Choose Name, IP Addresses, and SystemOU Name, then click Next.

8.Select Configure a WMI Query Filter.

9. Click “Add New Condition to this group” and select Name LIKE (use whatever matches your environment here). NOTE: You must add the % for a wildcard search.

10. Click OK and Finish.

Now you have a WQL Query you can use in the Config Manager console. Create a new query and paste everything except the “Root\SMS\SITE_(SiteCode)” into the query and you will now have a table like this one.

USE SCCM QUERIES TO DEFINE CUSTOM COLLECTIONS OR REPORTS — COPY THE WQL FROM WMIX INTO A NEW SCCM QUERY

Conclusion

Config Manager Queries are useful in helping create on-demand views of your organization or for designing SCCM collections for specific needs and advertisements. You can also retrieve this data easily with a Goverlan Scope Action, but we all know being a good systems administrator means knowing that there is more than one way to skin a server.

For a free, full 30-day trial, click here. To see a full independent comparison of Goverlan and SCCM, click here.