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.

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.

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.

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:

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 🙂