Monitor OS Deployments with PowerShell

If you have access to your ConfigMgr database, you have access to a wealth of information, including the detailed steps of your OS deployments.

Below is a PowerShell script that will query the ConfigMgr database and return the detailed steps of your deployments.  You can specify a particular machine that is being or has been deployed, the length of time in which to return data, and whether to output the data into PowerShell’s Gridview, CSV, or both.  You can also run this for any task sequence, not just OS deployments.

For currently running deployments, the script will get you a ‘snapshot’ of the current deployment progress.  To update it, simply run the script again.  In a coming blog post, I will show you how to monitor deployments in near real-time using the ConfigMgr database.

The TS steps are returned with the most recently completed step at the top, so you can easily see where the deployment is at present.  The ActionOutput column contains a snippet of the deployment logs for that step, giving more detailed information about what was carried out. To look for any TS steps that failed, simply filter the ExitCode column for any value other than 0.

To run the script, enter the following parameters:

• $TS – The name of your OS Deployment (or other) task sequence
• $ComputerName – Enter the computername for a computer already known to ConfigMgr (eg for rebuilds), enter ‘Unknown’ for bare-metal deployments, or leave blank to get all deployments
• $SearchTimeInHours – The length of time in hours in which to return data, eg ’24’ gets all data up to 24 hours ago
• $CSV – Enter Yes to return the data into a csv file
• $Grid – Enter Yes to return the data into PowerShell’s Gridview
• $datasource – The name of the SQL Server and Instance hosting your ConfigMgr database
• $database – Your ConfigMgr database name

You must have at least ‘db_datareader’ access to the ConfigMgr database to run the script.

<#

This script will get the detailed steps carried out by a Task Sequence from the ConfigMgr database

#>
$TS = "Windows OS Deployment x64" # Enter the name of the Task Sequence you wish to monitor
$ComputerName = "unknown" # Leave blank to query for all deployments, enter 'Unknown' for a bare-metal deployment, or enter the computer name for a known device
$SearchTimeInHours = 24 # Enter the length of time in hours to return past data, eg '24' means all data until 24 hours ago
$CSV = "No" # Yes or No
$Grid = "Yes" # Yes or No

# Database info
$dataSource = “mysqlserver\INST_SCCM”
$database = “CM_ABC”

# Open a connection
cls
Write-host "Opening a connection to '$database' on '$dataSource'"
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Run SQL query
Write-host "Running query..."

If ($ComputerName)
{
$query = "Select Name0 as 'Computer Name' `
,Name as 'Task Sequence'`
,ExecutionTime `
,Step `
,ActionName `
,GroupName `
,tes.LastStatusMsgName `
,ExitCode `
,ActionOutput `

from vSMS_TaskSequenceExecutionStatus tes `
inner join v_R_System sys on tes.ResourceID = sys.ResourceID `
inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID `
where (tsp.Name = '$TS' `
and sys.Name0 = '$ComputerName') `
and DATEDIFF(hour,ExecutionTime,GETDATE()) < '$SearchTimeInHours' `
ORDER BY Step Desc"
}
Else
{
$query = "Select Name0 as 'Computer Name'`
,Name as 'Task Sequence'`
,ExecutionTime `
,Step `
,ActionName `
,GroupName `
,tes.LastStatusMsgName `
,ExitCode `
,ActionOutput `

from vSMS_TaskSequenceExecutionStatus tes `
inner join v_R_System sys on tes.ResourceID = sys.ResourceID `
inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID `
where tsp.Name = '$TS' `
and DATEDIFF(hour,ExecutionTime,GETDATE()) < '$SearchTimeInHours' `
ORDER BY Step Desc"
}

$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

# Output data
$table = new-object “System.Data.DataTable”
$table.Load($result)

if ($CSV -eq "Yes")
{
$Date = Get-Date -Format HH-mm--dd-MMM-yy
$Path = "C:\Script_Files\$TS-$Date.csv"
$table | Export-Csv -Path $Path
Invoke-Item -Path $Path
}
If ($Grid -eq "Yes")
{
$table | Out-GridView -Title "$TS on $ComputerName"
}

# Close the connection
$connection.Close()