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
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
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
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
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