/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Add a Column in Report for a Subset of another Column in same report

02/14/2020 185 views

I have the following SQL that I am using to get the Patching Percentages based upon OS and it is working just fine. However, I would like to add a column to it that would Call out the Patch Severity of Critical patches that have not been patched yet based upon the NOTPATCHED column in the same query. I can get to a point where I am displaying the report, but it is SUM'ing up ALL Critical patches regardless of the detected Patch Status. Is this possible to do in the same report? See below:

WORKING SQL BELOW - BUT WOULD LIKE TO UPDATE WITH ABOVE:

-------------------------------------------------------------------------------------------------

SELECT
M.OS_NAME as OS,
 SUM(MS.DETECT_STATUS='NOTPATCHED') AS NOT_PATCHED,
 SUM(MS.DETECT_STATUS='PATCHED') AS PATCHED,
 
 ((SUM(MS.DETECT_STATUS='PATCHED'))*100)/(SUM(MS.DETECT_STATUS='PATCHED')+SUM(MS.DETECT_STATUS='NOTPATCHED')) AS PERCENT_PATCHED  
FROM
 MACHINE M
  LEFT JOIN PATCH_MACHINE_STATUS MS
  ON (M.ID = MS.MACHINE_ID)
 LEFT JOIN KBSYS.PATCH P
  ON (P.ID = MS.PATCH_ID)      
WHERE
  P.IS_SUPERCEDED != 1
GROUP BY
  M.OS_NAME
ORDER BY
NOT_PATCHED desc

------------------------------------------------------------------------------------

This SQL highlighted below is one that i have tried and resulted in the output of all critical patches regardless of Detected Patch Status:

------------------------------------------------------------------------------------

SELECT
M.OS_NAME as OS,
 SUM(MS.DETECT_STATUS='NOTPATCHED') AS NOT_PATCHED,

 SUM(P.SEVERITY='CRITICAL') UNPATCHED_CRITICALS,
 SUM(MS.DETECT_STATUS='PATCHED') AS PATCHED,
 
 ((SUM(MS.DETECT_STATUS='PATCHED'))*100)/(SUM(MS.DETECT_STATUS='PATCHED')+SUM(MS.DETECT_STATUS='NOTPATCHED')) AS PERCENT_PATCHED  
FROM
 MACHINE M
  LEFT JOIN PATCH_MACHINE_STATUS MS
  ON (M.ID = MS.MACHINE_ID)
 LEFT JOIN KBSYS.PATCH P
  ON (P.ID = MS.PATCH_ID)      
WHERE
  P.IS_SUPERCEDED != 1
GROUP BY
  M.OS_NAME
ORDER BY
NOT_PATCHED desc

0 Comments   [ + ] Show comments

Comments


Be the first to answer this question

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