ConfigMgr SQL Query Windows 10 Version Count | Dashboard | SCCM

3
SQL Query Windows 10 Version

Let’s see how to find out ConfigMgr SQL Query Windows 10 Version Count | Dashboard | SCCM. The different versions of Windows 10 using SQL query. 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 required to get this query.

Related PostWindows 10 Version Numbers & Build Numbers Major Minor Build Rev

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 a completely different topic 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 subscribe 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 application deployment with collection details.
  • Click on the Execute button.
Windows 10 Version SQL Query - ConfigMgr SQL Query Windows 10 Version Count | Dashboard | SCCM
Windows 10 Version SQL Query – ConfigMgr SQL Query Windows 10 Version Count | Dashboard | SCCM

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

Raw Data

Let’s find the SQL query for the total number of Windows 10 Versions available in your environment.

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 = '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

Windows 10 version Dashboard

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

ConfigMgr SQL Query Windows 10 Version Count | Dashboard | SCCM
ConfigMgr SQL Query Windows 10 Version Count | Dashboard | SCCM
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

Let’s check the results.

ConfigMgr SQL Query Windows 10 Version Count | Dashboard | SCCM
ConfigMgr SQL Query Windows 10 Version Count | Dashboard | SCCM

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

Resources

3 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.