How can I get this report to show only Microsoft patches?
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
Answers (1)
Comments:
-
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? - oweaver 9 years ago
-
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. - jdornan 9 years ago-
Thanks again! - oweaver 9 years ago
-
Having trouble finding the platform ID for Adobe. Any other advice on how I can get this? - oweaver 9 years ago
-
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. - oweaver 9 years ago