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
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.
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
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.
- 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.
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!
