quote:

SELECT DISTINCT sys.Netbios_Name0,
               csys.Manufacturer0,
               csys.Model0,
               enc.SerialNumber0,
               sys.AD_Site_Name0,
               sys.User_Domain0 + '\' + sys.User_Name0 AS "Domain\User",
               hwstat.LastHWScan,
               os.Caption0 AS "Operating System",
               os.CSDVersion0 AS "Service Pack Level",
               mem.TotalPhysicalMemory0 AS "Memory (KBytes)",
               (SELECT SUM(Size0)
                FROM   v_GS_LOGICAL_DISK
                       JOIN v_FullCollectionMembership
                         ON (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID)
                WHERE  v_GS_LOGICAL_DISK.ResourceID = sys.ResourceID
                       AND v_FullCollectionMembership.CollectionID = @CollectionID) AS "Disk Space (MB)",
               (SELECT SUM(v_GS_LOGICAL_DISK.FreeSpace0)
                FROM   v_GS_LOGICAL_DISK
                       JOIN v_FullCollectionMembership
                         ON (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID)
                WHERE  v_GS_LOGICAL_DISK.ResourceID = sys.ResourceID
                       AND v_FullCollectionMembership.CollectionID = @CollectionID) AS "Free Disk Space (MB)"
FROM     dbo.v_R_System sys
        JOIN dbo.v_GS_OPERATING_SYSTEM os
          ON (os.ResourceID = sys.ResourceID)
        JOIN dbo.v_GS_SYSTEM_ENCLOSURE enc
          ON (enc.ResourceID = sys.ResourceID)
        JOIN dbo.v_GS_COMPUTER_SYSTEM csys
          ON (csys.ResourceID = sys.ResourceID)
        JOIN dbo.v_GS_X86_PC_MEMORY mem
          ON (mem.ResourceID = sys.ResourceID)
        JOIN dbo.v_FullCollectionMembership fcm
          ON (fcm.ResourceID = sys.ResourceID)
        JOIN dbo.v_GS_WORKSTATION_STATUS hwstat
          ON (hwstat.ResourceID = sys.ResourceID)
        JOIN dbo.v_GS_LOGICAL_DISK ldisk
          ON (ldisk.ResourceID = sys.ResourceID)
             AND ldisk.DeviceID0 = SUBSTRING(os.WindowsDirectory0,1,2)
WHERE    fcm.CollectionID = @CollectionID
ORDER BY sys.Netbios_Name0