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

Posted on December 22, 2011 by | 16 Comments | 837 Views

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

 

Related Posts

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

    1. Eswar Koneti · Edit

      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
  2. Son · Edit

    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. Eswar Koneti · Edit

      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
  4. Bogdan · Edit

    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. Eswar Koneti · Edit

      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
  6. ITKROPLIS · Edit

    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 Alexander Cancel reply