Custom Windows 7 report – workstation manufacturer & model & client version

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