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.
Select
— vTSP.Name,
Convert(VarChar(21), vTS.ExecutionTime, 100) AS [Execution Time],
— vTS.ExecutionTime,
VRS.Name0,
vTS.Step,
vTS.GroupName,
vTS.ActionName,
vTS.LastStatusMessageIDName,
vTS.ExitCode,
Vts.ActionOutput
From
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
WHERE
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 = ”
ORDER BY vTS.Step
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.
Add comment