Let’s create an SCCM report for Windows 11 version Count and Dashboard. The first version of Windows 11 was released on 4th Oct 2021. The first release of Windows 11 is called 21H2 (Oct 2021 release). In this post, you will get a count of Windows 11 versions from the SQL query shared.
You might have already used the SQL Query Windows 10 Version Count that Ankit shared for the HTMD community. In this post, I will help you get Windows 11 version count based on the build number. You have to install the Configuration Manager client on Windows 11 PC as an initial step.
The Windows 11 version count report gives you the total number of PCs in a particular version of Windows. It’s also worth creating a Windows 11 device collection to deploy applications and policies only to Windows 11 PCs.
You can use the following Windows 11 version numbers/build numbers table to create the SCCM report for WIndows 1 version count. I think it’s better to combine Windows 10 and Windows 11 version count details in the report. This report is going to help with Windows 11 migration status.
- SCCM Report Office 365 Client Upgrade Readiness Dashboard
- SCCM SQL Query to Find Collections Used for App Deployment
- SCCM Report Include Membership Collection Rule
- Application Deployment Status SQL Query Custom Report
Windows 11 Migration Help
Let’s see how to track down different Windows 11 versions in your environment and how it’s progressing? If you are managing a large number of Windows 10 devices and start planning for Windows 11 migration, this SCCM report will be a great help for you.
You will have different methods to upgrade to Windows 11. You will have a manual method to upgrade to Windows 11 using ISO or the bootable USB method. Also, there are SCCM and Intune methods that upgrade Windows 10 PCs to Windows 11.
First of all, you will need to check Windows 11 Minimum System Requirements. You can use Windows Update for Business(WUfB) and Intune Feature Update Deployment Policy to perform Windows 11 upgrades. There is an option to upgrade to Windows 11 using SCCM Task Sequence.
SCCM Report for Windows 11 Version Count
The following SQL query will help you create the SCCM report for Windows 11 version count and Dashboard. This can also be used as the Windows 11 migration status dashboard. I have taken all the Windows 11 and Windows 10 versions. Let me know if I’m missing any Windows versions in the query.
- 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 and Windows 10 version details.
- Click on the Execute button.
NOTE! – Windows 11 reports the Operating System property as Microsoft Windows NT Workstation 10.0, which is identical to Windows 10.
You will have to copy the following SQL query to get Windows 11 version count. And you can use the SCCM report builder to create a custom report for the Windows 11 version count and Dashboard.
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', case when v_GS_OPERATING_SYSTEM.BuildNumber0 = '22000' then 'Windows 11 21H2' when v_GS_OPERATING_SYSTEM.BuildNumber0 = '19044' then 'Windows 10 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 10 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
You can check the results of different versions of Windows operating systems available in your workplace environment that SCCM/ConfigMgr manages. You will also be able to check out the username for each Windows 11 PC. The user name field is going to help you in user communications.
Windows 11 version Dashboard
The following Dashboard will give you the details of Windows 11, and Windows 10 migration status for your SCCM managed devices. During the Windows 11 migration, I expect Windows 10 count to go down and Windows 11 count to go up.
You will need to copy the following SQL code to create an SCCM report for Windows 11 version count and Dashboard.
select v_GS_OPERATING_SYSTEM.BuildNumber0, "Windows 11 or Windows 10 Version"= case v_GS_OPERATING_SYSTEM.BuildNumber0 when '22000' then 'Windows 11 21H2' when '19044' then 'Windows 10 21H2' when '19043' then 'Windows 10 21H1' when '19042' then 'Windows 10 20H2' when '19041' then 'Windows 10 2004' when '18363' then 'Windows 10 1909' when '18362' then 'Windows 10 1903' when '17763' then 'Windows 10 1809' when '17134' then 'Windows 10 1803' when '16299' then 'Windows 10 1709' when '15063' then 'Windows 10 1703' when '14393' then 'Windows 10 1607' when '10586' then 'Windows 10 1511' when '10240' then 'Windows 10 1507' when '22454' then 'Windows 11 Insider Version' End,count(*) [Total] from v_GS_OPERATING_SYSTEM where v_GS_OPERATING_SYSTEM.Caption0 like 'Microsoft Windows 10%' or v_GS_OPERATING_SYSTEM.Caption0 like 'Microsoft Windows 11%' group by v_GS_OPERATING_SYSTEM.BuildNumber0 order by [Total] desc
Now it’s time to take a look at the SCCM Dashboard to get the count of Windows 11 PCs.
List of Windows 11 Devices using SQL
Let’s list Windows 11 Devices using SQL management studio and managed by SCCM/ConfigMgr. You can try the SQL query mentioned below.
Select v_R_System.Name0 as 'Hostname', v_R_System.User_Name0 as 'Username', v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System', v_GS_OPERATING_SYSTEM.BuildNumber0 as 'Build Number' 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.BuildNumber0 >= '22000' and v_GS_OPERATING_SYSTEM.BuildNumber0 < '30000' order by v_R_System.Name0
List of Windows 10 Devices using SQL
Let’s find the list of Windows 10 Devices using SQL. You need to use the following SQL query to find out Windows 10 devices.
Select v_R_System.Name0 as 'Hostname', v_R_System.User_Name0 as 'Username', v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System', v_GS_OPERATING_SYSTEM.BuildNumber0 as 'Build Number' 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.BuildNumber0 < '20000' and v_GS_OPERATING_SYSTEM.BuildNumber0 > '10000' order by v_R_System.Name0
Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with more than 20 years of experience (calculation done in 2021) in IT. He is a blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. E writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc…
2 thoughts on “SCCM Report for Windows 11 Version Count Dashboard”
Quick question. Will it do any harm to use the v_GS_OPERATING_SYSTEM.Caption0 instead?
Forgot I asked. I see this is to report on Build version specifically