Lately I have been doing a lot of performance testing for ConfigMgr OSD. Basically I've deploying separate batches of machines, and I needed a quick way monitor certain milestones of their deployment, and filter out everything else. The various task sequence reports and monitors I found out there gave me a bit to much info, so I came up with a PowerShell script that you find further down this post.
Update: Added duration per machine as well as for the batch itself
Some prereqs
The script is tracking four milestones of a native ConfigMgr task sequence, including a custom "dummy action" named Report Finish Time that I added to the end. The milestones I track are the following:
- Partition Disk 0 – UEFI
- Apply Operating System
- Apply Windows Settings
- Report Finish Time

The Script
Simply run the script as a user that have at least read access to the ConfigMgr database. Modify the first part of the script to match your environment.
# Sample script to get milestones from batch deployments
# The script will limit the output to only deployments that started after the first client in the batch
#
# Note: Change line 8-18 to match your environment. For example "Report Finish Time" was just a dummy action I added to the end of my TS.
#
# Export File
$ExportPath = "E:\Setup\BatchDeploymentMilestones.csv"
# Database Info
$DatabaseServer= "CM01"
$Database = "CM_PS1"
# Batch Info
$TaskSequenceName = "Windows 10 Enterprise x64 v1903 - Native - 2Pint"
$FirstClientInBatch = "TMP-W10PEER-001"
$FirstActionToLookFor = "Partition Disk 0 - UEFI"
$LastActionToLookFor = "Report Finish Time" # Used for duration calucation later
$AllActionsToLookFor = "Partition Disk 0 - UEFI","Apply Operating System","Apply Windows Settings","Report Finish Time"
# SQL Query for Batch Start Time (borrowed from first client in batch)
$BatchStartTimeQuery = $("
SELECT
TOP 1 vTS.ExecutionTime
FROM
v_TaskExecutionStatus AS vTS
LEFT JOIN v_R_System VRS ON vTS.ResourceID = VRS.ResourceID
LEFT JOIN v_Advertisement vADV ON vTS.AdvertisementID = vADV.AdvertisementID
LEFT JOIN v_TaskSequencePackage vTSP ON vADV.Priority = vTSP.Priority
WHERE
vTSP.Name = '$TaskSequenceName'
AND ActionName = '$FirstActionToLookFor'
AND vRS.Name0 = '$FirstClientInBatch'
ORDER BY vTS.ExecutionTime DESC
")
# Run Batch Start Time SQL Query, will be used as filter later
$Datatable = New-Object System.Data.DataTable
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$DatabaseServer';database='$Database';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $BatchStartTimeQuery
$Reader = $Command.ExecuteReader()
$Datatable.Load($Reader)
$Connection.Close()
# Since Batch Start Time SQL Query only returns one entry, I'm cheating...:)
$BatchStartTime = $Datatable.Rows.ExecutionTime
# Reduce Batch Start Time with 5 minutes in case the first client started, wasn't the one that started the task sequence first.
$BatchStartTime = $BatchStartTime.AddMinutes(-5)
# SQl Query to get Batch Deployment Milestones
$BatchDeploymentQuery = $("
Select
vTS.ActionName,
vTS.ExecutionTime,
vRS.Name0
From
v_TaskExecutionStatus as vTS
LEFT JOIN v_R_System VRS ON vTS.ResourceID = VRS.ResourceID
LEFT JOIN v_Advertisement vADV on vTS.AdvertisementID = vADV.AdvertisementID
LEFT JOIN v_TaskSequencePackage vTSP on vADV.Priority = vTSP.Priority
WHERE
vTSP.Name = '$TaskSequenceName'
AND vTS.ExecutionTime >= '$BatchStartTime'
AND ActionName in ('$($AllActionsToLookFor -join "','")')
ORDER BY vRS.Name0
")
# Run Batch Deployment Milestone Query
$Datatable = New-Object System.Data.DataTable
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$DatabaseServer';database='$Database';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $BatchDeploymentQuery
$Reader = $Command.ExecuteReader()
$Datatable.Load($Reader)
$Connection.Close()
# Get Batch Deployment Info
[System.Collections.ArrayList]$BatchDeploymentMilestones = @()
Foreach ($Row in $Datatable){
$obj = [PSCustomObject]@{
# Add values to arraylist
ActionName = ($row.ActionName)
ExecutionTime = ($row.ExecutionTime)
ComputerName = ($row.Name0)
Duration = ""
}
# Add all the values
$BatchDeploymentMilestones.Add($obj)|Out-Null
}
# Calculate deployment duration per machine (only if machine reached last milestone)
# Get unique computernames from array
$Computers = $BatchDeploymentMilestones | Select-Object ComputerName | Get-Unique -AsString
foreach ($Computer in $Computers){
$FirstAction = $BatchDeploymentMilestones | Where {($_.ComputerName -eq $Computer.ComputerName) -And ($_.ActionName -eq $FirstActionToLookFor)}
$LastAction = $BatchDeploymentMilestones | Where {($_.ComputerName -eq $Computer.ComputerName) -And ($_.ActionName -eq $LastActionToLookFor)}
# Check for entries with both first and last action, and calculate the duration
If ($FirstAction -And $LastAction){
# Calculate duration
$Duration = New-TimeSpan –Start $FirstAction.ExecutionTime –End $LastAction.ExecutionTime
# Update array with duration value in hour, and minutes
$LastAction.Duration = "$($Duration.Hours) hours, $($Duration.Minutes) minutes"
}
}
# Export to CSV File
$BatchDeploymentMilestones | Export-Csv -Path $ExportPath -NoTypeInformation
# Display total batch runtime, from first time entry to last
$FirstEntry = $BatchDeploymentMilestones | Sort-Object ExecutionTime | Select -First 1
$LastEntry = $BatchDeploymentMilestones | Sort-Object ExecutionTime | Select -Last 1
$Duration = New-TimeSpan –Start $FirstEntry.ExecutionTime –End $LastEntry.ExecutionTime
Write-host "Total batch runtime is: $($Duration.Hours) hours, $($Duration.Minutes) minutes"

This is exactly what I am looking for – thanks for all the work that you give to us freely Johan.
Just want a bit of clarity regarding the "FirstClientInBatch" part. You are not looking at a collection, so how do you determine which machine is the first?
Hi Scott,
I'm looking for the first set of status messages to match.
Do you have an example of what the output of the report looks like? Also, do you have a repo where the scripts/files in the articles can be downloaded–or perhaps add a "copy to clipboard" button? Nice work!
The output from the script is just a single line with the total batch runtime in hours and minutes. I added a screenshot of a CSV example to the post.
You can find the build scripts I was using here: https://github.com/DeploymentResearch/DRFiles/tree/master/Scripts/BatchDeployments
/ Johan
Ciao Johan, nice job! I just test in my environment successfully but I have a bad surprise with child TS embedded in my root TS. Have any suggestion? Many thanks for this blog post
Hi Teffan,
If you are having issues with child sequences, make sure you're on 2006 or 2010 of ConfigMgr. You can also try removing/re-adding them to the task sequence pressing apply in between.