#configmgr queries to get a normalized list of machines with Windows 2008 installed

This query lists all the machines with Windows 2008 showing the  OS Edition, and Service Pack.
*Requires Normalize CM

Sample result:

OS Manufacturer OS Name OS MajorVersion OS ServicePack OS Edition
Microsoft Windows Server 2008 2008 R2 NULL Enterprise x64
Microsoft Windows Server 2008 2008 R2 SP0 Datacenter x64
Microsoft Windows Server 2008 2008 R2 SP1 Datacenter x64
Microsoft Windows Server 2008 2008 R2 SP0 Enterprise x64
Microsoft Windows Server 2008 2008 R2 SP1 Enterprise x64
Microsoft Windows Server 2008 2008 R2 NULL For Itanium-Based Systems x64
Microsoft Windows Server 2008 2008 R2 SP0 For Itanium-Based Systems x64
Microsoft Windows Server 2008 2008 R2 SP0 Standard x64
Microsoft Windows Server 2008 2008 R2 SP1 Standard x64

 

Thease queries use the BDNA_HARDWARE_DATA class

WQL:

select System.Name, HARDWARE.IsVirtual, HARDWARE.OS_Manufacturer, HARDWARE.OS_Name, HARDWARE.OS_MajorVersion, HARDWARE.OS_MinorVersion, HARDWARE.OS_ServicePack, HARDWARE.OS_Edition from  SMS_R_System inner join SMS_G_System_BDNA_BDNAHARDWAREDATA_1_1 as HARDWARE on HARDWARE.SMSUniqueIdentifier = SMS_R_System.SMSUniqueIdentifier inner join SMS_R_System as System on System.SMSUniqueIdentifier = HARDWARE.SMSUniqueIdentifier where HARDWARE.OS_Name = “Windows Server 2008″ and HARDWARE.OS_MajorVersion = “2008 “

SQL:

SELECT v_R_System.Name0 as Name, Hardware.IsVirtual0 as IsVirtual, Hardware.OS_Manufacturer0 as OS_Manufacturer, Hardware.OS_Name0 as OS_Name, Hardware.OS_MajorVersion0 as OS_MajorVersion, Hardware.OS_MinorVersion0 as OS_MinorVersion, Hardware.OS_ServicePack0 as OS_ServicePack, Hardware.OS_Edition0 as OS_Edition

FROM v_R_System

INNER JOIN v_GS_BDNA_Hardware_Data0 Hardware ON Hardware.SMSUniqueIdentifier0=v_R_System.SMS_Unique_Identifier0

WHERE Hardware.OS_Name0 = ‘Windows Server 2008′ AND Hardware.OS_MajorVersion0 = ’2008′

email

Written by , Posted .