SELECT DISTINCT
COMSYS.Manufacturer0,
COMSYS.Model0,
Client.Version0,
Count(*)
FROM
v_GS_COMPUTER_SYSTEM COMSYS
JOIN v_FullCollectionMembership fcm ON COMSYS.ResourceID = FCM.ResourceID
JOIN v_GS_PROCESSOR PROC1 ON COMSYS.ResourceID = PROC1.ResourceID
JOIN v_GS_X86_PC_MEMORY MEM ON COMSYS.ResourceID = MEM.ResourceID
JOIN v_GS_OPERATING_SYSTEM OPSYS ON COMSYS.ResourceID = OPSYS.ResourceID
JOIN v_GS_LOGICAL_DISK LDISK ON COMSYS.ResourceID = LDISK.ResourceID and LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)
JOIN v_GS_Client0 CLIENT ON COMSYS.ResourceID = CLIENT.ResourceID
WHERE
(fcm.CollectionID='SMS00001')
AND (ROUND (ROUND(CONVERT (FLOAT ,mem.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) < 1024
OR ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 < 1
OR ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) < 16)
Group by
COMSYS.Manufacturer0,
COMSYS.Model0,
Client.Version0