I am trying to run a script that John was nice enough to share and I can get it to work only if I have one label in the query:

SELECT M.NAME AS MACHINE_NAME,
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 PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'Server Group 1'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME

I would like to be able to put all six groups into one report if possible. I also would like the report to just show the last thirty days.  

http://www.itninja.com/blog/view/k1000-reports-patching-reports-for-completion-by-patch-machine-vendor-using-labels 

Now what would be awesome is if someone could point out how I could get this to show multiple labels, the patch that was installed and what critical patches were installed!

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

-1

Change this line 

AND L.NAME = 'Server Group 1'

to this 

AND L.NAME RLIKE 'Server Group 1|Server Group 2| Server Group 3'
Answered 08/15/2013 by: dugullett
Red Belt

  • Just saw the comment about the last 30 days. Are you talking about machine check in the past 30 days, or patch release date in the past 30 days?
  • DANG!!!! I forgot the rLike!!! D'oh!!

    The patches that were released in the previous month. (Ie July's critcals) That way I can tell compliance the percentage each month.
    • Add this line for for patches released in the past 30 days.

      AND PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 30 DAY)
      • What line do I need to add that to? When I place that line under the 'where' statement; nothing resolves....
      • I usually add lines like this to the first line in the WHERE.

        SELECT M.NAME AS MACHINE_NAME,
        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 PATCHLINK_MACHINE_STATUS MS
        JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
        JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
        JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
        JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
        JOIN LABEL L ON (ML.LABEL_ID = L.ID)
        WHERE PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 30 DAY)
        AND PP.IMPACTID = 'Critical'
        AND PPS.STATUS = 0
        AND PP.IS_SUPERCEDED = 0
        AND L.NAME like 'MACHINE_LABEL%'
        GROUP BY M.NAME
        ORDER BY PERCENT_PATCHED, M.NAME
Please log in to comment
Answer this question or Comment on this question for clarity