Patch Report
I am new to SQL query need help editing this query. Its not adding all computers in our inventory into report. it looks like it pulls only those that are being patched by dell kace is there a way to adjust this script to create report for all computers?
SELECT M.NAME AS MACHINE_NAME,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
ROUND(((SUM(MS.STATUS='PATCHED'))*100)/(SUM(MS.STATUS='PATCHED')+SUM(MS.STATUS='NOTPATCHED'))) 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)
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME
Answers (2)
Top Answer
SELECT
M.NAME AS MACHINE_NAME,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
ROUND(((SUM(MS.STATUS='PATCHED'))*100)/(SUM(MS.STATUS='PATCHED')+SUM(MS.STATUS='NOTPATCHED'))) AS PERCENT_PATCHED
FROM
MACHINE M
LEFT JOIN PATCHLINK_MACHINE_STATUS MS
ON (M.ID = MS.MACHINE_ID)
LEFT JOIN KBSYS.PATCHLINK_PATCH PP
ON (PP.UID = MS.PATCHUID)
/*JOIN PATCHLINK_PATCH_STATUS PPS
ON (PPS.PATCHUID = PP.UID)*/ /* NOT NEEDED */
/*JOIN MACHINE_LABEL_JT ML
ON (M.ID = ML.MACHINE_ID)*/ /* NOT NEEDED */
/*JOIN LABEL L
ON (ML.LABEL_ID = L.ID) */ /* NOT NEEDED */
GROUP BY
M.NAME
ORDER BY
PERCENT_PATCHED, M.NAME
