Find Windows 11 Versions using SCCM | SQL | CMPivot Query

In this post, we will use two methods to find Windows 11 versions using SCCM. To determine Windows 11 versions, we will use SQL query and SCCM CMPivot query.

You can run the SQL queries and CMPivot queries to find the Windows 11 versions which include version 21H2, 22H2. 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.

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 the queries, the build number is used to determine the Windows 11 versions. The 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 22H2 in SCCM

Method 1 – Find Windows 11 Versions using SCCM SQL Query

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

  • Launch the SQL Server Management studio (SSMS) and connect to database engine.
  • Expand Databases and right-click 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 = '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'
Find Windows 11 Versions using SCCM SQL Query
Find Windows 11 Versions using SCCM SQL Query

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.

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

Currently, Windows 11 22H2 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 (Endpoint Manager).

Find Windows 11 Versions using SCCM CMPivot Query
Find Windows 11 Versions using SCCM CMPivot Query

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 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.

Find Windows 11 versions using SCCM | ConfigMgr

Leave a Reply

Your email address will not be published. Required fields are marked *

3 Comments

  1. Excellent post Prajwal. We are all ready for Windows 11 deployment in my company now.

  2. Avatar photo Tomasz Michalek says:

    Hi Prajwal,

    In my knowledge MS call Windows 11 21H2 not 21H1 – Windows 11 version 21H2.