Here again your favorite Random Dude.
Today's topic goes about something that a lot of people that use the SMA need, a report for installed patches for a patch job. There used to be a good amount of examples in ITNinja, but since the big schema changes in version 10.0 most probably, none of them are working now. I made this one and I hope it helps you as much as it helps me.
M.NAME as COMPUTER_NAME,
MS.PATCH_ID AS PACHT_ID,
PP.TITLE as PATCH_NAME,
PP.IMPACT_INSTALL as PATCH_IMPACT,
P.DESCRIPTION as SHEDULE_NAME,
MS.DETECT_STATUS AS DETECT_STATUS,
MS.DEPLOY_STATUS_DT as INSTALL_DATE
from MACHINE M\left join PATCH_MACHINE_STATUS MS on MS.MACHINE_ID = M.ID
left join KBSYS.PATCH PP on PP.ID = MS.PATCH_ID
left join PATCH_SCHEDULE_RUN_MACHINE PS on PS.MACHINE_ID = MS.MACHINE_ID
left join PATCH_SCHEDULE P on P.PATCH_SCHEDULE_RUN_ID = PS.PATCH_SCHEDULE_RUN_ID
WHERE P.DESCRIPTION = 'test job'
AND MS.DETECT_STATUS = 'NOTPATCHED'
ORDER BY M.NAME, PP.TITLE
Note: just be sure to change the job name so it matches yours.
Note #2: if you remove the the line where we specify the patch job name it should bring the entire patch list for all devices.
The results should look like these
PS: I am open for any corrections or improvement that anyone can share.