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.

The Query:

SELECT

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.

See you in my next post!