Create SCCM Collection for Windows 10 21H2 CMPivot SQL Queries

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.

Patch My PC

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.

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.

1E Nomad
Create SCCM Collection for Windows 10 21H2 CMPivot SQL Queries
Create SCCM Collection for Windows 10 21H2 CMPivot SQL Queries
  • 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.
Collection for Windows 10 21H2
Collection for Windows 10 21H2

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

 Create Dynamic Collection for Windows 10 21H2 Devices
Create Dynamic Collection for Windows 10 21H2 Devices

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.

 Create Dynamic Collection for Windows 10 21H2 Devices
Create Dynamic Collection for Windows 10 21H2 Devices

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 collectiononce in every 7 days (for my lab).
  • Click on the Next, Next, Close button to finish the creation of All Windows 10 21H2 Devices.
 Create Dynamic Collection for Windows 10 21H2 Devices
Create Dynamic Collection for 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.

CMPivot Query for Windows 10 21H2 Devices
CMPivot Query for Windows 10 21H2 Devices

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
CMPivot Query for Windows 10 21H2 Devices
CMPivot Query for Windows 10 21H2 Devices

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.
 SQL Query for Windows 10 21H2
SQL Query for Windows 10 21H2

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’
from v_r_system
inner join v_gs_operating_system
on v_R_System.ResourceID=v_GS_OPERATING_SYSTEM.ResourceID
where v_GS_OPERATING_SYSTEM.Version0 = ‘10.0.19044’
order by v_R_System.Name0

Author

Anoop is Microsoft MVP! He is a Solution Architect on 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, Intune. He writes about technologies like ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.…

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.