ConfigMgr Client Boot Time Power BI Report

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)

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

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

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.

For this tutorial we will be utilizing the Direct Query template. You can always convert Direct Query to Import.

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.

With limited data available, I can ensure these visualizations will work for you evironment.

[ngg src=”galleries” ids=”14″ display=”basic_slideshow” gallery_width=”800″ gallery_height=”600″ autoplay=”0″ arrows=”1″ transition_style=”slide” show_thumbnail_link=”0″]

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.

Customization done to the See Records visualization / table do not save. This is a limitation in Power BI. Please feel free to vote on some of them at ideas.powerbi.com

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.

Tags: ConfigMgr Reporting Getting Started MEM MEMCM SCCM SCCM Reporting

ConfigMgr Client Boot Time Power BI Report