Find Windows 11 Versions using SCCM | SQL | CMPivot

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.

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 helps to find out devices with Windows 11 versions from SCCM console in real time. The SQL query should be used as alternate method in case you get results with CMPivot.

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.

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 = '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 SCCM SQL database.

when v_GS_OPERATING_SYSTEM.BuildNumber0 = '22000' then 'Windows 11 21H2'
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 to 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.

Currently, Windows 11 21H2 is the latest Windows 11 release. I will update the query once the new version of Windows 11 is released in the future.

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 > '22000', '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

Need Assistance?

Send us a message or post your question in forums.

3 thoughts on “Find Windows 11 Versions using SCCM | SQL | CMPivot”

Leave a Comment