Introducing SCCM Query Rules and their Importance

System Center Configuration Manager (SCCM) is a powerful tool that allows IT administrators to manage and deploy software, updates, and configurations across a network of Windows devices. One of the key features of SCCM is the ability to create collections, which are logical groups of devices that share common attributes. These collections can then be used to target specific deployments or perform administrative tasks.

To create collections in SCCM, you can use query rules. Query rules are SQL-like statements that define the criteria for including devices in a collection. By leveraging the power of query rules, you can dynamically add or remove devices from collections based on their attributes, such as hardware or software inventory.

Examples:

Let's take a look at an example query rule that selects devices based on their last hardware scan date:

SCCM Query Collection:

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client

from SMS_R_System

inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId

where SMS_G_System_COMPUTER_SYSTEM.Name not in (

    select SMS_R_System.Name

    from SMS_R_System

    inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId

    where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= DateAdd(dd, -30, GetDate())

)

This query rule selects devices from the SMS_R_System table and joins it with the SMS_G_System_COMPUTER_SYSTEM table based on the ResourceID. It then filters out devices that have a last hardware scan date within the last 30 days using a subquery.

To create this collection using the SCCM console, follow these steps:

1. Open the SCCM console and navigate to the "Assets and Compliance" workspace.
2. Expand the "Device Collections" node and right-click on "Collections".
3. Select "Create Device Collection" to open the "Create Device Collection Wizard".
4. Provide a name and optional description for the collection.
5. In the "Membership Rules" section, click on the "Add Rule" button and select "Query Rule".
6. In the "Query Rule Properties" dialog, provide a name and optional description for the query rule.
7. Click on the "Edit Query Statement" button to open the "Query Statement Properties" dialog.
8. In the "Query Language" drop-down, select "SQL" to use the SCCM query language.
9. Copy and paste the example query rule into the query statement field.
10. Click "OK" to save the query statement and close the dialog.
11. Click "OK" to save the query rule and close the dialog.
12. Complete the remaining steps in the wizard to finish creating the collection.

Once the collection is created, it will automatically update based on the query rule. Devices that meet the criteria defined in the query rule will be included in the collection, while devices that no longer meet the criteria will be automatically removed.

To run the query rule via the command line, you can use the SCCM PowerShell module or the SCCM command-line tool (CMTrace). These tools provide commands to create and manage SCCM collections, including query rules.

In conclusion, SCCM query rules are a powerful way to create collections in SCCM based on specific criteria. By leveraging SQL-like statements, you can dynamically manage and target devices within your network. Whether using the SCCM console or command line tools, query rules provide flexibility and automation in managing your SCCM collections.