/build/static/layout/Breadcrumb_cap_w.png
11/12/2018 135 views

I have a Patch Schedule to install patches under a Patch Label to devices in a Device Label. After running the schedule, it completes and I see the "Show All" in the schedule.  However, im not sure how to get this data out, on a schedule, in a more "management friendly" format.  Is there a report or can anyone help with a sql query to report on a patch schedule? I would like to show the machines in the label, show the patches in the patch label, and report on if the patches were installed or failed. Does this make sense?

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1

A report that shows the individual patches per machine would either be very long or very difficult to read. Here is a report that shows the patching status per machine and patching schedule (machines will show multiple times if they have more than one patching schedule applied):

SELECT MACHINE.ID, MACHINE.NAME, P.DESCRIPTION, PSMS.PATCHED, PSMS.NOTPATCHED, PSMS.DETECT_FAILURES, PSMS.LAST_RUN, K.PHASE, K.TYPE
FROM PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS
JOIN MACHINE on MACHINE.ID = PSMS.MACHINE_ID
JOIN PATCHLINK_SCHEDULE P on P.ID = PSMS.PATCHLINK_SCHEDULE_ID
JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE and K.KUID = MACHINE.KUID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
WHERE 
LABEL.NAME = "User Services"
ORDER BY MACHINE.NAME

Here is another one that is more specific and I wrote for http://www.itninja.com/question/show-update-software-packages-in-different-scripts 

SELECT M.NAME AS MACHINE_NAME, M.ID,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM MACHINE M
LEFT JOIN PATCHLINK_MACHINE_STATUS MS ON (M.ID = MS.MACHINE_ID)
LEFT JOIN KBSYS.PATCHLINK_PATCH PP ON 
(MS.PATCHUID = PP.UID AND PP.IMPACTID = 'Critical' 
AND RELEASEDATE between DATE_SUB(NOW(),INTERVAL 60 DAY) and DATE_SUB(NOW(),INTERVAL 30 DAY)
AND PP.IS_SUPERCEDED = 0)
LEFT JOIN PATCHLINK_PATCH_STATUS PPS ON (PP.UID = PPS.PATCHUID AND PPS.STATUS = 0)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE L.NAME = 'Test Patching'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME

This has the critical status and release date restrictions removed:

SELECT M.NAME AS MACHINE_NAME, M.ID,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM MACHINE M
LEFT JOIN PATCHLINK_MACHINE_STATUS MS ON (M.ID = MS.MACHINE_ID)
LEFT JOIN KBSYS.PATCHLINK_PATCH PP ON MS.PATCHUID = PP.UID AND PP.IS_SUPERCEDED = 0
LEFT JOIN PATCHLINK_PATCH_STATUS PPS ON (PP.UID = PPS.PATCHUID AND PPS.STATUS = 0)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE L.NAME = 'Test Patching'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME

If none of these work, let me know. 

Answered 11/13/2018 by: chucksteel
Red Belt

  • Thanks. It helps a lot!

All Answers

0

Have you checked all the built in Patching reports ?

Answered 11/12/2018 by: AbhayR
Red Belt