SCCM SQL Query for OS information from Discovery data

 V_GS_OperatingSystem depends upon Hardware inventory, if  HW is not colleced for the machine then it will be NULL.So, get the OS information from V_R_Sytem which has discovery data DDR.

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'