Tracking OS Deployment Milestones in ConfigMgr (PowerShell Edition)

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
Custom "dummy action" added to the end of my task sequence.

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"
Sample CSV out in Excel
About the author

Johan Arwidmark

0 0 votes
Article Rating
Subscribe
Notify of
guest
4 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
Scott
Scott
11 months ago

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!

Teffan Hawk
Teffan Hawk
11 months ago

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


>