SCCM Collection for Windows 11 22H2 Devices | CMPivot | SQL Queries

This is the post to create SCCM Collection for Windows 11 22H2 Devices. You can also get the CMPivot and SQL query details to get the 22H2 device details from the SCCM environment.

WQL query is the subset of SQL query language, and this query language is used when you create dynamic collections in SCCM. You can get the details of Windows 11 21H2 devices using the WQL query. It would be helpful for the SCCM admin to have a SQL query to find out the Windows 11 22H2 version of devices.

You can upgrade Windows 10 21H2 to 22H2 using Enablement Package. There is no enablement package-based upgrade for Windows 11 21H2 to 22H2 because a Windows OS-level change is needed. The enablement package upgrade scenario is NOT available for Windows 11 22H2.

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 11 22H2 versions of PCs from your SCCM environment.

Patch My PC

Windows 11 22H2 version Details

Understanding the Windows 11 version details is important before creating the Windows 11 22H2 collection. The collection query is based on the WQL query, but the logic of the WQL query role is based on the OS version of Windows 11.

Let’s quickly look at the Windows 11 build numbers and version numbers. The Windows 11 version build number starts with 22000. But the Windows 11 22H2 version is 10.0.22621.

Adaptiva
Windows 11 21H2New! – Windows 11 22H2
10.0.2200010.0.22621
SCCM Collection for Windows 11 22H2 Devices – Table 1
SCCM Collection for Windows 11 22H2 Devices Fig. 1
SCCM Collection for Windows 11 22H2 Devices Fig. 1

Create SCCM Collection for Windows 11 22H2 Devices

It’s time to create Windows 11 22H2 SCCM dynamic device collection. The process of collection creation is not changed significantly. This 22H2 SCCM collection is needed to deploy apps and policies specific to Windows 11 22H2.

  • Navigate to \Assets and Compliance\Overview\Device Collections.
  • Right-click on the Devices collection node and select ->Create Device Collection!
SCCM Collection for Windows 11 22H2 Devices
SCCM Collection for Windows 11 22H2 Devices Fig. 2

Now you can create the Dynamic query for 22H2 devices, and this is the place where you need to build a WQL query to group all Windows 11 22H2 devices.

  • 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 the Criteria Properties, the dialog box clicks on Show Query Language.
SCCM Collection for Windows 11 22H2 Device Fig. 3.1
SCCM Collection for Windows 11 22H2 Device Fig. 3.1

Copy the following WQL query to create an SCCM collection for Windows 11 22H2 devices. The Windows 11 build number and version details are given as part of the above table. The first WQL query is based on the build number.

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 = "22621"
SCCM Collection for Windows 11 22H2 Device Fig. 4
SCCM Collection for Windows 11 22H2 Device Fig. 4

You can also use the Operating System Version number 10.0.22621 to create the group of Windows 11 22H2 devices.

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"
SCCM Collection for Windows 11 22H2 Device Fig. 5
SCCM Collection for Windows 11 22H2 Device Fig. 5

Validate WQL Collection Query for Windows 11 22H2 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.

SCCM Collection for Windows 11 22H2 Device Fig. 6
SCCM Collection for Windows 11 22H2 Device Fig. 6

Click the Ok and Ok buttons to complete the WQL collection query creation process for Windows 11 22H2 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 11 22H2 device collection.

  • Enable the following option – Use Incremental updates for this collection.
  • Keep the default value – Schedule a full update on this collectiononce every 7 days (for my lab).
  • Click on the Next, Next, Close button to finish the creation of All Windows 11 22H2 Devices.
SCCM Collection for Windows 11 22H2 Device Fig. 7
SCCM Collection for Windows 11 22H2 Device Fig. 7

CMPivot Query for Windows 11 22H2

Let’s quickly check how to find out Windows 11 22H2 devices using the CMPivot query based on the Operating system version or build number. You can launch the CMPivot query from any device collection, as seen in the screenshot below.

  • Right-Click on All Systems collection or any other device collection.
  • Select Start CMPivot.
OperatingSystem | where Version == '10.0.22621'
OperatingSystem | where BuildNumber == '22621'
SCCM Collection for Windows 11 22H2 Device Fig. 8
SCCM Collection for Windows 11 22H2 Device Fig. 8

You can get more details about the pie chart and other charts of Windows 10 or 11 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 11 22H2

Now, it’s time to look into SQL queries to find out about Windows 11 22H2 devices. This SQL Query will help you use the ‘when’ statement and declare it according to your environment.

I use the version number of Windows 11 22H2 to find the list of 22H2 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 11 22H2 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.22621' then 'Windows 11 22H2'
End as 'Windows 11 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.22621'
order by v_R_System.Name0
SCCM Collection for Windows 11 22H2 Device Fig. 9
SCCM Collection for Windows 11 22H2 Device Fig. 9

Author

Anoop C Nair is Microsoft MVP! He is a Device Management Admin 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.

Leave a Comment

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