SQL Query to track OSD


SQL Query to track OSD

Typically whenever I enter a new environment I get a wave of questions focused on infrastructure, patching, software, and OSD. More specifically the OSD questions, are how to track the TS, how to make the TS better, and how to troubleshot the TS in the event of a failure. This blog-post I will let you know what I use from the community & as well as the SQL Logic that I prefer to use to track the OSD process. I have already written a piece that helps you “Reduce OSD Times“, but you should also check out the blog my buddy Gary runs at GaryTown as I have been incorporating more, and more of his logic into my TS.  …Shoot I even stopped the way I do my wallpaper upgrade screens/lockouts b/c his way is more efficient lol.


Most of my customers typically have high turnover on the engineering staff so nobody onsite really knows much in depth about SCCM, or it’s some guy who just inherited the infrastructure and needs a hand to make things run smooth again. These are the community tools that I like to use to help me educate the customer specific on tracking the OSD Task sequence.


Status Message Queries by Nickolaj Andersen  – Especially helpful if you want to filter by all “Errors” this has helped me quickly identify serious problems in OSD Task Sequences. This is something I implement on DAY 1 at customers sites

OSD Dashboard by Thomas Larsen – Super helpful when trying to calculate how long customers OSD process takes, and where we can create improvement. I try to get my Tier 1 support to run this report to help them better understand the OSD Process, and to aid in their troubleshooting. This has some awesome visuals b/c management always loves visuals….and this is something else that I implement on DAY 1 at customers sites.

Task Sequence Monitor by Trevor Jones – This is just an awesome tool that tracks status messages from your specified OSD Task sequence…the best part is it is free.


I think all of the tools I listed above are great and that I stand behind them. In my day to day when I am designing a TS, or tracking 1 specific computer I typically prefer to just have SQL open and track directly that way….for me it’s just quicker, and I’m way more comfortable with SQL today than when I was 3 years ago. At any site I go you can always find me with my head buried in the ConfigMgr console, and SQL….usually saying whyyyyyy me lol jk. I like to do take the query below and modify for your specific needs so obviously. I typically need to look at specific systems, groups, steps, in the TS so I keep those ready to go in my where statements….just commented out.


When you copy and use this SQL Query make sure you are connected to your ConfigMgr DB, and specify your TS name, and the name of the specific machine you are looking up.



       — vTSP.Name,

       Convert(VarChar(21), vTS.ExecutionTime, 100) AS [Execution Time],

       — vTS.ExecutionTime,









       v_TaskExecutionStatus 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


       vTSP.Name = ‘CBuck_SCCMF12TWICE_NiceAF_TSv2’ — Task Sequence Name

       AND DATEDIFF(dd,vTS.ExecutionTime,GetDate()) <3 — how far back to get data

       –AND vTS.GroupName = ”

       –AND ActionName in (”,”)

       –AND LastStatusMsgName NOT LIKE ‘%Ignored%’

       –ANd LastStatusMsgName Like ‘%Failed%’

       –AND LastStatusMsgName!= ‘NULL’

       AND VRS.Name0 = ”

       –AND vTS.ExitCode = ”




In a future blog-post I will finally release my “14 pg OSD Troubleshooting Guide” that was created with Khalid Al alul , and Ricky RIchard who are 2 other well-known SCCM Guys in the Pub-Sec space….

NOTE: we can make this sql fancier, but for purposes of this blog-post….let’s not.

Potential USMT Errors & Resolution

, , , , , , , , ,
Potential USMT Errors

Failure when “trying to reboot into WinPE”
appears on the Task Sequence UI (forgot to take screenshot)

 Potential USMT Errors & Resolution


      Check the “SMSTS.LOG” “C:WindowsCCMLogs”


         In the screenshot below look for a line like “Unable to find a volume that is suitable for staging the boot image” in the log file 


Potential USMT Errors
       This is typically for 2 different reasons.
      1.  The drive is locked by bitlocker encryption and you need to first disable bitlocker in the task            sequence. once this is done then the task sequence engine can identify the drive to stage the        boot image locally
      2.   This can also be because the drive is still undergoing the encryption process. You must wait             until the drive is fully encrypted before you can execute this task sequence from windows.
       To verify the drive is finished encrypting launch powershell as an admin and type “Get-BitlockerVolume” and do not attempt to run the installation until the “Encryption KeyProtector Percentage” is are 100%  
Potential USMT Errors
NOTE: it is possible you receive this error when you are trying to stage the boot image onto the disk and the disk cannot be read b/c of a different encryption software locks the disk. For example if you are running Dell Credant you must be logged into the system then the TS Engine will be able to read the disk.
To identify Dell Credant systems via SQL…this is a hybrid of one my queries that I use in my production environment but you can modify it to abosrb only bitlocker information by commenting out not needed parts

SELECT Distinct

v_R_System.Name0 AS System,

Computer_System_DATA.Model00 AS [System Model],


V_R_System.AD_Site_Name0 AS [AD Site],

CASE V_R_System.Build01

When ‘6.1.7601’ THEN ‘Windows 7’

WHEN ‘10.0.14393’ THEN ‘Win 10 v1607’

WHEN ‘10.0.15063’ THEN ‘Win 10 v1703’

END AS [Operating System],

CASE V_R_System.Client0

When ‘0’ THEN ‘No Client’

WHEN ‘1’ THEN ‘Client Installed’

END AS [Client],

v_GS_ENCRYPTABLE_VOLUME.DriveLetter0 AS [Drive Letter],

–v_GS_ENCRYPTABLE_VOLUME.ProtectionStatus0 AS [Protection Status],


WHEN ‘0’ THEN ‘not encrypted’

WHEN ‘1’ THEN ‘encrypted’

WHEN ‘2’ THEN ‘Encrypted Requires Pin’

END AS [Bitlocker Status]



INNER JOIN v_R_System ON v_GS_ENCRYPTABLE_VOLUME.ResourceID = v_R_System.ResourceID

INNER JOIN Computer_System_Data ON V_R_System.Name0 = Computer_System_Data.Name00




AND Computer_System_DATA.Model00!= ‘VMware Virtual Platform’

AND Computer_System_DATA.Model00!= ‘Virtual Machine’

–AND v_GS_ENCRYPTABLE_VOLUME.ProtectionStatus0 = ‘0’ –for not bitlocker encrypted systems–

–AND V_R_System.Name0 = ‘P620268’

AND ___System_INSTALLED_SOFTWARE0.ARPDisplayName00 like N’Credant_WindowsShield%’


Potential USMT Errors


Failure when trying to connect to SMP Share
This failure is more often seen when trying to rerun on a failed system (can be seen during backup or restore part of the process) 
Potential USMT Errors
For this we corrected the issue is to open PowerShell and running the following.


Remove-Item -Path ‘HKLM:SOFTWAREMicrosoftSystemCertificatesSMSCertificates*’ -force; restart-service ccmexec

I would also recommend opening registry location to verify this has successfully been deleted.
Once this runs successfully you should then be able to re-run the task sequence successfully.
File Not Found: 
of course there is the standard make sure your commands are typed correctly. In the example below we see a file not found error. Make sure you type out your file names correctly etc.
Potential USMT Errors
Potential USMT Errors
NOTE: I have seen cases where a variable is set for Packages, but it does not always translate, so I tend to just hard code the package ID when I set restore/capture options into a variable see the example below
Potential USMT Errors
Connection to SMP Refused: 
Make sure when you being your deployment strategy that you plan for an adequate number of connections to the SMP. The default for this I believe is 100 connections, but that does not mean concurrent connections. The criteria that goes into the count is any established connection (completed or in progress) within the your deletion policy time period. If you have a problem where the connection is actively refused by the SMP you should increase the max allowed connections you have configured.
Potential USMT Errors
I tried to create as many Potential USMT Errors as I could think of to help out the community. If I  encounter anymore or can think of new ones I will add them to this blogpost.