dotps1
commented
9 years ago I just checked my SCCM 2012 SP1 DB, and that BEM_BIOS_DATA table doesn't exist, there is a PC_BIOS_DATA table, but the SKU value doesn't exist in it. im not sure what i want to do about this in the code, i suppose i can look up a good way on how to detect if its SCCM 2012 or SCCM 2012 R2, using WMI perhaps, then change query accordingly. Any way, can you deploy a test client setting, inventorying the MS_SystemInformation WMI Class, and see if it creates the matching table? then the query would work on both systems.
SCCM 2012 R2 must have captured HKEY_LOCAL_MACHINE\HARDWARE\DESCRIPTION\System\BIOS with configuration.mof. For me this created a "BEM_BIOS_DATA" table. The following code works in SCCM 2012 R2:
$sql = "Select distinct sys.Name0 AS ComputerName, LTRIM(RTRIM(BIOS.SerialNumber0)) AS SerialNumber, CS.Manufacturer0 AS ProductManufacturer, CS.Model0 AS ProductModel, BIOSD.SystemSKU00 AS ProductID, SYS.AD_Site_Name0 AS ADSiteName, sys.User_Name0 AS Username, CONVERT(varchar, WS.LastHWScan, 120) AS LastHardwareScan From v_R_System SYS left join v_GS_COMPUTER_SYSTEM CS on sys.Name0=cs.Name0 left join v_GS_PC_BIOS BIOS on sys.ResourceID=bios.ResourceID left join v_GS_OPERATING_SYSTEM OS on sys.ResourceID=OS.ResourceID left join v_GS_WORKSTATION_STATUS WS on sys.ResourceID=WS.ResourceID left join BEM_BIOS_DATA BIOSD on sys.ResourceID=BIOSD.MachineID LEFT OUTER JOIN v_GS_SYSTEM_ENCLOSURE ENC ON BIOS.SerialNumber0 = ENC.SerialNumber0 LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM ON ENC.ResourceID = CS.ResourceID where (CS.Manufacturer0='Hewlett-Packard' or CS.Manufacturer0='HP') and LEN(LTRIM(RTRIM(BIOS.SerialNumber0)))>=1 and LTRIM(RTRIM(BIOS.SerialNumber0)) IS NOT NULL and LEN(LTRIM(RTRIM(CS.Model0)))>=1 AND BIOSD.SystemSKU00 IS NOT NULL and LEN(LTRIM(RTRIM(BIOSD.SystemSKU00)))>=1 AND SYS.client0=1 AND SYS.Obsolete0=0 AND SYS.Active0=1"