Let’s try to create SCCM collection for Windows 10 21H2 in this post. Windows 10 21H2 release that is almost ready to roll out in production for most of us. Windows 10 21H2 upgrade is going to happen with enablement package for most of the scenarios.
You can get more details on upgrading to Windows 10 21H2 using KB5003791 Enablement Package. If you are a bit behind with Windows 10 version upgrades and are still using Windows 10 1909 or older versions, you can’t use the enablement package to upgrade to Windows 10 21H2 version. You will need to use other Windows 10 upgrade methods.
Windows 10 21H2 comes with many fixes, and I have explained a number of them in the Fix Azure AD PRT Primary Refresh Token Issue blog post. You will also need to understand the basic collection creation process from the previous posts similar to SCCM Collection Best Practices while Creating a Dynamic Collection.
I will also provide CMPivot query details to find out the Windows 10 21H2 versions of PCs from your SCCM environment. I think it would be helpful for the SCCM admin to have a SQL query to find out the Windows 10 21H2 version of devices.
- Convert WQL Queries To SQL Queries Using SCCM Trick
- Verify SCCM Collection Query Preview Tool | Best Tool to Analyse Collection Query
Create Dynamic Collection for Windows 10 21H2 Devices
You can create the dynamic collection from the Assets and Compliance workspace. You can create a dynamic device collection using the following WQL query. There are some easy methods to build a WQL query that you should look into before spending a lot of time creating complex WQL queries.
Let’s create a new collection from the Assets and Compliance – Device Collections node. You will need to right-click on Device Collection and create a new collection. You will need to follow the basic collection creation process. Make sure the correct limiting collection is in place.
- Click Edit Query Statement to open the Query Statement Properties dialog box where you can create a query to use as the rule for the SCCM dynamic collection.
- On Query Statement Properties click on the Criteria tab.
- On Criteria Properties dialog box click on Show Query Language.
Copy the following WQL query to create SCCM collection for Windows 10 21H2 devices. The important information in this query is BuildNumber. We will use the build number (19044) in this query to group all Windows 10 21H2 PCs.
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 = “19044“
You can also use the Operating System Version number to 10.0.19044 group Windows 10 21H2 PCs of your SCCM environment.
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.19044“
You can review the WQL query by clicking on the green button near the query statement to validate the WQL query. I have explained the end-to-end validation process in the following post verify SCCM Collection, Query Preview Tool | Best Tool to Analyse Collection Query.
Click the Ok and Ok buttons to complete the WQL collection query creation process for Windows 10 21H2 Devices.
NOTE! – I don’t think there is any requirement to change the full schedule update from 7 days to 1 day if you already enabled incremental updates for Windows 10 21H2 device collection.
- Enable the following option – Use Incremental updates for this collection.
- Keep the default value – Schedule a full update on this collection – once in every 7 days (for my lab).
- Click on the Next, Next, Close button to finish the creation of All Windows 10 21H2 Devices.
CMPivot Query for Windows 10 21H2 Devices
Let’s quickly check how to find out Windows 10 21H2 devices using CMPivot query based on Operating system version or build number. You can launch the CMPivot query from any device collection, as you can see in the screenshot below.
You will need to use any of the following CMPivot queries to find out Windows 10 21H2 devices. You can also get the pie chart, column chart, and pie chart for Windows 10 devices.
OperatingSystem | where Version == '10.0.19044' OperatingSystem | where BuildNumber == '19044'
You can get more details about the pie chart and other charts of Windows 10 versions from the following query.
OperatingSystem | summarize count() by Caption | render barchart OperatingSystem | summarize count() by Caption | render columnchart OperatingSystem | summarize count() by Caption | render piechart
SQL Query for Windows 10 21H2
Now, it’s time to look into SQL queries to find out Windows 10 21H2 devices. This SQL Query will help you use the ‘when’ statement and then declare it according to your environment.
I use the version number of Windows 10 21H2 to find the list of 21H2 devices with assigned user details.
- Open the SQL Management Studio.
- Connect your Database Engine.
- Right Click on your database CM_XXX and click on ‘New Query’
- Copy the following SQL query to find the report of the Windows 10 21H2 version details.
- Click on the Execute button.
select v_R_System.Name0 as ‘Hostname’,
v_R_System.User_Name0 as ‘Username’,
v_R_System.Operating_System_Name_and0 as ‘Operating System’,
v_GS_OPERATING_SYSTEM.BuildNumber0 as ‘Build Number’,
v_GS_OPERATING_SYSTEM.Version0 as ‘OS Version’,
case when v_GS_OPERATING_SYSTEM.Version0 = ‘10.0.19044’ then ‘Windows 10 21H2’
End as ‘Windows 10 Version’
inner join v_gs_operating_system
where v_GS_OPERATING_SYSTEM.Version0 = ‘10.0.19044’
order by v_R_System.Name0
Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc……..…