Is there a KACE Report showing the detail of patches installed by a patch schedule
Does anyone know if there is a report or query that I can run on my kbox that will tell me which patches were installed on a machine, by a particular patch schedule. I found the Scheduled task status at the bottom of the Patch Schedule detail, but that just gives the total number of patches that were installed, and what I am looking for is along the lines of the MS KB #s that get installed on each machine by the schedule.
Thanks
Answers (2)
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)
______________________
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
______________________
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
Comments:
-
Thanks John, will definitely be looking into more of the reports you linked for me. Also I found that by increasing my number of retries for each patch schedule, I'm seeing very few outright failures for each update.
Peter - pazouz 12 years ago -
If you are dealing with patching failures, check these posts I did - taking this approach resolved pretty much all of the patch failures I was seeing:
http://www.itninja.com/blog/view/patching-via-k1000-slow-lots-of-errors-failures-try-replication-shares
http://www.itninja.com/blog/view/determining-if-patches-pulled-from-replication-share-or-k1000
John - jverbosk 12 years ago
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
