I'd like to get this report to show only Microsoft critical patches.  Is it possible?  Thanks!!

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 = 'ML - HW - ALL SERVERS'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

0
Try adding

AND PP.PLATFORM_ID = 1
Answered 12/17/2014 by: jdornan
Red Belt

  • Thanks jdoran! That works. Guessing 1 is the Platform ID for MS Corp. How could I find our what the Platform ID is for Adobe?
    • I don't have any Adobe patches on my box but you could do a

      SELECT DISTINCT PLATFORM_ID from KBSYS.PATHCLINK_PATCH


      Mac is 7
      MS is 1

      The rest should be easy to figure out.
      • Thanks again!
      • Having trouble finding the platform ID for Adobe. Any other advice on how I can get this?
      • I get this error when I run SELECT DISTINCT PLATFORM_ID from KBSYS.PATHCLINK_PATCH. The error is - error: [1142: SELECT command denied to user 'R1'@localhost' for table 'PATCHLINK_PATCH'] in EXECUTE ("SELECT DISTINCT PLATFORM_ID from KBSYS.PATCHLINK_PATCH LIMIT 0") . I'm logged in as admin locally on the appliance.
Please log in to comment
Answer this question or Comment on this question for clarity