Version 1.2 Notes:

Tthis version of the OSD has corrected several small bugs that I won't mention but one rather large bug around the structure of time. Originally I was using Name0 as the key for calculating time for the duration of TS. I have now updated that to use the ResourceID attribute instead. This greatly improves the accuracy of the average run-time. If you want to change the Database you can update the parameter and then refresh the DB. 

To celebrate PowerBIs Birthday I wanted to create a new PowerBI dashboard that would be useful for the community. Based on the feedback I got, OSD was the direction I decided to head in and created the first iteration of the available PowerBI Template. This template is still the first version and will be improved/optimized and added to as time goes on. 


The below query gathers information about every step that was executed. Careful when executing in large environments with complex task sequences as this could take a long time to assemble the data. I recommend testing the SQL query run before implementing the PowerBI template.


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


Below gathers the duration information for how long the a task sequence that is related to OSD has been running.


Select Distinct 
    v_R_System.Name0 , 
    MAX(v_TaskExecutionStatus.ExecutionTimeas 'END TIME', 
    MIN(V_TaskExecutionStatus.ExecutionTimeas 'Start Time', 
    DATEDIFF(MINUTEMIN(V_TaskExecutionSTatus.ExecutionTime) , MAX(V_TaskExecutionSTatus.ExecutionTime)) as 'The Difference', 
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
Again I strongly urge you to TEST the queries before usage in the PowerBI template in a controlled scenario using SQL Management Studio.