KACE Product Support

The patches deployed by a patch schedule are determined by what you have specified in the Detect Patch Label Selection and Deploy Patch Label Selection sections.  If you have patch labels setup, you can find out what patches are included pretty quickly by going into the label and expanding the Labeled Items > Patches at the bottom. 

If you don't have patch labels setup, I strongly recommend it - here's a few posts i've done on the subject if you need any help.

John

______________________

LDAP, Patching & SQL Reports - Using All Three For Efficient & Managed Patching (And Other Cool Tricks)

http://www.itninja.com/question/ldap-patching-sql-reports-using-all-three-for-efficient-managed-patching-and-other-cool-tric

______________________

Patching via K1000 Slow? Lots of Errors/Failures? Try Replication Shares

http://www.itninja.com/blog/view/patching-via-k1000-slow-lots-of-errors-failures-try-replication-shares

______________________

K1000 Report to List Free Hard Drive Space on Replication Share Machines' Targeted Drives

http://www.itninja.com/blog/view/k1000-report-to-list-free-hard-drive-space-on-replication-share-machines-targeted-drives

______________________

Also, please note that I've changed the smart label strategy I outlined in the LDAP... post.  That approach potentially missed older patches outside of the target date range, required maintenance (updating the date range, etc), and resulted in a good number of inactive patches being downloaded.  I have revised this and currently use the current criteria for my patch smart labels:

Type:  (specify - OS, App)

Operating System:  (specify - i.e. XP SP3, Win7 x64 SP1, etc)

Level:  Critical

Status: Active

More comments on this in this post:

http://www.itninja.com/question/disk-space-full-on-kbox

I got inspired after playing with a report last night...  Here's a few variations, should do what you want (except the last one - I tried to only list the most recent Last Run times to remove the "duplicates" but it drops out some PCs... if anyone has suggestions/fixes, I'm all ears).

Hope that helps!

John

__________________________________________

Complete listing, has all Last Patch Run times (based on the machine).  May have "duplicates" due to multiple Last Run times for the same patch (would indicate machine has missed scheduled time more than once).

SELECT S.DESCRIPTION as PATCH_SCHEDULE, M.NAME as MACHINE, PP.TITLE as PATCH, MS.STATUS as PATCH_STATUS, PP.RELEASEDATE as RELEASE_DATE, MS.STATUS_DT as DEPLOY_DATE, K.PHASE as PATCH_SCHEDULE_STATUS, SMS.LAST_RUN

FROM PATCHLINK_SCHEDULE_MACHINE_STATUS SMS

JOIN PATCHLINK_MACHINE_STATUS MS on (MS.MACHINE_ID = SMS.MACHINE_ID)

JOIN KBSYS.PATCHLINK_PATCH PP on (PP.UID = MS.PATCHUID)

JOIN PATCHLINK_PATCH_STATUS PPS on (PPS.PATCHUID = PP.UID and PPS.PATCHUID = MS.PATCHUID)

JOIN PATCHLINK_SCHEDULE S on (S.ID = SMS.PATCHLINK_SCHEDULE_ID)

JOIN KBSYS.KONDUCTOR_TASK K on (K.TYPE=S.KONDUCTOR_TASK_TYPE)

JOIN MACHINE M on (M.KUID = K.KUID and M.ID = SMS.MACHINE_ID)

WHERE PPS.STATUS in (0)

ORDER BY S.DESCRIPTION, M.NAME, PP.TITLE, SMS.LAST_RUN

__________________________________________

Same as above but only lists missing patches, has all Last Patch Run times (based on the machine).  May have "duplicates" due to multiple Last Run times for the same patch (would indicate machine has missed scheduled time more than once).

SELECT S.DESCRIPTION as PATCH_SCHEDULE, M.NAME as MACHINE, PP.TITLE as PATCH, MS.STATUS as PATCH_STATUS, PP.RELEASEDATE as RELEASE_DATE, MS.STATUS_DT as DEPLOY_DATE, K.PHASE as PATCH_SCHEDULE_STATUS, SMS.LAST_RUN

FROM PATCHLINK_SCHEDULE_MACHINE_STATUS SMS

JOIN PATCHLINK_MACHINE_STATUS MS on (MS.MACHINE_ID = SMS.MACHINE_ID)

JOIN KBSYS.PATCHLINK_PATCH PP on (PP.UID = MS.PATCHUID)

JOIN PATCHLINK_PATCH_STATUS PPS on (PPS.PATCHUID = PP.UID and PPS.PATCHUID = MS.PATCHUID)

JOIN PATCHLINK_SCHEDULE S on (S.ID = SMS.PATCHLINK_SCHEDULE_ID)

JOIN KBSYS.KONDUCTOR_TASK K on (K.TYPE=S.KONDUCTOR_TASK_TYPE)

JOIN MACHINE M on (M.KUID = K.KUID and M.ID = SMS.MACHINE_ID)

WHERE PPS.STATUS in (0)

AND MS.STATUS = 'NOTPATCHED'

ORDER BY S.DESCRIPTION, M.NAME, PP.TITLE, SMS.LAST_RUN

__________________________________________

* This is the one that I need help with
Slightly different, only lists the last scheduled patch run time for each patch.  Shouldn't be any duplicates as above and will only show the last run for the patch, so if you have patch schedules with overlapping patches it will only show the last one that ran (or attempted to run).

SELECT S.DESCRIPTION as PATCH_SCHEDULE, PP.TITLE as PATCH, MS.STATUS as PATCH_STATUS, PP.RELEASEDATE as RELEASE_DATE, M.NAME as MACHINE, K.PHASE as PATCH_SCHEDULE_STATUS, S.LAST_RUN as SCHEDULED_RUN

FROM PATCHLINK_SCHEDULE S

JOIN KBSYS.KONDUCTOR_TASK K on (K.TYPE = S.KONDUCTOR_TASK_TYPE)

JOIN MACHINE M on (M.KUID = K.KUID)

JOIN PATCHLINK_MACHINE_STATUS MS on (MS.MACHINE_ID = M.ID)

JOIN KBSYS.PATCHLINK_PATCH PP on (PP.UID = MS.PATCHUID)

JOIN PATCHLINK_PATCH_STATUS PPS on (PPS.PATCHUID = PP.UID and PPS.PATCHUID = MS.PATCHUID)

JOIN (SELECT S.DESCRIPTION, PP.TITLE as PATCH_TITLE, MS.STATUS, PP.RELEASEDATE,

M.NAME, K.PHASE, MAX(S.LAST_RUN) as MAX_LASTRUN
FROM PATCHLINK_SCHEDULE S

JOIN KBSYS.KONDUCTOR_TASK K on (K.TYPE = S.KONDUCTOR_TASK_TYPE)

JOIN MACHINE M on (M.KUID = K.KUID)

JOIN PATCHLINK_MACHINE_STATUS MS on (MS.MACHINE_ID = M.ID)

JOIN KBSYS.PATCHLINK_PATCH PP on (PP.UID = MS.PATCHUID)

JOIN PATCHLINK_PATCH_STATUS PPS on (PPS.PATCHUID = PP.UID and PPS.PATCHUID = MS.PATCHUID)

GROUP BY PP.TITLE) AS INNER_TABLE

WHERE PPS.STATUS in (0)

AND MS.STATUS = 'NOTPATCHED'

AND INNER_TABLE.PATCH_TITLE = PP.TITLE

AND INNER_TABLE.MAX_LASTRUN = S.LAST_RUN

ORDER BY M.NAME, PP.TITLE, S.LAST_RUN