The built-in Configuration Manager task sequence reporting view [v_TaskExecutionStatus] provides a great level of detail about each step and group executed, but I’ve always felt it was missing a collation of each execution so you could quickly see how many times it’s been executed per computer, how long it took to run each time, what step it ended on, etc.
The SQL code I wrote builds a common table to separate the starting status messages (only from step 0) from the ending messages (completed / failure / aborted). The data is partitioned using DENSE_RANK() by the starting messages found for each computer and task sequence to create an ExecutionID column. A select statement self-joins the start times to the end times on this ExecutionID to create a single row and calculates some of the particulars for each task sequence execution.
The data ends up looking like this:
The data from this query can be used to get the step details by querying the [v_TaskExecutionStatus] view for any output between the Start/EndTimes for the computer, package and step.
My main goal was to expose an execution status view to server owners that have been using some of my OnDemand Patching tools, but really this dataset enhances all of our reporting on task sequences…