SCCM SQL Query for Windows 10 / 11 Version Summary

The below SQL query will summarize the list of Windows 10 / 11 versions along with total device account. This will help in understanding demographic of each windows build version in your environment and you can plan Windows upgrade accordingly. This SQL query can be used to create a report as well.

To run this SQL query, you need to launch Microsoft SQL Server Management Studio and connect with SCCM Site database.

SQL Query

select 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 = '22621' then 'Windows 11 22H2'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '22000' then 'Windows 11 21H2'
when v_GS_OPERATING_SYSTEM.BuildNumber0 = '19045' then 'Windows 10 22H2'
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', Count(*) as 'Total Devices'
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%'
Group by v_R_System.Operating_System_Name_and0,v_GS_OPERATING_SYSTEM.BuildNumber0
Order by [Build Number] Desc

Query Result

Related Posts:

• SCCM Dynamic Collection for Windows 10 / 11 Devices
• SCCM SQL Query for Windows 10 / 11 Version Summary
• Configure Windows 10 / 11 Device as Kiosk | Microsoft Intune
• Windows 10 / 11 Operating System Build Versions
• Windows 11 enrollment failed with error 0x800700b7
• Enroll Windows 11 Device to Intune through Azure AD Join method | Corporate Devices
• Invalid_Client error when joining Windows 10 device to Azure AD tenant
• Join Windows 10 Device to Azure Active Directory
• How to Obtain Hardware Hash for Manually Registring Devices with Windows Autopilot
• Bulk enrollment of Windows 10/ 11 Devices to Intune
• Intune – Windows 10 MDM- Basic troubleshooting
• That account info didn’t work – error when disconnecting Windows 10 / 11 Work or School account
• MDM Enroll: Device Credential, Failed (Unknown Win32 Error code : 0xcaa9001f