SCCM SQL query for OS build numbers

If we try to get OS and Build number information from V_GS_OperatingSystem, it may not uptodate if Hardware inventory is not updated.

If we need OS and build number information, even if the Client is not updated with recent Hardware inventory then we can use V_R_Sytem.

select sys.Name0 as 'Hostname',

sys.User_Name0 as 'Username',

case 

when sys.Operating_System_Name_and0 like '%server%' then 'Server' 

when sys.Operating_System_Name_and0 like '%workstation 6%' then 'Windows 7'

when sys.Operating_System_Name_and0 like '%workstation 5%' then 'Windows XP'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%19045%'  then 'Windows 10 Build 22H2'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%22621%'  then 'Windows 11 Build 22H2'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%17134%'  then 'Windows 10 Build 1803'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%22000%'  then 'Windows 11 Build 21H2'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%19044%'  then 'Windows 10 Build 21H2'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%19043%'  then 'Windows 10 Build 21H1'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%19042%'  then 'Windows 10 Build 20H2'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%19041%'  then 'Windows 10 Build 2004'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%18363%'  then 'Windows 10 Build 1909'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%18362%'  then 'Windows 10 Build 1903'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%17763%'  then 'Windows 10 Build 1809'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%16299%'  then 'Windows 10 Build 1709'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%15063%'  then 'Windows 10 Build 1703'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%14393%'  then 'Windows 10 Build 1607'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%10240%'  then 'Windows 10 Build 1507'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%10586%'  then 'Windows 10 Build 1511'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 is NULL then 'Windows 10 Build not specified'

else sys.Operating_System_Name_and0

end as 'O/S',

cs.Manufacturer0 [Make],

cs.Model0 [Model],

bios.SMBIOSBIOSVersion0

from V_R_system sys

left join v_gs_operating_system os on sys.ResourceID=os.ResourceID

left join v_FullCollectionMembership col on sys.ResourceID = col.ResourceID

LEFT JOIN v_GS_COMPUTER_SYSTEM cs on sys.ResourceID=cs.ResourceID

left JOIN v_GS_PC_BIOS BIOS ON sys.ResourceID=BIOS.ResourceID

where col.CollectionID = 'DEV01F7F'