SCCM configMgr inventory report like Asset number Serial number Manufacturer Model Processor IP Address

Long back i posted report  http://eskonr.com/2009/12/sccm-report-for-computers-asset-information-including-sn-number-and-model-name/ to get computer information like computer name,Serial number,Processor,Physical memory,service Pack,computer model etc

In this post ,i have modified the report little bit from previous post to add more fields that gives you lot more information about the computer . It is  very simple and straight forward report that just use Where class

what columns do you get after running this report ? Name,Serialnumber, Manufacturer,Model,Processor,Memory (KBytes),Size (MBytes),MAC Adress,IP Adress,AD Site,Last user logged in,Operating System,Service Pack,Creationdate in SMS,Last Hardware Scan

SELECT distinct
sys.name0 as 'Computer Name',
sys.AD_Site_Name0 as 'AD Site',
CS.domain0 as 'Domain',
sys.User_Name0 as 'Last Logged On User',
BIOS.Version0 as 'Bios Version',
BIOS.Name0 as 'BIOS Name' ,
CONVERT(VARCHAR(26), BIOS.ReleaseDate0, 101) as 'BIOS Relase Date',
SE.SerialNumber0 as 'System Enclosure serial',
CS.Manufacturer0 as 'Manufacturer',
CSP.Version0 as 'Model',
cs.SystemType0 as 'System Type',case
WHEN sc.chassistypes0 LIKE '2' THEN 'Blade Server'
WHEN sc.chassistypes0 LIKE '3' THEN 'Desktop'
WHEN sc.chassistypes0 LIKE '4' THEN 'Low-Profile Desktop'
WHEN sc.chassistypes0 LIKE '5' THEN 'Pizza-Box'
WHEN sc.chassistypes0 LIKE '6' THEN 'Mini Tower'
WHEN sc.chassistypes0 LIKE '7' THEN 'Tower'
WHEN sc.chassistypes0 LIKE '8' THEN 'Portable'
WHEN sc.chassistypes0 LIKE '9' THEN 'Laptop'
WHEN sc.chassistypes0 LIKE '10' THEN 'Notebook'
WHEN sc.chassistypes0 LIKE '11' THEN 'Hand-Held'
WHEN sc.chassistypes0 LIKE '12' THEN 'Mobile Device in Docking Station'
WHEN sc.chassistypes0 LIKE '13' THEN 'All-in-One'
WHEN sc.chassistypes0 LIKE '14' THEN 'Sub-Notebook'
WHEN sc.chassistypes0 LIKE '15' THEN 'Space Saving Chassis'
WHEN sc.chassistypes0 LIKE '16' THEN 'Ultra Small Form Factor'
WHEN sc.chassistypes0 LIKE '17' THEN 'Server Tower Chassis'
WHEN sc.chassistypes0 LIKE '18' THEN 'Mobile Device in Docking Station'
WHEN sc.chassistypes0 LIKE '19' THEN 'Sub-Chassis'
WHEN sc.chassistypes0 LIKE '20' THEN 'Bus-Expansion chassis'
WHEN sc.chassistypes0 LIKE '21' THEN 'Peripheral Chassis'
WHEN sc.chassistypes0 LIKE '22' THEN 'Storage Chassis'
WHEN sc.chassistypes0 LIKE '23' THEN 'Rack-Mounted Chassis'
WHEN sc.chassistypes0 LIKE '24' THEN 'Sealed-Case PC' ELSE 'Unknown' END AS [Chassis Type],
CS.Roles0 as 'Role',
OS.Caption0 as 'OS',
CONVERT(VARCHAR(26), OS.InstallDate0, 101) as 'OS Install Date',
CPU.NumberOfLogicalProcessors0 as 'Number of CPU',
CPU.MaxClockSpeed0 as 'Max CPU Speed',
CPU.Name0 as 'CPU Model' ,
hdd.Model0 as 'HD Model',
ROUND (CONVERT (FLOAT, LDisk.Size0) / 1024, 2) AS 'Hard Disk Space',
ROUND (CONVERT (FLOAT, LDisk.FreeSpace0) / 1024, 2) AS 'Free Space',
ROUND (ROUND(CONVERT (FLOAT ,RAM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) AS 'Total Memory',
CONVERT(VARCHAR(26), WKS.Lasthwscan, 101) as 'Last Hardware Scan',
DATEDIFF(dd, WKS.Lasthwscan, GETDATE())AS 'Number of Days Since Last HW Scan',
CONVERT(VARCHAR(26), OS.LastBootUpTime0, 120) as 'Last Boot Time',
DATEDIFF(dd, OS.LastBootUpTime0, GETDATE())AS 'Number of Days Since Last Bootup'
from v_R_System sys
LEFT JOIN v_GS_COMPUTER_SYSTEM CS on cs.ResourceID = sys.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT CSP on csp.ResourceID = sys.ResourceID
LEFT JOIN v_GS_PC_BIOS BIOS on BIOS.ResourceID = sys.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OS on OS.ResourceID = sys.ResourceID
LEFT JOIN V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = sys.ResourceID
LEFT JOIN v_GS_Logical_Disk LDisk on LDisk.ResourceID = sys.ResourceID
LEFT JOIN v_GS_Processor CPU on CPU.ResourceID = sys.ResourceID
LEFT JOIN v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = sys.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS WKS on sys.resourceid = wks.resourceid
LEFT JOIN v_GS_DISK hdd on hdd.ResourceID = sys.ResourceID
left join v_gs_system_enclosure sc on sc.resourceid=sys.resourceid
order by 1

You can also other custom values from SQL Views ,please take a look Creating Custom Reports By Using Configuration Manager 2007 SQL Views http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=22052

18 Responses to "SCCM configMgr inventory report like Asset number Serial number Manufacturer Model Processor IP Address"

    1. That is going to be complete custom solution .All the information which you asked is already available in SCCM except monitor info and mouse serial etc for which you may have to use custom solution or invest time to get it done.
      For the rest ,you can use SQL code given in the blog post.

      Regards,
      Eswar

      Reply
    1. You may have login and check the clients that have no SN what is missing in those. What model are those missing SN ?

      Regards,
      Eswar

      Reply
  1. Thank you for taking the time to generate the report. Can you include internet explorer version in the report? Basically what i like to have is computers that have last user log on and IE version.

    Thank you

    Reply
    1. software scan to retrive the information for .exe files in CM12 is very slow.Garth has blogged post on this but would recomend to go through sherry method to retrive the internet explorer info via hardware inventory.

      Reply
  2. Hello,

    Very helpful !

    What if I want to prompt for a Collection ? because if I run it this it works, but listing all the systems.

    Regards.

    Reply
    1. here you go :

      SELECT
      A.Name0,
      MAX (B.SerialNumber0) AS 'Serialnumber',
      A.Manufacturer0,
      A.Model0, C.Name0 AS 'Processor',
      D.TotalPhysicalMemory0 AS 'Memory (KBytes)',
      MAX ( E.Size0 ) AS 'Size (MBytes)',
      MAX (F.MACAddress0) AS 'MAC Adress',
      MAX (F.IPAddress0) AS 'IP Adress',
      G.AD_Site_Name0 AS 'AD Site',
      MAX (A.UserName0) AS 'Last user logged in',
      H.Caption0 AS 'Operating System',
      H.CSDVersion0 AS 'Service Pack',
      G.Creation_Date0 AS 'Creationdate in SMS',
      I.LastHWScan
      FROM
      v_GS_COMPUTER_SYSTEM A,
      v_GS_PC_BIOS B,
      v_GS_PROCESSOR C,
      v_GS_X86_PC_MEMORY D,
      v_GS_DISK E,
      v_GS_NETWORK_ADAPTER_CONFIGUR F,
      v_R_System G,
      v_GS_OPERATING_SYSTEM H,
      v_GS_WORKSTATION_STATUS I,
      v_FullCollectionMembership fcm
      WHERE
      A.ResourceID = B.ResourceID AND
      A.ResourceID = C.ResourceID AND
      A.ResourceID = D.ResourceID AND
      A.ResourceID = E.ResourceID AND
      A.ResourceID = F.ResourceID AND
      A.ResourceID = G.ResourceID AND
      A.ResourceID = H.ResourceID AND
      A.ResourceID = I.ResourceID and
      fcm.ResourceID=A.ResourceID and
      fcm.CollectionID='CEN00F49'
      GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan

      Reply
  3. Last time I created a new report. Supposed to build it in two layers. If I try to build a report from the example (single layer), we obtain the error. Specifically references the need to properly create?

    Reply

Leave a Reply