Tuesday, June 17, 2014

SCCM Inventory Report (Computer Manufacturer, Model, Description, Bios Serial, Computer Name, Total RAM, Total Hard Drive, Operating System, Service Pack, User and Domain)


Script below will extract Computer Manufacturer, Model, Description, Bios Serial, Computer Name, Total RAM, Total Hard Drive, Operating System, Service Pack, User and Domain.


se sms_bah
SELECT  distinct
  CS.Manufacturer0 as 'Manufacturer',
  CS.Model0 as 'model',
    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 'Description',
 BIOS.SerialNumber0 as 'Bios serial',
 CS.name0 as 'Computer Name',
 RAM.TotalPhysicalMemory0 as 'Total Memory',
 sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
 OS.Caption0 as 'OS',
 CSDVersion0 as 'Service Pack',
 CS.UserName0 as 'User',
 CS.domain0 as 'Domain'
from
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID
right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ES on SYS.ResourceID = ES.ResourceID
where
 LDisk.DriveType0 =3
group by
  CS.Manufacturer0,
  CS.Model0,
  ChassisTypes0,
  BIOS.SerialNumber0,
  CS.Name0,
  RAM.TotalPhysicalMemory0,
  OS.Caption0,
  CSDVersion0,
  CS.Username0,
  CS.domain0;