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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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
Answered 06/20/2012 by: CharlesG
Yellow Belt

  • 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")
Please log in to comment
0

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

Answered 06/22/2012 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity