In this installment of the Getting Started with ConfigMgr and Power BI; we create a simple client boot time report. The ConfigMgr Client Boot Time Power BI Report gives the ability to drill down unique boot time configuration ranging from group policy to event viewer startup times.
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
ConfigMgr Make Model Power BI Report
(this post)
The below sections outline creating the ConfigMgr Client Boot Time Power BI Report. With attention to the SQL query used for pulling device’s boot configuration.
Getting Setup
For the step by step portion we will utilize the direct query template we created in the third series.
ConfigMgr Make Model Power BI Report
Get to the Queries Section
- Fill in the parameters and select Load
- Select Edit Queries
Setting up the Template
The subsections require getting data from SQL query source, follow the below steps.
- Duplicate the Hardware page and rename it to Boot Times
- Clear the visuals and page filters
- Change the title to Client Boot Times
- Right click on your ConfigMgr folder and select New Query > SQL Server
- Change the server and database values to your ConfigMgr parameters
ConfigMgr Client Boot Time Power BI Report
The below query pulls in the device’s available ConfigMgr inventoried boot times. It includes group policy, update duration, and event viewer processing times.
- Create a new SQL Server query and paste the following SQL statement
SELECT boot.[ResourceID], boot.[TimeStamp], CAST((boot.[MedianBootDuration0] / 1000.0) as int) as [Total Boot (s)], CAST(((boot.[MedianBootDuration0] / 1000.0) / 60) as int) as [Total Boot (m)], CAST((boot.[MedianCoreBootDuration0] / 1000.0) as int) as [Core Boot (s)], CAST(((boot.[MedianCoreBootDuration0] / 1000.0) / 60) as int) as [Core Boot (m)], CAST((boot.[MedianBiosDuration0] / 1000.0) as int) as [BIOS Boot (s)], CAST(((boot.[MedianBiosDuration0] / 1000.0)/60) as int) as [BIOS Boot (m)], CAST((boot.[MedianEventLogStart0] / 1000.0) as int) as [Event Log (s)], CAST(((boot.[MedianEventLogStart0] / 1000.0) / 60) as int) as [Event Log (m)], CAST((boot.[MedianGPDuration0] / 1000.0) as int) as [Group Policy (s)], CAST(((boot.[MedianGPDuration0] / 1000.0) / 60) as int) as [Group Policy (m)], CAST((boot.[MedianUpdateDuration0] / 1000.0) as int) as [Update (s)], CAST(((boot.[MedianUpdateDuration0] / 1000.0) / 60) as int) as [Update (m)], CAST((boot.[LatestBootDuration0] / 1000.0) as int) as [Latest Total Boot (s)], CAST(((boot.[LatestBootDuration0] / 1000.0) / 60) as int) as [Latest Total Boot (m)], CAST((boot.[LatestCoreBootDuration0] / 1000.0) as int) as [Latest Core Boot (s)], CAST(((boot.[LatestCoreBootDuration0] / 1000.0) / 60) as int) as [Latest Core Boot (m)], CAST((boot.[LatestBiosDuration0] / 1000.0) as int) as [Latest BIOS Boot (s)], CAST(((boot.[LatestBiosDuration0] / 1000.0) /60) as int) as [Latest BIOS Boot (m)], CAST((boot.[LatestEventLogStart0] / 1000.0) as int) as [Latest Event Log (s)], CAST(((boot.[LatestEventLogStart0] / 1000.0) / 60) as int) as [Latest Event Log (m)], CAST((boot.[LatestGPDuration0] / 1000.0) as int) as [Latest Group Policy (s)], CAST(((boot.[LatestGPDuration0] / 1000.0) / 60) as int) as [Latest Group Policy (m)], CAST((boot.[LatestUpdateDuration0] / 1000.0) as int) as [Latest Update (s)], CAST(((boot.[LatestUpdateDuration0] / 1000.0) / 60) as int) as [Latest Update (m)] FROM v_GS_SYSTEMBOOTSUMMARY as boot INNER JOIN v_R_System as s ON s.[ResourceID] = boot.[ResourceID] AND 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_DevicesBootTimes
- 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.
Viewing Details of Pie Chart Visuals
With the pie chart visualizations, you are able to right click and select See Records to view information. You are able to customize this data for export. It is done simply by removing unnecessary fields and dragging news ones.
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 boot time report. We have included the device’s core, group policy, update duration, and total boot time statistics. You have boot the average and latest available boot times. I created some initial visualizations, but with a limited number of clients in my lab environment, I cannot deliver on proven diagrams and charts.
Hope you enjoyed and stay tuned for the next post in this series.