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
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • 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.
    • 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!
Please log in to comment

Answer Chosen by the Author

1
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.

Answered 03/22/2016 by: chucksteel
Red Belt

  • Many thanks Chuck! Thats what we wanted. Here, take my upvote!
Please log in to comment

Answers

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

Answered 03/22/2016 by: chucksteel
Red Belt

  • 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!
Please log in to comment
Answer this question or Comment on this question for clarity