SQL Query
Question
Need SQl query to get detail when last patch installed with kb details like current month or last month .
solved
0
Configuration Manager
9 months
2020-07-22T10:59:39+05:30
2020-07-22T10:59:39+05:30 4 Answers
119 views
Beginner 0
Answers ( 4 )
ok let me check this .
Make sure you have v_GS_QUICK_FIX_ENGINEERING class enabled in hardware inventory.
Try this one, change the Patches accordingly and define them.Also define the collection against which you want to run it.
Declare @CollID char(8)
Set @CollID = ‘C0001420’
SELECT
COMP.name0 as ‘Host Name’,
COMP.operatingSystem0,
case when HOTFIX.HotFixID0 =’KB4561643′ then ‘Jun 20′
when HOTFIX.HotFixID0 =’KB4556836’ then ‘May 20′
when HOTFIX.HotFixID0 =’KB4550964’ then ‘Apr 20′
when HOTFIX.HotFixID0 =’KB4540688’ then ‘Mar 20′
when HOTFIX.HotFixID0 =’KB4537820’ then ‘Feb 20′
when HOTFIX.HotFixID0 =’KB4534310’ then ‘Jan 20′
when HOTFIX.HotFixID0 =’KB4530734’ then ‘Dec 19’ end ‘Patch Level’
from v_r_system COMP
JOIN v_FullCollectionMembership FCM
ON COMP.resourceID = FCM.resourceID
JOIN v_GS_QUICK_FIX_ENGINEERING HOTFIX
ON FCM.resourceID = HOTFIX.resourceID
WHERE FCM.CollectionID = @CollID
AND (HOTFIX.HotFixID0 = ‘KB4561643’ OR
HOTFIX.HotFixID0 = ‘KB4556836’ OR HOTFIX.HotFixID0 = ‘KB4550964’ OR HOTFIX.HotFixID0 = ‘KB4540688’ OR
HOTFIX.HotFixID0 = ‘KB4537820’ OR HOTFIX.HotFixID0 = ‘KB4534310’ OR HOTFIX.HotFixID0 = ‘KB4537820’ OR
HOTFIX.HotFixID0 = ‘KB4530734’)
It’s not easy to build a specific query like this
But the best way is to get the report using PowerShell script
https://www.anoopcnair.com/powershell-script-patch-installation-status/