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,
MACHINE,
KBSYS.PATCHLINK_PATCH P,
PATCHLINK_PATCH_LABEL_JT PL,
LABEL PATCH_LABEL,
LABEL MACHINE_LABEL,
MACHINE_LABEL_JT ML
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
Answers (1)
Try this. I'm currently in the middle of rebuilding some patching stuff so I can't really test too well.
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,
MACHINE,
KBSYS.PATCHLINK_PATCH P,
PATCHLINK_PATCH_LABEL_JT PL,
LABEL PATCH_LABEL,
LABEL MACHINE_LABEL,
MACHINE_LABEL_JT ML,
PATCHLINK_PATCH_STATUS PS
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 PS.PATCHUID= P.UID
AND PS.STATUS = '0'
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
Comments:
-
I didn't see PPS in this query. I used PS. It can be changed to PPS if needed. - dugullett 11 years ago
-
Works like a charm. Cant believe I missed that. Thank you! - jparkins 11 years ago
-
I plugged your code in and it didn't return anything... Is this because I'm in MySQL Workbench? - GeekSoldier 11 years ago
-
Make sure to adjust it to match your labels, and machine names.
AND PATCH_LABEL.NAME IN('Patch - SOX Windows OS')
AND MACHINE_LABEL.NAME IN ('Windows Workstations (All)')
AND MACHINE.NAME not like '%WINDOWS-%' - dugullett 11 years ago
-
Ahhh, sorry I see it now. - GeekSoldier 11 years ago
-
Works like a charm! - GeekSoldier 11 years ago
