Select Page

Configuration Manager Report (Most Amazing Hardware Inventory SQL Report)

Configuration Manager Report (Most Amazing Hardware Inventory SQL Report)
Advertisement

Configuration manager report

This blog post talks about how to create a detailed configuration manager report, that can be considered a unique report in the internet. The reason why this report is unique, is the way it aggregate information, and help prevent duplicate records and filter orphan devices. To do that, I had to dig deep and understand the schema of configuration manager SQL database and the relationship between tables. After that, I would figure out the main records to query, and what columns to use for my filtering and aggregations. Sounds geeky I know!

If you are a beginner in configuration manager, you might be using the SCCM queries (WQL), which is based on WMI name spaces. I can argue that the best way to get rich queries and information from configuration manager, is by using the SQL report engine and native SQL queries. Usually the ResourceID  is the main identifier of resources hosted inside the configuration manager SQL database.

Tip: I created a blog post to list all Windows update package size, so  that you can have an idea of your patching software update group before downloading them and perhaps congest your network. Check my configuration manager update size post for more details.

Why this can be hard and challenging?

While most of you want to jump directory and try the report, I want to spend a little time talking about the challenges I faced. You can skip this section if you want to try the report right away in your environment and enjoy the wonderful results I hope.
I hope by explaining how I thought about this report, it would make it easier for you to go and tweak the report query to fit your needs, or perhaps to add more information that you might need in your environment.

The first and biggest challenge is to understand the configuration manager SQL database schema, and to find our where the needed data resides. There are many tables out there, but the built-in SQL views inside the configuration manager database helped me a lot in my JOIN statements.
I hate when the report comes back with duplicate records. This is when you have two records for the same device or machine appearing in your report. Each record for the same machine represents a certain meaning or goal.

Say for example that a machine has 4 GB of physical memory installed in four different memory slots, that is 1 GB physical item per slot. Now, if you do a JOIN into the memory table to get the memory information for a device, you will get four records for that machine, with each record representing a slot number. I do not believe you want four records for the same device, just because it contains four memory slots with 1 GB physical memory installed in each.

Configuration Manager Report 1

Another good example for duplicate records is duplicates due to Windows versions. If you have a device with Windows 7 operating system, and then you upgraded that device to Windows 10, then there is a good chance you will see two records for the same device, one with Windows 7 in the O.S column, and one with Windows 10. Each record will have different ResourceID  value because you formatted the device before installing a new Windows 10 on it.

It took me long time to investigate duplicate records, and how to sort out each duplicate occurrence. One of the techniques I used is looking at every duplicate record for the same ResourceID  and take the one that last one seen online based on the last seen online time value.

In case of duplicates due to memory slots, like the one shown in the previous table, I would aggregate those records belonging to the same ResourceID  into one record and do a simple addition of the physical memory by adding the memory value in each physical slot. So, in the previous table, I would show one device with 4 GB total RAM in the Physical Memory column.
Finally, I recently added a variable in the SQL query called ( @BackInTime ) that you can use to filter the results according to the machines who recently reported information. The value is set to 30 days initially, and this means that we will only get results from machines that reported data in the last 30 days.

To write a SQL query that solves all the previous challenges, I had to start with the v_R_System SYS  view, and then eliminate duplication by using the MAX(Creation_Date0) as my criteria. From there, I started to JOIN other tables, and each time the JOIN is performed, I am eliminating duplication by a way or another. The GROUP BY statement has most of the logic that solves the duplication criteria.

What information the report will provide?

There is no limit on the information that can be pulled here. As long as the information exists in the configuration manager database, it can be pulled easily. I made a choice to collect the following items that can become handy for every configuration manager administrator. You can easily extend the items to be pulled by doing a simple JOINT statement to the SQL table that contains that information, and add them to the SELECT  and GROUP BY  lines.

  • Machine Name: without any duplicates in the way
  • Active Directory Site
  • User Name
  • Top User : the most user appearing to log to this machine
  • Windows Version: the last Windows version installed, removing any duplicates in the way
  • Windows Service Pack
  • Machine Manufacturer
  • Machine Model
  • Serial Number
  • BIOS Date
  • BIOS Version 
  • Managed Date: the date in which SCCM started to manage this machine
  • Physical Memory: Aggregate memory installed in all memory slots, removing any duplicates in the way
  • Number of memory slots
  • Type of the machine: X64 or X86
  • Logical Disk Size in GB
  • CPU Information [New]
    • CPU Type
    • Number of sockets
    • Number of cores
    • Number of logical processors
  • Windows Update Scan Information [New]
    • Last Windows Update scan
    • Last Windows Update scan error
    • Last location for Windows Update scan
  • PC Type: enumeration of all values of ChassisTypes0 . This can be one of the following:
    • when ‘1’ then ‘Other’
    • when ‘2’ then ‘Unknown’
    • when ‘3’ then ‘Desktop’
    • when ‘4’ then ‘Low Profile Desktop’
    • when ‘5’ then ‘Pizza Box’
    • when ‘6’ then ‘Mini Tower’
    • when ‘7’ then ‘Tower’
    • when ‘8’ then ‘Portable’
    • when ‘9’ then ‘Laptop’
    • when ’10’ then ‘Notebook’
    • when ’11’ then ‘Hand Held’
    • when ’12’ then ‘Docking Station’
    • when ’13’ then ‘All in One’
    • when ’14’ then ‘Sub Notebook’
    • when ’15’ then ‘Space-Saving’
    • when ’16’ then ‘Lunch Box’
    • when ’17’ then ‘Main System Chassis’
    • when ’18’ then ‘Expansion Chassis’
    • when ’19’ then ‘SubChassis’
    • when ’20’ then ‘Bus Expansion Chassis’
    • when ’21’ then ‘Peripheral Chassis’
    • when ’22’ then ‘Storage Chassis’
    • when ’23’ then ‘Rack Mount Chassis’
    • when ’24’ then ‘Sealed-Case PC’
    • else ‘Undefinded’

Have a look at the SQL query powering this configuration manager report?

Well, by now you can do directly to download the SQL query powering this configuration manager report and enjoy the results. I want though to write down here the SQL query I used for reference. You can go to your configuration server, open SQL management studio console, connect to the SQL server for your configuration manager, and copy and paste the below query and hit execute. Just make sure you are querying the CM database.

Well, here is how it looks like when you run it from the SQL management studio console. Of course the picture does not show everything, as you can scroll to the right and see a lot of device properties as described previously.

Configuration Manager Report 2

The best way to consume this report of course is by building a SQL report based on this query using SQL reporting services and configuration manager reporting point role. The result will look beautiful like shown below:

Configuration Manager Report 3

 

Get the SQL report now

You can copy and paste the query that was mention in the configuration manager report blog post, or you can download the SQL query file directly from Microsoft TechNet Gallery here. I posted it there so that you can see how many other people are using this configuration manager report and feel the impact. I am getting a lot of good feedback after posting this report, and this makes my day.

Another bonus for you is another similar SQL query for configuration manager, that looks exactly as this configuration manager report. The only difference is that this report will show you detailed memory slot information for each machine in case you want to have such detailed memory view. For each machine, you will get a record for each memory slot with a memory chip installed on it. If you have a machine with 2 memory slots, each with 1 GB physical memory, you will get two records for that machine, one with [Slot_Number = 1, RAM = 1 GB] and one with [Slot_Number = 2, RAM = 1 GB]. You can download this report from here. 

 

Filter by Collection ID

New Update May 2018

As requested by a lot of people, I am adding another inner Join to dbo.v_FullCollectionMembership  table to filter by Collection ID. The new query looks like this. Please replace CM100017 with your own collection ID.

 

Send a feedback

If you like this configuration manager report, please share with me what do you think about it, and perhaps share it. I will do my best to read all your feedback and answer any questions you might have. You can also refer to Microsoft documentation about reporting in configuration manager, and get information about list of built-in reports in configuration manager here.

About The Author

Ammar Hasayen

Ammar is a digital transformer, cloud architect, public speaker and blogger. He is considered a trusted advisory with the ability to quickly navigate complex multi-cultural organizations and continuously improve and motivate cross-functional teams to achieve higher productivity, collaboration, revenue gain and cross-group knowledge sharing. His contributions to the tech community helped him get awarded the Microsoft Most Valuable Professional. Ammar appears in a lot of global conferences, and he has many publications about digital transformation and next generation technologies.

30 Comments

  1. dave

    this truly is the most awesome sccm hardware inventory report i have seen. next i will be working on creating a sql report with the sql report builder that u also have another blog on…….great stuff indeed.

    i was curious to know if it is possible to tweak the query with the below:

    show only server os
    add column to indicate if the server is sql, exchange, sharepoint, terminal server, etc, etc

    Reply
    • Ammar Hasayen

      This so specialized functionality, kind of what SCOM do when it do agent discovery to detect roles. This script is just trying to get the information that is already stored in the CM database and aggregate it in away that no duplicates appear. Thanks for the suggestion though!

      Reply
  2. David

    Hi Ammar, Do you have a copy of this report that can be limited by collection?

    Reply
    • Ammar Hasayen

      I have updated the blog post to include Collection ID filter. hope you will like it

      Reply
  3. Dave

    Thank you! Excellent job. Interestingly, we have a tablet computer here that returns ChassisType 30 – undefined in your query.
    That ChassisType is nowhere to be found in any Microsoft documentation… 😉

    Reply
  4. Sabir

    Gr8 Work

    Reply
  5. Diego

    This is really something amazing, it almost fits my needs, but I’m not a DBA (or have any SQL skills) I’m struggling to edit this report to get information from the hardware inventory to bring also some installed applications.

    My Idea is kinda of use this report to check my baseline, for instance I wish to see office version, antivirus solution version and so on.

    Reply
  6. Greg Ivinson

    Hi, this looks like an awesome hardware report for SCCM.

    You may want to add 3 new Chassis types to your report (30, 31, & 32). Ive been tearing my hair out recently trying to find out why some of our laptop like devices from the work fleet havent been reporting when doing reports by Chassis number, and Ive since found out its because with the SMBIOS 3.0 changes, theres 3 new chassis types added…

    30 – Tablet
    31 – Convertible
    32 – Detachable

    (Source: https://thinkdeploy.blogspot.com/2017/04/new-enclosure-types-for-convertible.html)

    Reply
    • Ammar Hasayen

      What a feedback, let me look at this and see ! Thanks for sharing !

      Reply
      • Greg Ivinson

        Another point I would probably make, is that Lenovo stores like their computer model string seperately than the other manufacturers.

        While HP and Dell store their model display name under v_GS_COMPUTER_SYSTEM.Model0, Lenovo store the model display name under v_GS_COMPUTER_SYSTEM_PRODUCT. Version0 and store a Machine Type build number in the usual CS.Model value.

        Where on a HP CS.Model0 might be ‘HP Compaq Elite 8300 All-in-One PC’, a Lenovo might display ’10F4S05100′

        That ’10F4S05100′ is useful when you are trying to determine driver packs for the 2 or 3 different builds of Lenovo X1 Carbon’s, but doesnt seem to look as pretty to people who then need to manage that data.

        On a Lenovo v_GS_COMPUTER_SYSTEM.Model0 might be ’10F4S05100′ while v_GS_COMPUTER_SYSTEM_PRODUCT. Version0 = ‘ThinkCentre M900z’.

        Using a copy of your table Im playing tinkering with, I have added an extra column for CSP.Version0, as for now, I think I want both versions shown, but I see else where some people are merging the two values using CASE or IIF.

        Overall though so far, I like this report, it looks good!

        Reply
      • Dave

        There are several new Chassis types.

        My version of this query now has the following additions

        WHEN ’25’ THEN ‘Space-Saving’
        WHEN ’26’ THEN ‘Compact’
        WHEN ’27’ THEN ‘Advanced’
        WHEN ’28’ THEN ‘Blade’
        WHEN ’29’ THEN ‘Blade Enclosure’
        WHEN ’30’ THEN ‘Tablet’
        WHEN ’31’ THEN ‘Convertible’
        WHEN ’32’ THEN ‘Detachable’
        WHEN ’33’ THEN ‘IoT Gateway’
        WHEN ’34’ THEN ‘Embedded PC’
        When ’35’ THEN ‘Mini PC’
        When ’36’ THEN ‘Stick PC’

        Reply
  7. Naji

    Hi Ammar

    That is excellent job.
    I am new to sql queries.
    when I run the queries on Management studio I get the following error

    Msg 208, Level 16, State 1, Line 7
    Invalid object name ‘v_R_System’.

    Reply
    • Ammar Hasayen

      Make sure in the SQL management Studio that you are selecting the configuration manager database please. Hope this will work for you!

      Reply
  8. ersatyle

    I’ve been using this for some time now, so first thanks a lot for sharing it!
    with Windows 10 it become important to see which version / build it is, is there any way to add this in the report?
    thanks a lot.

    Reply
    • Ammar Hasayen

      Thanks for your feedback. I will look at this next week!

      Reply
  9. Gregory B

    Hi Again Amar,

    For your information, since SCCM 1710, the view “v_GS_LOGICAL_DISK” is now in GB according to documentation (https://docs.microsoft.com/en-us/sccm/core/plan-design/changes/whats-new-in-version-1710#cloud-distribution-point-support-for-azure-government-cloud——sms491428—).
    “As devices now include hard drives with sizes in the gigabyte (GB), terabyte (TB) and larger scales, this release changes the default unit (SMS_Units) used in many views from megabytes (MB) to GB. For example, the v_gs_LogicalDisk.FreeSpace value now reports GB units.”
    Meaning line 30 “SUM(D.Size0) / 1024 AS ‘Disk Size GB’,” you should remove “/1024” for those who are using latest versions of SCCM.

    Cheers
    Gregory B

    Reply
  10. Gregory B

    Hi again,

    Can you please confirm your value for memory and disk size are true? I can see some value that are not good (i.e.: memory 4096GB, 2*slot memory, result in the query momory size = 8192). Same for the Disk size.

    Cheers
    Gregory B

    Reply
    • Ammar Hasayen

      The question is , is that data in the SQL database correct or not. Remember, the script only reads data from SCCM database, so try to see if the data there is correct first.

      Reply
  11. kcarney81

    Great work! Any chance you can add CPU type? I need i3, i5 and i7s reported

    Reply
    • Ammar Hasayen

      Will try to look at this when I back from my travel.

      Reply
  12. Bob turner

    Hi,

    Firstly, thanks for a fantastic query!

    I am seeing some inconsistencies between the original query (TechNet gallery) and the new one May 2018.

    The original one brings back all object in the SCCM DB, whereas the new one seems to be missing a few hundred machines which is strange.

    Any ideas why this would be?

    Reply
  13. Valerio

    Hi Ammar.
    Very very very nice sql query. I need to add ipaddresses and mac addresses but for me is very difficult.
    Thanx in advance.
    Valerio

    Reply
    • Ammar Hasayen

      Hi, I had previous requests to add IP and MAC, but this will add a lot of complexity as it will break the one to one relation between a device and a record, as a device can have multiple IPs and MAC, and then we need to aggregate that to total number of IPs or MAC for example, which defeats the purpose of showing such records. Else, we will have duplicate records which defeats the purpose of the query which is to ensure there is one truth about each device.

      Reply

Leave a reply

Your email address will not be published. Required fields are marked *

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

Microsoft MVP

AMMAR - MVP - DARK BLUE

About Ammar

Digital Transformation | Microsoft MVP | Cloud Architect | Azure | Microsoft 365 |Modern Workplace | Cyber-Security | Speaker | Blogger | I Pluralsight Author| Jordan | http://me.ahasayen.com

Speaking at Microsoft Ignite

Microsoft Ignite Speaker

My Pluralsight Course

Pin It on Pinterest