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