/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


What is the proper SQL to create a report that only shows missing patches using the GROUP_CONCAT

01/30/2020 121 views

I have the following SQL in place that is working.

SELECT M.NAME AS MACHINE_NAME,

OS_NAME AS WINDOWS_VERSION,

GROUP_CONCAT(PP.TITLE) AS PATCH_NAME,

SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,

SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,

FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS = 'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS PERCENT_PATCHED

FROM PATCH_MACHINE_STATUS MS

JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)

JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)

JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)

JOIN KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)

JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = M.ID

JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID

WHERE PPS.STATUS = 0

AND PP.IS_SUPERCEDED = 0

AND PPS.IS_SUPERCEDED = 0

AND MS.PATCH_APPLICABLE = 1

AND PP.CREATION_DATE between '2020-01-01' and '2020-12-31'

GROUP BY MS.MACHINE_ID

ORDER BY PERCENT_PATCHED , M.NAME


However I only want to Patch Name column to only show missing patches not all patches.


1 Comment   [ + ] Show comment

Comments

  • Thanks @chucksteel this works out perfect.

All Answers

0

I ended up re-writing the query to simplify it and resolve an issue where it was counting needed patches inaccurately:

SELECT M.NAME, OS_NAME,
SUM(MS.PATCH_INSTALLED) as 'Installed',
SUM(MS.PATCH_APPLICABLE) as 'Needed',
GROUP_CONCAT(IF(DETECT_STATUS = 'NOTPATCHED', TITLE, null)) AS 'Needed Patches',
FLOOR(ROUND((SUM(PATCH_INSTALLED) / COUNT(MS.PATCH_ID)) * 100,1)) AS PERCENT_PATCHED
FROM ORG1.PATCH_MACHINE_STATUS MS
JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
WHERE
PPS.STATUS = 0
AND PPS.IS_SUPERCEDED = 0
AND YEAR(PP.CREATION_DATE) = YEAR(NOW())
GROUP BY M.ID
ORDER BY M.NAME

Some notes on the changes:

  • I'm using the PATCH_INSTALLED and PATCH_APPLICABLE columns instead of conditional sums. In my testing those columns equate to the patch being installed or not installed.
  • The conditional group_concat is the answer to your original question.
  • I simplified the percent patched column.
  • I removed joins you weren't using in your query. If you want to limit this by label those two tables will need to be added again.
  • You were checking if the patch is superceded in both the global (KBSYS) and organization tables. I removed the KBSYS check to make the query organization specific and non-redundant.
  • I changed the criteria for the patch creation date to be relative instead of hard coded to 2020.

Let me know if that works for you.



Answered 01/31/2020 by: chucksteel
Red Belt

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