Easily Find Windows 10 Version using SCCM SQL Query

In SCCM, you can find Windows 10 version using SCCM SQL query. The SQL query outputs a count of all Windows 10 versions.

There are alternate ways to find Windows 10 versions and build numbers using SCCM. For example, you can use ConfigMgr CMPivot Query to find Windows 10 Version.

If you want to query the real-time data, the CMPivot comes for help. The ConfigMgr CMPivot query to find Windows 10 version is really useful.

One of the challenges faced by IT admins is to find the count of Windows 10 machines and their builds and version. This is especially relevant when you want to perform Windows 10 in-place upgrade.

I would recommend reading the Windows Release health, the official information on Windows 10 releases and servicing milestones.

Using the SQL query, you can find Windows 10 version number and build number easily in SCCM. You can also create a Windows 10 device collection based on either builds or edition.

Find Windows 10 Version using SCCM SQL Query

Let’s learn how to find Windows 10 version using SCCM SQL Query. First you need the access to SCCM database to run the query. Launch the SQL Server Management studio (SSMS) and connect to database engine.

Expand Databases and right click SCCM database and select New Query.

Find Windows 10 Version using SQL Query
Find Windows 10 Version using SQL Query

In the query window, paste the below SCCM SQL query to find the Windows 10 version. To run the query, click Execute.

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 10 Build Number',
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
Find Windows 10 Version using SCCM SQL Query
Find Windows 10 Version using SCCM SQL Query

The above query returns the Windows 10 version based on the build numbers. Whenever you see any new version of Windows 10, you can add a new build number and update the query.

After you run the SCCM SQL query, you should find all the Windows 10 build numbers and Windows 10 version numbers listed in the query results.

Find Windows 10 Version using SCCM SQL Query
Find Windows 10 Version using SCCM SQL Query

To export the results into an Excel file, all you need to do is press CTRL+A to select and CTRL+C to copy the results.

I have published a video tutorial to find Windows 10 version using SQL Query.

Find Windows 10 versions using SQL Query


  1. Excelente, muchas gracias! hablando de cpivot, hice un pequeño cambio para que me trajera el nombre del recurso que tiene la compilacion. saludos

    | where (ProductType == 1)
    | project device, 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 == ‘19044’), ‘Windows 10 21H2’, (BuildNumber > ‘19045’), ‘Windows 10 Other’, BuildNumber )
    | where (Edition == ‘Windows 10 20H2’)

  2. Hi,

    Can you please me with SQL query to pull all Server list in sccm with Version details

  3. Hi,
    Thank you very much, for the useful information, How can I add to this query information about Last Online Time and Last Activity.

    Thank in advance

Leave a Reply

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