Adding total HDD and RAM size Part 2

For full details, please see
http://social.technet.microsoft.com/Forums/en-ca/configmgrai/thread/7951b781-8354-490b-9b59-4fe4e68d1975
SELECT DISTINCT
    CS.Name0,
    CASE 
        WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
        WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
        Else 'Unknown'
    END as 'Chassis',
    BIOS.SerialNumber0, 
    CS.Manufacturer0, 
    CS.Model0,
    OS.Caption0, 
    OS.CSDVersion0, 
    SCUM.TopConsoleUser0,
    R.User_Name0 AS 'Last Logged', 
    BIOS.ReleaseDate0, 
    NAC.IPAddress0,
    NAC.DefaultIPGateway0,
    RAM.TotalPhysicalMemory0,
    Sum(LD.Size0)
FROM
    dbo.v_R_System R
    INNER JOIN dbo.v_GS_PC_BIOS BIOS ON BIOS.ResourceID = R.ResourceID 
    INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID = CS.ResourceID 
    INNER JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC ON R.ResourceID = NAC.ResourceID 
    INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON R.ResourceID = OS.ResourceID 
    LEFT OUTER JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM ON R.ResourceID = SCUM.ResourceID 
    INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ES on R.ResourceID = ES.ResourceID
    INNER JOIN dbo.v_GS_SYSTEM S on R.ResourceID = S.ResourceID
    INNER JOIN dbo.v_GS_X86_PC_MEMORY RAM on R.ResourceID = RAM.ResourceID
    INNER JOIN dbo.v_GS_LOGICAL_DISK LD on R.ResourceID = LD.ResourceID
WHERE     
    NAC.IPEnabled0 = 1
    AND (NOT (NAC.DefaultIPGateway0 IS NULL)) 
    AND S.SystemRole0 = 'Workstation'
Group by
    CS.Name0,
    CASE 
        WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
        WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
        Else 'Unknown'
    END,
    BIOS.SerialNumber0, 
    CS.Manufacturer0, 
    CS.Model0,
    OS.Caption0, 
    OS.CSDVersion0, 
    SCUM.TopConsoleUser0,
    R.User_Name0, 
    BIOS.ReleaseDate0, 
    NAC.IPAddress0,
    NAC.DefaultIPGateway0,
    RAM.TotalPhysicalMemory0

Leave a Reply

Your email address will not be published. Required fields are marked *