Find Windows 11 Versions using SCCM | SQL | CMPivot Query

In this article, we will explore two methods to find Windows 11 versions using SCCM. To determine Windows 11 versions, we will use SQL query and CMPivot query in Configuration Manager.

You can run the SQL queries and CMPivot queries to find the Windows 11 versions, which include versions 23H2, 22H2, and 21H2. On Windows 11 computers, you must first install ConfigMgr client agent. You can read the guide on multiple ways to install SCCM agent on Windows 11.

Once you have Windows 11 computers running SCCM agents, you can run queries to find the Windows 11 versions including the build number.

Methods to determine the versions of Windows 11 using SCCM

In this article, we will use two methods to find Windows 11 versions using SCCM.

• Run SQL query and find Windows 11 versions.
• Run a CMPivot query and determine the Windows 11 versions.

The CMPivot entity makes it easier to instantly identify devices running Windows 11 from the SCCM console. If CMPivot doesn’t produce any results, an alternative method to try is an SQL query.

In both queries, the build number is used to determine the Windows 11 versions. The build number of Windows 11 23H2 is 22631, build number of Windows 11 version 21H2 is 22000. For Windows 11 version 22H2, the build number is 22621.

Also Read: How to create device collection for Windows 11 23H2 in SCCM

Method 1: Find Windows 11 Versions using SCCM SQL Query

To find the Windows 11 versions using the SCCM SQL query:

• Launch the SQL Server Management Studio (SSMS) and connect to the database engine.
• Expand Databases and right-click the SCCM database, and select New Query.
• In the query window, paste and execute the below SCCM SQL query to find the Windows 11 versions.

select v_R_System.Name0 as 'Hostname',
v_R_System.User_Name0 as 'System Username',
v_R_System.Operating_System_Name_and0 as 'Operating System',
v_GS_OPERATING_SYSTEM.BuildNumber0 as 'Windows OS Build Number',
case
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '22631' then 'Windows 11 23H2'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '22621' then 'Windows 11 22H2'				
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '22000' then 'Windows 11 21H2'				
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '19043' then 'Windows 10 21H1'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '19042' then 'Windows 10 20H2'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '19041' then 'Windows 10 2004'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '18363' then 'Windows 10 1909'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '18362' then 'Windows 10 1903'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '17763' then 'Windows 10 1809'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '17134' then 'Windows 10 1803'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '16299' then 'Windows 10 1709'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '15063' then 'Windows 10 1703'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '14393' then 'Windows 10 1607'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '10586' then 'Windows 10 1511'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '10240' then 'Windows 10 1507'
End as 'Windows OS Version'
from v_r_system
inner join v_gs_operating_system
on v_R_System.ResourceID=v_GS_OPERATING_SYSTEM.ResourceID
where v_R_System.Operating_System_Name_and0 like '%Microsoft Windows NT Workstation 10.0%'
order by v_R_System.Name0

Using the above SQL query, you can find all the Windows 11 versions along with Windows 10 versions. The following line queries the Windows 11 versions from the SCCM SQL database.

when v_GS_OPERATING_SYSTEM.BuildNumber0 = '22000' then 'Windows 11 21H2'

To find the Windows 11 22H2 devices using SQL query, run the following command.

when v_GS_OPERATING_SYSTEM.BuildNumber0 = '22621' then 'Windows 11 22H2'

To find the Windows 11 23H2 devices using SQL query, run the following command.

when v_GS_OPERATING_SYSTEM.BuildNumber0 = '22631' then 'Windows 11 23H2'

Note: In the above query, please substitute Windows 11 21H1 for Windows 11 21H2.

Method 2: Use SCCM CMPivot Query to Find Windows 11 Versions

In this method, we will use SCCM CMPivot query to find Windows 11 versions.

• Launch the ConfigMgr console and go to Assets and Compliance > Overview > Device Collections.
• Choose the device collection against which you want to run the CMPivot.
• Right-click the device collection and click Start CMPivot.
• Enter the following query to find Windows 11 versions.

OperatingSystem | where BuildNumber == '22000'

The above CMPivot query lists all the Windows 11 versions based on the build number. The build number of Windows 11 21H2 is 22000.

The following CMPivot query can be used to find all Windows 11 22H2 devices in Configuration Manager.

OperatingSystem | where Version == '10.0.22621'
OperatingSystem | where BuildNumber == '22621'

The following CMPivot query can be used to find all Windows 11 23H2 devices in SCCM.

OperatingSystem | where Version == '10.0.22631'
OperatingSystem | where BuildNumber == '22631'

Currently, Windows 11 22H23 is the latest Windows 11 release. The query has been updated to include the new version of Windows 11. Learn how to upgrade to Windows 11 22H2 using Configuration Manager and Microsoft Intune.

If you want to query all Windows 10 versions and Windows 11 versions in one go, here is the CMPivot query that you can use. The below CMPivot query can find all Windows 10 versions and even Windows XP and Windows 7.

OperatingSystem | where ProductType == 1 | project Edition=case( 
 BuildNumber == '2600', 'Windows XP', 
 BuildNumber like '760%', 'Windows 7', 
 BuildNumber == '9600', 'Windows 8', 
 BuildNumber == '10240', 'Windows 10 1507', 
 BuildNumber == '10586', 'Windows 10 1511', 
 BuildNumber == '14393', 'Windows 10 1607', 
 BuildNumber == '15063', 'Windows 10 1703', 
 BuildNumber == '16299', 'Windows 10 1709', 
 BuildNumber == '17134', 'Windows 10 1803', 
 BuildNumber == '17763', 'Windows 10 1809', 
 BuildNumber == '18362', 'Windows 10 1903', 
 BuildNumber == '18363', 'Windows 10 1909', 
 BuildNumber == '19041', 'Windows 10 2004',
 BuildNumber == '19042', 'Windows 10 20H2',
 BuildNumber == '19043', 'Windows 10 21H1',
 BuildNumber == '22000', 'Windows 11 21H2',
 BuildNumber == '22621', 'Windows 11 22H2',
 BuildNumber == '22621', 'Windows 11 23H2',
 BuildNumber > '22621', 'Windows 11 Other', 
 BuildNumber) 
 | summarize count() by Edition | order by count_ asc 

Here is a video tutorial that shows how to find Windows 11 versions using SQL Query and CMPivot Query using SCCM.