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
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

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

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

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 to Son Cancel reply