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”
[su_note note_color=”#90bcc9″ text_color=”#ffffff”]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. [/su_note]
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.
For the step by step portion we will utilize the direct query template we created in the second series.
[su_note note_color=”#90bcc9″ text_color=”#ffffff”]We start with direct query template because we can always convert to Import. We cannot convert Import to Direct Query. [/su_note]
Get to the Queries Section
- Fill in the parameters and select Load
- Select 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
- Change the server and database values to your ConfigMgr 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.
[su_note note_color=”#90bcc9″ text_color=”#ffffff”]For this tutorial we will be utilizing the Direct Query template. You can always convert Direct Query to Import. [/su_note]
[su_note note_color=”#90bcc9″ text_color=”#ffffff”]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.[/su_note]
- 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.
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.
[ngg src=”galleries” ids=”4″ display=”basic_slideshow” arrows=”1″ show_thumbnail_link=”0″]
Adding Additional Filters
Moreover to keep great visualization real estate, utilize the built in filter blade to add additional column filters.
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!