I have a SQL report that shows compliance per computer with a custom patch label filter , how can I show overall compliance percentage?
Hello All,
I have a SQL patch report that will show me # of patches installed / not installed by computer name.
Select M.NAME as MACHINE_NAME, SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
GROUP_CONCAT(CASE WHEN MS.STATUS = 'PATCHED' THEN PP.TITLE END) AS 'Titles Patched',
GROUP_CONCAT(CASE WHEN MS.STATUS = 'NOTPATCHED' THEN PP.TITLE END) AS 'Titles Not Patched'
FROM ORG1.PATCHLINK_MACHINE_STATUS MS
JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN PATCHLINK_PATCH_LABEL_JT ON PATCHLINK_PATCH_LABEL_JT.PATCHUID = PP.UID
JOIN LABEL ON LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID
where PPS.STATUS = 0 /* 0=active patches */
AND LABEL.NAME = "Your Custom Patch Filter Label Here"
group by MACHINE_NAME
order by MACHINE_NAME, PP.TITLE
My question is, how do I show compliance %? I need to show overall domain wide compliance %, per PC compliance % would be nice as well but not as important.
Many thanks
Answers (1)
I might not be following you exactly but this produces a report that shows every PC, number patched, number unpatched, and the percentage.
SELECT
M.NAME AS MACHINE_NAME,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
((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)
WHERE
PP.IS_SUPERCEDED != 1
GROUP BY
M.NAME
ORDER BY
PERCENT_PATCHED, M.NAME