How to validate if a KB is installed?

Question

Hi all, I have some questions around Windows Update, pls, can you guys help me on that?

1 - Is there a way to see, using SQL Query or something similar, what was the last time a computer was updated and what was the last KB installed?

2 - I am using SCCM CB together Intune, Co Management, for Windows Update. My HW Inventory still inventorying Update status from computers for SCCM Database or this will stop and only Intune take care of this information know?

3 - If not possible anymore in SCCM to see if an specific KB is installed in a computer, where in Intune can I see that? (remembering, I am in a co-management scenario).

Thank you,

Answer 1

teasing out "exactly when a KB was installed", isn't that easy (or reliable, IMO); so instead I'm going to come at a possible answer sideways.

Microsoft publishes the UBR of Cumulative Updates (example, https://support.microsoft.com/en-us/topic/windows-10-update-history-8127c2c6-6edf-4fdf-8b9f-0f7be1ef3562) . ConfigMgr, in heartbeat discovery (I don't know about intune connected clients), reports that information.

Knowing that you can "look it up manually", here's one way to see what your Windows 10/11 boxes are reporting. What you end up with are lines like...

ComputerName1234 16777224 Windows 10 21H2 2364 10.0.19044 2364

Since you can manually/visually see that the MaxUBR (as known to your own database) for the Windows 10 21H2 is 2364, and the last column is the UBR as reported by ComputerName1234, you know that particular box is patched to the latest known CU. You 'could' make internal case when statements with every UBR as reported by your own computers, to say things like ... 'when UBR 2364 and 10.0.19044, then February 2023... ' etc. etc. That feels like too much work to me, but whatever works for you.

I'm curious if when you check this in your environment, for the intune/co-managed clients, do they report Build01 and Buildext? that would be useful to know for others (and me).

;with cte as (Select Distinct s1.build01
	,case 
	 when s1.Build01 = '10.0.17763' then 'Windows 10 1809'
	 when s1.Build01 = '10.0.18362' then 'Windows 10 1903'
	 when s1.Build01 = '10.0.18363' then 'Windows 10 1909'
	 when s1.Build01 = '10.0.19041' then 'Windows 10 2004'
	 when s1.Build01 = '10.0.19042' then 'Windows 10 20H2'
	 when s1.Build01 = '10.0.19043' then 'Windows 10 21H1'
	 when s1.Build01 = '10.0.19044' then 'Windows 10 21H2'
	 when s1.Build01 = '10.0.19045' then 'Windows 10 22H2'
	 when s1.Build01 = '10.0.20000' then 'Windows 11 21H2'
	 when s1.Build01 = '10.0.22621' then 'Windows 11 22H2'
	 else 'Unknown MarketingName, report needs updating: ' + s1.build01
	 end as 'MarketingVersion'
	 ,MAX (CAST(SUBSTRING(s1.buildext,12,100) as int) ) as 'MaxUBR'
from v_r_system s1
where s1.Build01 like '10.0.%'
Group by s1.Build01
)
,cte2 as (
 select s1.netbios_name0 as 'ComputerName'
 ,s1.ResourceID
 ,s1.Build01
 ,cte.MarketingVersion
 ,cte.MAXUBR
 ,UBRFromHeartBeat = CAST(Replace(Right(s1.BuildExt, charindex('.',s1.BuildExt, CharIndex('.',s1.BuildExt)+1)-1), '.','') as int)
from v_r_system s1
join cte on cte.Build01=s1.Build01)

Select cte2.ComputerName
,cte2.ResourceID
,cte2.MarketingVersion
,cte2.MaxUBR
,cte2.Build01
,cte2.UBRFromHeartBeat
from cte2

Answer 2

Hi, @Peter Haslow

Thank you for posting in Microsoft Q&A forum.

You may check the hardware inventory class "Quick Fix Engineering" in client setting, then SCCM will collect the info "last patch date", after the hardware inventory run, we may query the view v_gs_patchstatusEx to get the info.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.