SCCM SQL Query Windows 10 Version Count Dashboard

Let’s see how to find out SCCM SQL Query Windows 10 Version Count | Dashboard | SCCM. The different versions of Windows 10 use SQL queries. The total number of Windows 10 versions dashboard from SQL query.

Since now most of the environment is either moved or moving towards windows 10 so it is important to track down different versions in your environment. No registration No Email ID is required to get this query.

Related Post – Windows 10 Version Numbers & Build Numbers Major Minor Build Rev

• SCCM Report for Windows 11 Version Count Dashboard
• Convert WQL Queries to SQL Queries using SCCM Trick

Introduction

If we are managing a large environment then it is always good to keep the environment on the same version of Windows 10. How to do that? so there are two options one is via Enablement package or using Windows 10 Upgrade task sequence both are completely different topics for which you can visit Upgrade Windows 10.

How to track down different Windows 10 versions in your environment and how it’s progressing?

So here it is sharing a SQL Query to fetch these details and then subscribing to your mailbox to track the progress.

SQL Query to Find Windows 10 Version Count

This SQL Query will help you to use the ‘when’ statement and then declare it according to your environment. I have taken all the Windows 10 versions released to date in this query you can add others for future releases.

• 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 10 version details.
• Click on the Execute button.

Note!– To check Windows 10 release notes Windows 10 Release notes

Raw Data – SCCM SQL Query Windows 10 Version Count Dashboard

Let’s find the SQL query for the total number of Windows 10 Versions available in your environment. The SCCM SQL Query Windows 10 Version Count 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 = '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 20H1'
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

Windows 10 version Dashboard

Let’s create the dashboard to check the Windows 10 version count details from the following query.

You will need to copy the following SQL query to get the Windows 10 Dashboard and count of versions.

select v_GS_OPERATING_SYSTEM.BuildNumber0, "Windows 10 Version"=
case v_GS_OPERATING_SYSTEM.BuildNumber0
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'
End,count(*) [Total]
from v_GS_OPERATING_SYSTEM
where v_GS_OPERATING_SYSTEM.Caption0 like 'Microsoft Windows 10%'
group by v_GS_OPERATING_SYSTEM.BuildNumber0
order by [Total] desc

Results Windows 10 Version Count and Dashboard

Let’s check the following results of the Windows 10 version count as a dashboard. The results SCCM SQL Query Windows 10 Version Count Dashboard.

How to create the same report in Report builder follow SQL query in report builder

Resources

• https://www.anoopcnair.com/windows-10-1909-upgrade-sccm-task-sequence/

Author

Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.