/build/static/layout/Breadcrumb_cap_w.png

List unpatched updates on already made report

Hi everyone!

I have a SQL report that outputs me the number of installed patches, not installed patches and the percentage relative to the previous two informations on each computer. Variables are patch impact and device label. An example of outcome is something like this:

Computer name    User name    SO    Patched    Unpatched    Percent
PC01                     User 1           W7    5               152                3
PC02                     User 2           XP     7               124                5

What I need is the report to list me which patches are not installed. I.e.: I could be able to explode the PC01 line to check all the 152 patches which are not installed. Could you please help me mod the SQL query? Thanks in advance!

Original query:

SELECT M.NAME AS MACHINE_NAME,
USER_NAME,
OS_NAME AS OS_Name,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
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 rlike 'Estações Trabalho'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME

1 Comment   [ + ] Show comment
  • The SQL reports don't allow you to expand a row with a subquery so you would need to have a list of all of the patches in a column. Depending on the number of patches not installed that will be a very long column. - chucksteel 8 years ago
    • Hi Chuck!

      Thanks for you reply. That won't be a problem. The info can be shown in another column, even if it'll be a long one.

      Could you please update the query? Many thanks! - mchawk 8 years ago

Answers (2)

Answer Summary:
Posted by: chucksteel 8 years ago
Red Belt
1
Give this a try:
SELECT M.NAME AS MACHINE_NAME,
USER_NAME,
OS_NAME AS OS_Name,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
+SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED,
(SELECT GROUP_CONCAT(KBSYS.PATCHLINK_PATCH.TITLE)
FROM ORG1.PATCHLINK_MACHINE_STATUS 
JOIN KBSYS.PATCHLINK_PATCH on KBSYS.PATCHLINK_PATCH.UID = ORG1.PATCHLINK_MACHINE_STATUS.PATCHUID 
JOIN ORG1.MACHINE on MACHINE.ID = ORG1.PATCHLINK_MACHINE_STATUS.MACHINE_ID 
WHERE MACHINE.NAME = M.NAME
             and ORG1.PATCHLINK_MACHINE_STATUS.STATUS = 'NOTPATCHED'
             GROUP BY MACHINE.NAME) AS "Patches Needed"
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 rlike 'Estações Trabalho'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME


Comments:
  • Many thanks chuck! You got the point, but the report is showing all the patches, even the installed ones. So more down the report I have devices with 0 unpatched but the "Patches Needed" is full of entries.

    Noob question: Is there any way I can submit and attachment here?

    Thanks again! - mchawk 8 years ago
Posted by: chucksteel 8 years ago
Red Belt
1

Top Answer

I wasn't using the same criteria for the sub select statement as the main statement so more updates were being selected. Here is an updated version:
SELECT M.NAME AS MACHINE_NAME,
USER_NAME,
OS_NAME AS OS_Name,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
+SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED,
(SELECT GROUP_CONCAT(KBSYS.PATCHLINK_PATCH.TITLE)
FROM ORG1.PATCHLINK_MACHINE_STATUS 
JOIN KBSYS.PATCHLINK_PATCH on KBSYS.PATCHLINK_PATCH.UID = ORG1.PATCHLINK_MACHINE_STATUS.PATCHUID 
JOIN ORG1.MACHINE on MACHINE.ID = ORG1.PATCHLINK_MACHINE_STATUS.MACHINE_ID 
WHERE MACHINE.NAME = MACHINE_NAME
             and ORG1.PATCHLINK_MACHINE_STATUS.STATUS = 'NOTPATCHED'
             and PATCHLINK_PATCH.IMPACTID = 'Critical'
             and PATCHLINK_PATCH.IS_SUPERCEDED = 0
             GROUP BY MACHINE.NAME) AS "Patches Needed"
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 rlike 'Estações Trabalho'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME
You can only post attachments in an original answer, not in comments.


Comments:
  • Many thanks Chuck! Thats what we wanted. Here, take my upvote! - mchawk 8 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ