Create Query-Based SCCM Device Collections

 In this article, I’ll walk you through step-by-step creation of query-based device collections, along with ready-to-use queries for:

  • Windows Server 2016 / 2019 / 2022 / 2025
  • All Servers
  • Windows 11 (latest builds)
  • Windows 10 versions



Step-by-Step: Create Query-Based Device Collection

Follow these steps in Microsoft Endpoint Configuration Manager console:

1. Open SCCM Console

  • Go to Assets and Compliance
  • Click on Device Collections

2. Create New Collection

  • Right-click Device Collections
  • Select Create Device Collection

3. General Settings

  • Name: Enter collection name (e.g., Windows Server 2025)
  • Limiting Collection:  use All Systems

4. Add Membership Rule

  • Click Add Rule → Query Rule
  • Provide:
    • Name: e.g., Server 2025 Query
  • Click Edit Query Statement

5. Add Query

  • Go to Show Query Language
  • Paste your query (from below sections)

6. Complete Wizard

  • Click OK → Next → Close

7. Update Collection

  • Right-click collection → Update Membership

🖥️ Windows Server 2025 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_OPERATING_SYSTEM 
on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId 
where SMS_G_System_OPERATING_SYSTEM.BuildNumber = "26100"
AND SMS_G_System_OPERATING_SYSTEM.Caption LIKE '%Windows Server%'


🖥️ Windows Server 2019 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_OPERATING_SYSTEM  
ON SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID  
WHERE SMS_G_System_OPERATING_SYSTEM.Version = '10.0.17763'  
AND SMS_G_System_OPERATING_SYSTEM.Caption LIKE '%Windows Server 2019%'

🖥️ Windows Server 2022 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_OPERATING_SYSTEM 
on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId 
where SMS_G_System_OPERATING_SYSTEM.Version = "10.0.20348"

🖥️ Windows Server 2016 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_OPERATING_SYSTEM 
on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId 
where SMS_G_System_OPERATING_SYSTEM.Version = "10.0.14393"  
AND SMS_G_System_OPERATING_SYSTEM.Caption LIKE '%Windows Server 2016%'

🖥️ All Servers 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  
where SMS_R_System.OperatingSystemNameandVersion like "%Server 10.0%"


Or You can use >Add Rule > Include collections, and select windows server versions separately





💻 Windows 11 (25H2)

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_OPERATING_SYSTEM  
on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId  
where SMS_G_System_OPERATING_SYSTEM.Version like "10.0.26200%"
and SMS_G_System_OPERATING_SYSTEM.Caption not like "%Server%"

Use the build numbers below for other Windows client builds (add them to the Version like "10.0.26200%" section in the above query).



Popular Posts

Windows 11 24H2 Upgrade using Intune Feature Updates Policy

Image
How to Deploy Feature Updates Using Intune: A Step-by-Step Guide Microsoft Intune provides a streamlined method for managing Windows feature updates across your organization. In this guide, I'll walk through the essential prerequisites, policy creation steps, deployment, and monitoring process to ensure a smooth rollout of feature updates using Intune. 1. Verify Prerequisites Before you create a feature update policy, ensure the following prerequisites are met on the target devices: Device Enrollment : Devices must be enrolled in Intune, either as Microsoft Entra hybrid joined or Microsoft Entra joined . Supported OS : Devices must be running a supported version of Windows 10 or Windows 11 . Telemetry Settings : Devices must have the telemetry level set to Required . You can configure this via Devices > Windows> Configuration > Create Policy > Templates>  Device Restrictions > Reporting and Telemetry > Share Usage Data >Set as Required...
Read more

Remove CCMCache, Windows.old, and Temp folders using Powershell script

Image
This PowerShell script automates the cleanup of: CCM Cache Files : Removes files older than 14 days from C:\Windows\ccmcache. Temporary Files : Cleans up files older than 14 days from the user’s temp folder ($env:Temp) and the system-wide temp folder (C:\Windows\Temp). Windows.old Folder : Deletes the Windows.old folder if it’s older than 14 days. This folder is typically created during a Windows upgrade and can take up significant disk space. The script also logs all actions to a log file (C:\TempCleanupLog.txt), making it easy to track what was deleted and troubleshoot any issues. # Define paths $ccmCachePath = "C:\Windows\ccmcache" $tempPath = "$env:Temp" $systemTempPath = "C:\Windows\Temp" $windowsOldPath = "C:\Windows.old" $logFile = "C:\TempCleanupLog.txt" # Change log file location if needed # Function to clean up files and directories older than a specific number of days function Clean-Ol...
Read more

Deploying a Script through Intune to a Linux PC

Image
Can Intune Deploy Shell Scripts to Linux Devices? Yes! Just like deploying PowerShell scripts to Windows, Intune can also deploy shell scripts to Linux devices. In this blog, I'll walk you through the process of deploying shell scripts to Linux using Intune, making it easier to automate tasks and manage Linux endpoints efficiently. Prerequisites Before deploying a shell script via Intune, ensure the following requirements are met: 1. Intune and Microsoft Entra ID Your environment must have Microsoft Intune configured for device management. This setup enables secure enrollment and policy enforcement on Linux devices. 2. Linux Device Enrollment The Linux PC must be properly enrolled in Intune to receive policies and scripts. If the device is not enrolled, follow Microsoft's documentation on Linux enrollment in Intune. Deploying the Shell Script Once the prerequisites are met, follow these steps to deploy your shell script through Intune: Access Microsoft Intune Sign in to the ht...
Read more

SCCM Feature Upgrade Failure on HP Computers: Insufficient System Partition Disk Space (Error 0xC1900200 / -1047526912)

Image
When attempting to install Windows 11 feature upgrade via SCCM, you might notice that the update fails quickly, and under the “More Information” section, it shows the error code 0xC1900200 (-1047526912) . Additionally, you may observe that the update content is not downloading properly, and within a few minutes, the installation fails. I encountered this issue on several HP laptops and was able to identify the root cause and solution. The first step in troubleshooting should be reviewing the Panther logs , which can help pinpoint the exact blocker. You can find the Panther logs at the following location: C:\$WINDOWS.~BT\Sources\Panther Next, you need to check the XML files located in the Panther folder, as shown in the screenshot above. Look for the most recently created CompatData.xml file. This file corresponds to the latest SCCM feature update attempt that failed. Open this file using CMTrace , which makes it easier to read and highlight error codes or issues in a structured form...
Read more