SCCM SQL Query for Windows Build Version and Support State

 select 

VRS.name0 as [Computer Name],

OS.Caption0 as [Operating System],

VRS.User_Name0 , 

WSLN.Value as [Windows 10 build Version],

Case WSLN.Value

WHEN 'Windows 10 2015 LTSB' THEN 'Windows 10, version 2015'

WHEN 'Windows 10 2016 LTSB' THEN 'Windows 10, version 2016'

ELSE WSLN.Value

END as Value,

CASE WSS.Branch  

         WHEN '0' THEN 'Current Branch'  

         WHEN '1' THEN 'Current Branch for Business'  

         WHEN '2' THEN 'Long Term Servicing Branch'  

END as 'Branch',  

CASE WSS.State 

WHEN '1' THEN 'Release Ready'  

WHEN '2' THEN 'Supported'  

WHEN '3' THEN 'Expires Soon'  

WHEN '4' THEN 'End of Life'   

END as 'State',

CHS.ClientStateDescription , 

CHS.LastActiveTime

from v_R_System VRS

JOIN vSMS_WindowsServicingStates WSS on VRS.OSBranch01 = WSS.Branch and VRS.Build01 = WSS.Build

JOIN vSMS_WindowsServicingLocalizedNames WSLN on WSS.Name = WSLN.Name

join v_GS_OPERATING_SYSTEM OS on VRS.ResourceID = OS.ResourceID

join v_CH_ClientSummary CHS on VRS.ResourceID = CHS.ResourceID