Close Menu
    Wednesday, December 18

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

    Eswar KonetiBy 3 Mins Read SCCM 2007 7,969 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 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

    Share.

    Related Posts

    18 Comments

    1. Kaushik Sivanantham on

      I would like to get an Hardware inventory report with the details of all the users like:

      user id, machine serial number,model number,monitor serial number,keyboard,mouse serial numbers and models via SCCM.

      Reply
      • Eswar Koneti on

        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
      • Eswar Koneti on

        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
    3. Son on

      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
      • Eswar Koneti on

        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
    5. Bogdan on

      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
      • Eswar Koneti on

        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
    7. ITKROPLIS on

      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

    This site uses Akismet to reduce spam. Learn how your comment data is processed.