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

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

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

 

 

Answered 06/21/2012 by: jverbosk
Red Belt

  • 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
  • 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
Please log in to comment

Answers

0

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

Answered 06/30/2012 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity