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.

Reduce OSD Time

, , ,

Reduce OSD Time

1. Set High Performance Power Plan

  • This has proven in multiple environments that I have been to save 20% – 50% on certain steps, such as download/apply the OS. Also note that by default configmgr while in WinPE will run power setting set to balanced, so making sure we use all resources we can see a major win from this piece. I also set this to prvent systems from going to sleep during the TS.

2. Set SMS Host Agent start-up options

  • By default the SMS Host Agent (CcmExec) service is set to a delayed start. So every time you restart the system during the OSD Process and you see the “initializing System Center Configuration client” you are just costing time. In my environment this message would usually last 2 – 5 minutes each restart. Once I implemented this we noticed the configmgr client would only take < 15 seconds to initialize, that is a savings of 800% – 2000% for this specific task sequence action.
  • Frank Rojas whom is one Microsofts CSS Lead OSD Engineer has let me know that this has POTENTIAL for failures in a TS. So please know that if you use this step please understand it is not supported by Microsoft. I have not personally seen any problems with this, but I do understand there is possible for OSD failure as a result.

3. Set SMSTSRebootDelay

  • This will change the default behavior from 30 seconds to 0 seconds and occur immediately
4. Set manual Restart times (for displayed messages)
  • By default when you add a restart computer step into the TS the time is set to 60 seconds. Simply by un-checking the “Notify the User before restarting” box you now force the restart immediately. Should you need to actually display a message you can just reduce the amount of time this box is displayed.
5. Eliminate unnecessary restarts / steps
  • I have seen several customer environments that had 8 or more restart steps in their TS. Whenever you have a restart in a TS you can assume usually 2 – 5 minutes are added to the total TS time. I would recommend investigating the status messages for exit codes indicating a restart is necessary before you start modifying a customers task sequence. Many of my peers usually have only 2 or 3 restarts in their TS as they have streamlined their deployments.
  • MVP Mick Pletcher has a great post on how to handle restarts in your TS. Basically add a condition statement to check if the system needs a restart you can read about it and implement it here
6. Patch the WIM
  • This will of course help you out on the install software update steps in your TS. The more patches that are in your WIM, the less to process later. I tend to bake office and patches into the WIM. I’m not a fan of having really thick images loaded with several 3rd party apps that can be installed during ts.
7. Verify boundaries are efficient
  • This should be another no-brainier idea, but you will be surprised. I’ve seen customer environments where a machine from the east cost USA would pull down images from the middle east….like there is literally an entire ocean btwn the DP and the workstation..
8. Prevent OOBE From connecting to Windows Update
  • I’ve seen in the SMSTS.LOG before that a system I was imaging was trying to reach out to windows update. Some places you are confined to image from certain vlans only and you can block this communication at the firewall. In my TS I modify the Registry to prevent this action from occurring. Unfortunately this was something changed in my TS a long time ago so I did not grab a screenshot. I can edit this post when I get around to making the next revision of changes in the updated WIM. (Thanks Todd)

On my test experiment the OSD process would take around 100 – 110 minutes…that’s nearly two hours, and wayyyy too long.

Reduce OSD time


I like to use Thomas Larsens OSD Dashboard when I keep track of some Task sequence information like this. Please check out his blog here. I believe this dashboard is a must have for every SCCM admin.

Screenshot after changes OSD Time. We see below that this has been reduced to 36 minutes. I am positive I can actually get this to 32 minutes or less, but for now I will leave good enough alone. This test shows that we have cut the OSD time to less than 1/3 of the time. I’ve been in other environments where they had the image process take 24+ hours, but that is mainly boundary related issues.




Power settings: I kind of have this littered everywhere in my TS before major time consuming steps like apply OS, install patches, install apps, etc.

Name: Set High Performance Power Scheme
Command Line: PowerCfg.exe /s 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c
SMS Host Agent Start-Up Properties: This only needs to go in 1 place right after you install the SCCM Client. 

NameSet SMS Host agent to start immediately
Command Linecmd /c sc config “CcmExec” start= auto

Dynamic Variables: I have all my variables set here. These are for resiliency, and speed.


Prevent OOBE from Downloading Drivers:

NameMount the Offline HKLM File
Command Linereg load HKLMOffline C:WindowsSystem32ConfigSoftware

NameSet DODownloadMode
Command LineREG ADD HKLMOfflineSOFTWAREPoliciesMicrosoftWindowsDeliveryOptimization /v DODownloadMode /t REG_DWORD /d 100

NameSet DoNotConnectToWindowsUpdate
Command LineREG ADD HKLMOfflineSOFTWAREPoliciesMicrosoftWindowsWindowsUpdate /v DoNotConnectToWindowsUpdateInternetLocations /t REG_DWORD /d 1

NameUnmount the Offline HKLM File
Command Linereg unload HKLMOffline

End of TS to change back to default (if wanted)


NameSet SMS Host agent to start delayed
Command Linecmd /c sc config “CcmExec” start= delayed-auto

Name: Set Balanced Power Scheme
Command LinePowerCfg.exe /s 381b4222-f694-41f0-9685-ff5bb260df2e


This customer has unnecessary restarts in their OSD design, and were not very streamlined. Things in this version of the TS can be easily baked into the WIM on the next image refresh with the build/capture TS i provided for the customer. There are things that are slow during this TS like install Visual J+, or .Net Framework 3.5 that would reduce the OSD time if baked into image. 

Other things to reduce OSD Time: 
I still have unnecessary install software update/restart steps in the TS as a sort of catch all. These systems come out fully patched, but are left enable in case I do not get time to capture and test a new wim one month.

I have seen in the SMSTS.LOG where systems would try to reach out to windows updates for patches instead of staying within my environment. This can be changed via registry modification…I have this in place in my environment but did not capture screenshots when I saw it in the log files. 

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.