SCCM collects operating system details from client as part of inventory. We can create SCCM collection based on operating system attribute class such as OS Name, Build or version number, and OS architecture. We will discuss about different collection queries in this blog post.
A collection or query created in Configuration Manager uses WMI Query Language (WQL) to request objects from the SMS Provider WMI Schema, which in turn retrieves the data from site database.
The SMS_G_System_OPERATING_SYSTEM Windows Management Instrumentation (WMI) class) is an SMS Provider server class in Configuration Manager. This class contains inventory information for operating system.
In the Query / Collection Query wizard, the friendly name of SMS_G_System_OPERATING_SYSTEM is Operating System and it’s referred as Attribute Class.
In this blog post, we will discuss about most commonly used collection queries related with Operating System attribute class with examples and sample queries.
Related post: If you are looking for step by step process to create a dynamic / query based collection then follow this article : How to Create Dynamic Collection in SCCM
Other posts in this series:
- SCCM Collection Queries for System Resource (SMS_R_System) Attribute Class
- SCCM Device Collection Based on Computer Hardware Details
- SCCM collection Based on Operating System Name
- SCCM collection based on Operating System Version
- SCCM collection based on Operating System Install Date
- Attributes to select from Query Designer
- SCCM Collection Query / WQL Query for Operating System installed after specific date
- SCCM Collection Query / WQL Query for Operating System installed before specific date
- SCCM Collection Query / WQL Query for Operating System installed between two dates
- SCCM Collection Query / WQL Query for Operating System installed in last n number of days
- SCCM device collection based on Computer Last Boot-up time
- Related Posts:
SCCM collection Based on Operating System Name
The below collection query can be used to create a dynamic collection for all computers based on Windows OS caption. If you are using query designer then select the below attribute class and attribute name. Otherwise you can also copy the below WQL query and paste that directly in query editor.
Attributes to select from Query Designer
Attribute Class : Operating System
Attribute Name : Caption
SCCM Collection Query / WQL Query for All Windows 10 devices
select distinct 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.Caption like "Windows 10%"
SCCM Collection Query / WQL Query for All Windows 11 devices
select distinct 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.Caption like "Windows 11%"
The above WQL query used V_GS_System base WMI Class which contains the information for hardware and software inventory. If you need to create device collection for Windows 10 / 11 devices where SCCM client is not yet installed then you can use SMS_R_System WMI class. Check out this article for more details about System Resouce disovery class.
SCCM collection based on Operating System Version
The Version or Build Number attributes of Operating System class can be used to create query based collection for sepecific OS version / Build number.
Attributes to select from Query Designer
Attribute Class : Operating System
Attribute Name : Version / Build Number
SCCM Collection Query / WQL Query for Windows 11 22H2 devices
Please find the WQL query example below to create a collection for Windows 11 Version 22H2 computers.
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.22621"
We have more examples for device collection based on OS Version in another blog post SCCM dynamic collection for Windows 10 / 11 devices.
You can also find the details of of Windows 10 / 11 OS build versions in our post Windows 10 / 11 Operating System Build Versions.
SCCM collection based on Operating System Install Date
The Operating System – Install Date attribute can be used to create dynamic query based collection based on OS install date.
Attributes to select from Query Designer
Attribute Class : Operating System
Attribute Name : Install Date
SCCM Collection Query / WQL Query for Operating System installed after specific date
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.InstallDate >= "06/29/2022"
SCCM Collection Query / WQL Query for Operating System installed before specific date
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.InstallDate <= "06/29/2022"
SCCM Collection Query / WQL Query for Operating System installed between two dates
The below query cannot be directly created in query desginer. You need to edit the query rules in query editor.
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.InstallDate BETWEEN "10/01/2022" AND "10/31/2022"
SCCM Collection Query / WQL Query for Operating System installed in last n number of days
The below query will include the computer installed in last 30 days in collection.
The below query cannot be directly created in query desginer. You need to edit the query rules in query editor.
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 (DATEDIFF(day,SMS_G_System_OPERATING_SYSTEM.InstallDate , GetDate()) < 30)
SCCM device collection based on Computer Last Boot-up time
Attributes to select from Query Designer
Attribute Class : Operating System
Attribute Name : Last Boot-up time
SCCM Collection Query / WQL Query for computers not rebooted in last n number of days
The below WQL query can be used to create collection for computers which have not been rebooted since n number of days. This WQL use SQL function DateDiff and GetDate to calculate the difference between current date and Last boot up time to find the gaps.
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 (DATEDIFF(day,SMS_G_System_OPERATING_SYSTEM.LastBootUpTime , GetDate()) = 15)
Related Posts:
- How to Create Dynamic Collection in SCCM
- Enclosure Chassis Types Value & Description | ConfigMgr | SCCM
- ConfigMgr: Maintenance Window for member of specific collection – SQL Query
- SCCM Dynamic Collection for Windows 10 / 11 Devices
- SCCM Device Collection Equivalents in Microsoft Intune for App Deployment
- SCCM Dynamic Collection Based on Device Type | Collection Queries
- SCCM Collection Based on Operating System Optional Features
- SCCM Device Collection Based on Installed Software Products & Inventoried Files
- SCCM Device Collection Based on Computer Hardware Details
- SCCM Collection Queries for Opearting System Attribute Class
- SCCM Collection Queries for System Resource (SMS_R_System) Attribute Class
- SCCM Dynamic Collection using Application Deployment Status
- SCCM Dynamic Collection Based on Configuration Baseline Compliance Status
- How to Create , View and Organize SCCM Maintenance Windows
- How to Provision Windows 10 / 11 Device using Intune and Windows Autopilot
Subscribe to Techuisitive Newsletter
Be the first to know about our new blog posts. Get our newsletters directly in your inbox and stay up to date about Modern Desktop Management technologies & news.