Need assistance getting patch report to ONLY include active patches.
I have the following report and it works perfectly. I am looking to modify it to only show active patches. (PPS.STATUS = 0) but everything I try fails.
If anyone has suggestions please let me know.
Select MACHINE.NAME as MACHINE_NAME, MACHINE.USER, MACHINE.USER_FULLNAME, SUM(S.STATUS='NOTPATCHED') AS NOT_PATCHED, GROUP_CONCAT(P.TITLE) AS PATCH_NAME
FROM PATCHLINK_MACHINE_STATUS S,
WHERE MACHINE.ID = S.MACHINE_ID
AND S.PATCHUID = P.UID
AND PL.PATCHUID = P.UID
AND PATCH_LABEL.ID = PL.LABEL_ID
AND ML.MACHINE_ID = MACHINE.ID
AND MACHINE_LABEL.ID = ML.LABEL_ID
AND S.STATUS = 'NOTPATCHED'
AND PATCH_LABEL.NAME IN('Patch - SOX Windows OS')
AND MACHINE_LABEL.NAME IN ('Windows Workstations (All)')
AND MACHINE.NAME not like '%WINDOWS-%'
AND ((TIMESTAMP(LAST_SYNC) <= NOW() AND TIMESTAMP(LAST_SYNC) > DATE_SUB(NOW(),INTERVAL 14 DAY)))
group by MACHINE_NAME
order by MACHINE_NAME, P.TITLE