Add Serial Number and Manufacturer to “Computers that do not meet the minimum system requirements for Windows 7” report

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/2ed1b090-2b2b-4a3f-9359-e1ac5fd0adba

SELECT DISTINCT 

  SYS.Netbios_Name0, 

  CS.Manufacturer0,

  BIOS.SerialNumber0,

  fcm.SiteCode, 

  ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) AS FreeSpace0,

  ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 AS 'Processor (GHz)', 

  ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) AS C083, 

  ISNULL(CONVERT(VARCHAR(7), MAX (DA.AdapterRAM0) / 1024), 'UNKNOWN') AS AdapterRAM,

  OPSYS.Caption0 AS 'Operating System',

  CONVERT(FLOAT, LEFT(OPSYS.Version0, 3)) Version

FROM 

  dbo.v_FullCollectionMembership fcm

  JOIN dbo.v_R_System SYS ON fcm.ResourceID=SYS.ResourceID

  JOIN dbo.v_GS_PROCESSOR PROC1 ON SYS.ResourceID = PROC1.ResourceID

  JOIN dbo.v_GS_X86_PC_MEMORY MEM ON SYS.ResourceID = MEM.ResourceID

  LEFT OUTER JOIN dbo.v_GS_VIDEO_CONTROLLER DA ON SYS.ResourceID = DA.ResourceID

  JOIN dbo.v_GS_OPERATING_SYSTEM OPSYS ON SYS.ResourceID = OPSYS.ResourceID

  JOIN dbo.v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID AND LDISK.DeviceID0 = SUBSTRING(OPSYS.WindowsDirectory0,1,2)

  JOIN dbo.v_GS_COMPUTER_SYSTEM CS on SYS.ResourceID = CS.ResourceID

  JOIN dbo.v_GS_PC_BIOS BIOS  on SYS.ResourceID = BIOS.ResourceID

WHERE 

  ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) >= 1024

  AND ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 >= 1 

  AND ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) >= 16

  AND fcm.CollectionID = @CollID

GROUP BY 

  SYS.Netbios_Name0, 

  CS.Manufacturer0,

  BIOS.SerialNumber0,

  fcm.SiteCode, 

  LDISK.FreeSpace0, 

  PROC1.MaxClockSpeed0, 

  MEM.TotalPhysicalMemory0, 

  OPSYS.Caption0, 

  OPSYS.Version0