Patch Report
Someone on teh forum was nice enough to write this script for patch reporting, however it does not populate teh adobe or java critical patches. If someone could take a look that would be great.
SELECT RELEASEDATE,VENDOR,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,IMPACTID,
(CASE PATCHLINK_PATCH_STATUS.STATUS WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown' END) AS PATCH_STATUS,
SUM(P.STATUS='PATCHED') AS PATCHED,SUM(P.STATUS='NOTPATCHED') AS NOTPATCHED, ROUND((SUM(P.STATUS='PATCHED')/(SUM(P.STATUS='PATCHED')+SUM(P.STATUS='NOTPATCHED')))*100,0) AS PERCENT,TITLE FROM KBSYS.PATCHLINK_PATCH LEFT JOIN PATCHLINK_PATCH_LABEL_JT ON (PATCHLINK_PATCH_LABEL_JT.PATCHUID = KBSYS.PATCHLINK_PATCH.UID)
LEFT JOIN LABEL ON (LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID)
LEFT JOIN PATCHLINK_PATCH_STATUS ON PATCHLINK_PATCH_STATUS .PATCHUID = PATCHLINK_PATCH.UID LEFT JOIN PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID WHERE (RELEASEDATE > DATE_SUB(NOW(),INTERVAL 30 DAY)) AND ((VENDOR = 'Sun Microsystems') OR (VENDOR = 'Adobe') OR (VENDOR = 'Microsoft Corp.')AND (LABEL.NAME like '%Critical%'AND PATCHLINK_PATCH_STATUS.STATUS = 0))
GROUP BY KBSYS.PATCHLINK_PATCH.ID ORDER BY LABEL_NAME_GROUPED desc,PATCH_STATUS desc
Answers (2)
Hi tjbake let me know if this works for you.
SELECT
RELEASEDATE, VENDOR, GROUP_CONCAT(DISTINCT LABEL.NAME) AS LABEL_NAME_GROUPED, IMPACTID,
(CASE PATCHLINK_PATCH_STATUS.STATUS WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown'
END) AS PATCH_STATUS, SUM(P.STATUS = 'PATCHED') AS PATCHED, SUM(P.STATUS = 'NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(P.STATUS = 'PATCHED') / (SUM(P.STATUS = 'PATCHED') + SUM(P.STATUS = 'NOTPATCHED'))) * 100,0)
AS PERCENT, TITLE
FROM
KBSYS.PATCHLINK_PATCH
LEFT JOIN
PATCHLINK_PATCH_LABEL_JT ON (PATCHLINK_PATCH_LABEL_JT.PATCHUID = KBSYS.PATCHLINK_PATCH.UID)
LEFT JOIN
LABEL ON (LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID)
LEFT JOIN
PATCHLINK_PATCH_STATUS ON PATCHLINK_PATCH_STATUS.PATCHUID = PATCHLINK_PATCH.UID
LEFT JOIN
PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID
WHERE
((VENDOR = 'Sun Microsystems') OR (VENDOR = 'Adobe') OR (VENDOR = 'Microsoft Corp.')
AND (IMPACTID like '%Critical%' AND PATCHLINK_PATCH_STATUS.STATUS = 0))
GROUP BY KBSYS.PATCHLINK_PATCH.ID
ORDER BY LABEL_NAME_GROUPED desc , PATCH_STATUS desc
Comments:
-
Charles. This is what I get
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY LABEL_NAME_GROUPED desc , PATCH_STATUS desc LIMIT 0' at line 3] in EXECUTE(
"SELECT RELEASEDATE, VENDOR, GROUP_CONCAT(DISTINCT LABEL.NAME) AS LABEL_NAME_GROUPED, IMPACTID,(CASE PATCHLINK_PATCH_STATUS.STATUS WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown' END) AS PATCH_STATUS, SUM(P.STATUS = 'PATCHED') AS PATCHED, SUM(P.STATUS = 'NOTPATCHED') AS NOTPATCHED, ROUND((SUM(P.STATUS = 'PATCHED') / (SUM(P.STATUS = 'PATCHED') + SUM(P.STATUS = 'NOTPATCHED'))) * 100,0) AS PERCENT, TITLE
FROM KBSYS.PATCHLINK_PATCH LEFT JOIN PATCHLINK_PATCH_LABEL_JT ON (PATCHLINK_PATCH_LABEL_JT.PATCHUID = KBSYS.PATCHLINK_PATCH.UID) LEFT JOIN LABEL ON (LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID) LEFT JOIN PATCHLINK_PATCH_STATUS ON PATCHLINK_PATCH_STATUS.PATCHUID = PATCHLINK_PATCH.UID LEFT JOIN PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID
WHERE ((VENDOR = 'Sun Microsystems') OR (VENDOR = 'Adobe') OR (VENDOR = 'Microsoft Corp.') AND (IMPACTID like '%Critical%' AND PATCHLINK_PATCH_STATUS.STATUS = 0))GROUP BY KBSYS.PATCHLINK_PATCH.IDORDER BY LABEL_NAME_GROUPED desc , PATCH_STATUS desc LIMIT 0") - tjbake 12 years ago
If you just want to list all of the active and critical patches for all machines, you could use these - they're what I use, although I typically use variations of the last example so I can focus on specific machine labels (like control, server and non-server/control PCs, i.e. the rest).
This first report is sorted by patch:
*SQL Select Statement*
SELECT PP.TITLE AS 'Patch Name',
M.NAME AS 'Computer Name',
OS_NAME AS 'Windows Version',
PP.RELEASEDATE AS 'Released'
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
WHERE MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY PP.RELEASEDATE, PP.TITLE, M.NAME
*Break on Columns*
Patch Name
_____________________________________
Here's the same report, but sorted by machine.
*SQL Select Statement*
SELECT PP.TITLE AS 'Patch Name',
M.NAME AS 'Computer Name',
OS_NAME AS 'Windows Version',
PP.RELEASEDATE AS 'Released'
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
WHERE MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY M.NAME, PP.TITLE
*Break on Columns*
Computer Name
_____________________________________
If you want to focus on specific machine labels, just put your label name after LABEL.NAME - in example below I'm targeting my 'control' label.
*SQL Select Statement*
SELECT PP.TITLE AS 'Patch Name',
M.NAME AS 'Computer Name',
OS_NAME AS 'Windows Version',
PP.RELEASEDATE AS 'Released'
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
WHERE (1 in (select 1 from LABEL, MACHINE_LABEL_JT where M.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'control'))
AND MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY M.NAME, PP.TITLE
*Break on Columns*
Computer Name
_____________________________________
Hope that helps!
John
