Recently PowerBI had its birthday and I put a tweet out there on twitter asking what type of dashboard people would like to see in celebration. Well the results came in pretty quick and it was almost a tie between OSD and an update to my existing SoftwareUpdates PowerBI dashboard.
As a result I decided to do a new dashboard for PowerBI and OSD and to later on update the Software Updates dashboard inline with some of the new features that have come out for PowerBI since the last update.
So without further adue, I would like to introduce everyone to version 1.0 of the SCConfigMgr OSD Dashboard.
This dashboard is available for download from TechNet and all incremental/WIP updates will be stored in the SCConfigMgr GitHub Repository.
This template is a little different than templates I’ve released in the past because of some of the new features that have been released with the new PowerBI. Instead of updating the data source settings they are now handled as parameters within the report. When you first open the template it will prompt you to provide it with a ServerName and the Database Name using the FQDN.
Once you hit load, depending on settings and permissions you may be prompted for credentials or warned regarding encrypted connections getting past these is relatively simple and has been covered in my other PowerBI reports.
Now lets get into what exactly the report is doing the first big query you’ll encounter is the gather every step executed and its exit code/time component. this is done for a couple of reasons first to establish the number of times a specific error code on a specific step occurs and to allow you to organize the steps in a particular order. At a later date I intend to do some DAX work that will show the duration of time spent on each OSD step I also convert error codes to known error codes here rather than doing a bunch of handling in the PowerBI template with hundreds of applied steps to navigate. This data is stored in the ‘TaskExecutionData’ table in PowerBI and you can review the SQL used to gather this information below.
--Gather every step executed and its exit code/time-- Select Distinct v_R_System.Name0 , max(v_TaskExecutionStatus.Step) as 'Step' , v_TaskExecutionStatus.ActionName , v_TaskExecutionStatus.ExitCode , CASE v_TaskExecutionStatus.ExitCode WHEN 0 THEN 'Success' WHEN 128 THEN 'Non-Standard Error' WHEN -2147467259 THEN 'Install Application Error' WHEN 41 THEN 'Hardware Driver Error' WHEN 16389 THEN 'Request Content Failure' WHEN -1073741819 THEN 'Access is Denied' WHEN -2016409851 THEN 'Redundant Write' WHEN -2016410032 THEN 'Device Object Parameter Not Valid' WHEN -2145123271 THEN 'Non-Standard Error' WHEN 1 THEN 'Incorrect Function' WHEN 10 THEN 'Incorrect Environment' WHEN 115 THEN 'Non-Standard Error' WHEN 13 THEN 'The Data is Invalid' WHEN 14 THEN 'Not Enough Memory' WHEN 191 THEN 'Non-Standad Error' WHEN 2 THEN 'The System Cannot Find the File Specified' WHEN 50 THEN 'The Request is not Supported' WHEN 7 THEN 'The Storage Control Blocks Were Destroyed' WHEN 9 THEN 'The storage control block address is invalid' Else 'Undocumented Error' End AS 'ENG Error Codes' , v_TaskSequencePackage.Name , V_TaskExecutionStatus.ExecutionTime from v_TaskExecutionStatus left outer join v_Advertisement on v_TaskExecutionStatus.AdvertisementID = v_Advertisement.AdvertisementID Left outer Join v_R_System on v_TaskExecutionStatus.ResourceID = v_R_System.ResourceID left outer join v_TaskSequencePackage on v_Advertisement.PackageID = v_TaskSequencePackage.PackageID where v_TaskSequencePackage.BootImageID is not NULL and v_TaskExecutionStatus.ActionName != '' Group By v_TaskExecutionStatus.ActionName,v_R_System.Name0,v_TaskSequencePackage.Name,v_TaskExecutionStatus.ExecutionTime,v_TaskExecutionStatus.ExitCode Order By Name0,ExecutionTime
The second important query which depending on the size of your environment may take some time to run again – I did some of the math work in SQL – assuming that your SQL server can handle the math better than some desktops can this can be changed by leveraging DAX expressions if so desired. This query attempts to get a machines name and the start/end time for the device. I may later change this to ResourceID depending on feedback to help minimalize the possibility of generating ‘bad math’ where a machine with the same name stacks its time by being re-imaged. However in my testing so far this hasn’t seemed to be a large issue. This Query simply gets the max and minimum start time for the TS for the machine and calculates the difference.
Select Distinct v_R_System.Name0 , MAX(v_TaskExecutionStatus.ExecutionTime) as 'END TIME', MIN(V_TaskExecutionStatus.ExecutionTime) as 'Start Time', DATEDIFF(MINUTE, MIN(V_TaskExecutionSTatus.ExecutionTime) , MAX(V_TaskExecutionSTatus.ExecutionTime)) as 'The Difference', V_Package.Name from v_TaskExecutionStatus left outer join v_R_System on v_TaskExecutionStatus.ResourceID = v_R_System.ResourceID left Join v_AdvertisementInfo on v_AdvertisementInfo.AdvertisementID = v_TaskExecutionStatus.AdvertisementID Left join v_Package on v_Package.PackageID = v_AdvertisementInfo.PackageID left outer join v_Advertisement on v_TaskExecutionStatus.AdvertisementID = v_Advertisement.AdvertisementID left outer join v_TaskSequencePackage on v_Advertisement.PackageID = v_TaskSequencePackage.PackageID where v_TaskSequencePackage.BootImageID is not NULL Group By v_R_System.Name0,v_Package.Name order by V_r_system.Name0
The last important table that in the ‘PowerBI’ template you might want to look at is the ‘GoalCriteria’ table. This is a manually built table where I arbitrarily chose 60 minutes as being the goal for image duration. This can be changed by selecting edit queries, select the table, click the gear to the right of ‘source’ for the table edit the value and select OK.
Jordan has been working in the Industry since 2009. Since starting he’s worked with Active Directory, Group Policy, SCCM, SCOM and PowerShell. Jordan currently works in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.