ConfigMgr Make Model Power BI Report

In this installment of the Getting Started with ConfigMgr and Power BI; we create a simple hardware report.  The ConfigMgr Make Model Power BI Report gives the ability to drill down unique hardware configurations.  Ranging from make and model, BIOS versions, and operating system version.  

This post is part of a series of posts in my “Getting Started with ConfigMgr and Power BI”

Getting Started with ConfigMgr and Power BI
Core ConfigMgr Queries for Power BI
(this post)
ConfigMgr Client Boot Time Power BI Report

Please considered that I don’t take 100% credit for the below queries.  These are made up from multiple sources in our community.  Without the community this may have not been possible. 

The below sections outline creating the ConfigMgr Make Model Power BI Report.  With attention to the SQL query used for pulling device’s hardware information.  

ConfigMgr Make Model Power BI Report

Getting Setup

For the step by step portion we will utilize the direct query template we created in the second series. 

Core ConfigMgr Queries for Power BI

We start with direct query template because we can always convert to Import.  We cannot convert Import to Direct Query. 

Get to the Queries Section

  • Fill in the parameters and select Load

Template Parameters

  • Select Edit Queries

Edit Queries

ConfigMgr Make Model Power BI Report

The subsections require getting data from SQL query source, follow the below steps. 

  • Right click on your ConfigMgr folder and select New Query > SQL Server

Query SQL Server

  • Change the server and database values to your ConfigMgr parameters

ConfigMgr SQL Parameters

ConfigMgr Make and Model Report

The below query pulls in the device’s manufacturer, model, serial number, BIOS version, its release date, and asset tag.  Lenovo put their friendly name in the version property of Win32_ComputerSystemProduct.  We will then create some basic visuals and table layout. 

For this tutorial we will be utilizing the Direct Query template.  You can always convert Direct Query to Import. 
The Win32_ComputerSystemProduct is not a ConfigMgr standard hardware inventory class.  If you have Lenovo in your environment, you will need to enable this.  Please click here to learn more.
  • Create a new SQL Server query and paste the following SQL statement
SELECT s.[ResourceID],
    CASE cs.[Manufacturer0]
        WHEN 'Hewlett-Packard' THEN 'HP'
        ELSE cs.[Manufacturer0]
    END as [Manufacturer],
	CASE
		WHEN cs.[Manufacturer0] = 'Lenovo'
		THEN csp.[Version0]
		ELSE cs.[Model0]
	END as [Model],
	b.[SerialNumber0] as [SerialNumber], 
    b.[SMBIOSBIOSVersion0] as [BIOSVersion], 
    b.[ReleaseDate0] as [Release Date], 
    e.[SMBIOSAssetTag0] as [AssetTag],
	cs.[TotalPhysicalMemory0]/1024 as [Memory(MB)],
	pro.[Name0] as [Processor],
	CAST((ld.[Size0] *1.00 /1024) as decimal(10,2)) as [Total Size],
	CAST((ld.[FreeSpace0] *1.00 /1024) as decimal(10,2)) as [Free Space],
	CAST(((ld.[FreeSpace0] * 1.00 / ld.[Size0]) * 100.00) as int) as [% Free]
FROM v_R_System as s
LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM as cs ON cs.[ResourceID] = s.[ResourceID]
LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM_PRODUCT as csp on csp.[ResourceID] = s.[ResourceID]
LEFT OUTER JOIN v_GS_PC_BIOS as b on b.ResourceID = s.[ResourceID]
LEFT OUTER JOIN v_GS_SYSTEM_ENCLOSURE as e ON e.[ResourceID] = s.[ResourceID]
LEFT OUTER JOIN v_GS_LOGICAL_DISK as ld on ld.[ResourceID] = s.[ResourceID] AND ld.[DeviceID0] = 'C:'
LEFT OUTER JOIN v_GS_PROCESSOR as pro on pro.[ResourceID] = s.[ResourceID]
WHERE s.[Obsolete0] = 0 AND s.[Decommissioned0] = 0
  • You may receive an message stating it could not be ran, select Retry
  • Rename the SQL query to CM_DevicesMakeModel
  • Select Close & Apply 

New Page Configuration

  • Change the title
  • Change the page’s name

Validating the Data Relationships

As of today, you have to manually create the relationships for Direct Query files.  Select the Manage Relationships button under the Home ribbon menu.  And mirror the below table. 

Make Model Relationship

Visualizing the ConfigMgr Make Model Power BI Report

Therefore, to let your creative mind go, below will be personal design preferences.  You are welcome to modify the template files linked below. 

Adding Additional Filters

Moreover to keep great visualization real estate, utilize the built in filter blade to add additional column filters.  

Power BI Filter Blade

Conclusion

To sum up, we went through creating a very simple make and model report.  We have included the device’s manufacturer, model, serial number, BIOS, memory, processor, and OS disk size.  Resulting in us adding pie chart visualizations and a data table.  

Hope you enjoy and stay tuned for the next post in this series.  We will be reporting on boot up performance!

ConfigMgr Power BI Make Model Report Template

Leave a Reply

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