An Easy Way to Monitor Your ConfigMgr OS Deployments

There are several ways to monitor your OS deployments, since the monitoring service feature was introduced in MDT 2012 and above.  But if you are using ConfigMgr, or MDT-Integrated ConfigMgr, then it’s very easy to monitor your OSDs using a connection to the ConfigMgr database.  I made a post about this before, using a Powershell script to get a ‘snapshot’ of OSD status from the database.  In this post, I will use Microsoft Excel which allows me to set a refresh period on the SQL query, meaning I can get almost real-time information about my deployments as they happen, in step-by-step detail, even if my deployments are running on another geographical site.  This can be especially useful if a deployment fails (in a controlled way) as the error information is usually available in the database, and therefore visible in my Excel document.

Capture4

Create the Excel Document

First, we need to create a new Excel workbook and create a data connection to the ConfigMgr SQL database.  This is described in more detail in my post: Creating dynamic reports for configuration manager with microsoft excel

SQL Query

When you create the data connection, paste the following SQL 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 in ('Windows OS Deployment x64', 'Windows OS Deployment x86')
and DATEDIFF(hour,ExecutionTime,GETDATE()) < 8
ORDER BY Step Desc

In line 14 of the query, I have added the names of my OSD task sequences, you will need to add your own.  You could, of course, create more than one data connection in the same Excel workbook, using a new worksheet for each OSD task sequence.

In line 15, I am returning data from the database from the last 8 hours.  You can change this to your preferred setting.

Set the Refresh Period

When creating the data connection, you have the option to refresh the data periodically.  Set this to refresh every minute.  This will simply run the SQL query every minute, keeping the workbook up-to-date.

Capture

Using the OSD Monitoring Workbook

Filtering

The workbook will display the computer name, task sequence name, step number and execution time, the name of the action and the group it belongs to, the last status message for that action, the exit code, and the action output, which is basically a snippet from the smsts.log log file for that action.

You can use Excel’s filters to filter the information you want to see.  For example, if you want to monitor OSD for a specific machine, simply filter for the machine name in the ‘Computer Name‘ column.

Capture2

Note that the computer name will only appear if the machine is already known to ConfigMgr, ie it is being rebuilt.  If it is bare metal, it will display ‘Unknown’ in the computer name.

You can also use the ‘ExecutionTime‘ column to filter for OSDs during a specific time period.  For example, here I filter for all OSD actions after 10:00:

Capture3

Finding errors

Because the exit code is reported for each step, you can simply filter that column to discover any steps that failed by selecting the non-zero exit codes.  Then you can check the ‘ActionOutput‘ column for a snippet from the smsts.log to find more about why it failed.

For example, one of my OSDs failed to apply the OS image:

PC003 Windows OS Deployment x64 16/01/2015 08:28 89 Apply x64 Operating System Image (Partition 3) OS Image x64 The task sequence execution engine failed executing an action -2147024751

I’ve seen that before, but let’s check the ActionOutput column for more details:


... ,721)
ApplyImage(), HRESULT=80070091 (e:\nts_sccm_release\sms\client\osdeployment\applyos\installimage.cpp,1830)
Apply(), HRESULT=80070091 (e:\nts_sccm_release\sms\client\osdeployment\applyos\installimage.cpp,2019)
installer.install(), HRESULT=80070091 (e:\nts_sccm_release\sms\client\osdeployment\applyos\installimage.cpp,2094)
Closing image file \\sccmsrv-01.testlab.com\SMSPKGC$\ABC00116\W7-X64-001.wim
ReleaseSource() for \\sccmsrv-01-testlab\SMSPKGC$\ABC00116\.
reference count 1 for the source \\sccmsrv-01.diasemi.com\SMSPKGC$\ABC00116\ before releasing
Released the resolved source \\sccmsrv-01.diasemi.com\SMSPKGC$\ABC00116InstallImage( g_InstallPackageID, g_ImageIndex, targetVolume, ImageType_OS, g_ConfigPackageID, g_ConfigFileName, bOEMMedia, g_RunFromNet ), HRESULT=80070091 (e:\nts_sccm_release\sms\client\osdeployment\applyos\applyos.cpp,509)
Installation of image 1 in package ABC00116 failed to complete..
The directory is not empty. (Error: 80070091; Source: Windows)

As I suspected, it could not wipe the partition of all the files, so I need to do it manually with diskpart.

A handy solution for easy monitoring and troubleshooting of your ConfigMgr OSDs 🙂

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.

grid1

csv

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