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’)
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/