K1000 Reporting - Monthly patch report
Hi,
I've got this query to generate a report based on my list of approved patches "My Patch Label"
SELECT LABL.NAME as MACHINE_GROUP,
L.NAME as PATCH_GROUP,
MACH.NAME as MACHINE,
PP.TITLE AS PATCH_TITLE,
CASE PLMS.STATUS
WHEN 'PATCHED' THEN 'Patched'
WHEN 'NOTPATCHED' THEN 'NOT Patched'
ELSE 'Unknown'
END as PATCH_STATUS,
PLMS.STATUS_DT as STATUS_AS_OF,
PLMS.DEPLOY_STATUS_DT as DEPLOYED_AS_OF
FROM ORG2.PATCHLINK_PATCH_LABEL_JT PPLJT
join
ORG2.LABEL L
on L.ID = PPLJT.LABEL_ID
join
KBSYS.PATCHLINK_PATCH PP
on PP.UID = PPLJT.PATCHUID
join
ORG2.PATCHLINK_MACHINE_STATUS PLMS
ON PP.UID = PLMS.PATCHUID
JOIN
ORG2.MACHINE MACH
ON MACH.ID = PLMS.MACHINE_ID
JOIN
ORG2.MACHINE_LABEL_JT MLJT
ON MACH.ID = MLJT.MACHINE_ID
JOIN
ORG2.LABEL LABL
ON MLJT.LABEL_ID = LABL.ID
WHERE L.NAME IN ('My Patch Lablel')
order by LABL.NAME,
PPLJT.LABEL_ID,
MACH.NAME
but as we continue to use KACE for patch deployment the size of the reports continues to grow. Does anyone know how can I introduce a filter by date so the same report is generated but only of the activities done through a specific month?
Thanks
Answers (1)
If you add this line it will filter only patches released in the past month.
AND PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 1 MONTH)
Updated Query:
SELECT LABL.NAME as MACHINE_GROUP,
L.NAME as PATCH_GROUP,
MACH.NAME as MACHINE,
PP.TITLE AS PATCH_TITLE,
CASE PLMS.STATUS
WHEN 'PATCHED' THEN 'Patched'
WHEN 'NOTPATCHED' THEN 'NOT Patched'
ELSE 'Unknown'
END as PATCH_STATUS,
PLMS.STATUS_DT as STATUS_AS_OF,
PLMS.DEPLOY_STATUS_DT as DEPLOYED_AS_OF
FROM ORG2.PATCHLINK_PATCH_LABEL_JT PPLJT
join ORG2.LABEL L on L.ID = PPLJT.LABEL_ID
join KBSYS.PATCHLINK_PATCH PP on PP.UID = PPLJT.PATCHUID
join
ORG2.PATCHLINK_MACHINE_STATUS PLMS
ON PP.UID = PLMS.PATCHUID
JOIN
ORG2.MACHINE MACH
ON MACH.ID = PLMS.MACHINE_ID
JOIN
ORG2.MACHINE_LABEL_JT MLJT
ON MACH.ID = MLJT.MACHINE_ID
JOIN
ORG2.LABEL LABL
ON MLJT.LABEL_ID = LABL.IDa
WHERE L.NAME IN ('My Patch Lablel')
AND PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 1 MONTH)
order by LABL.NAME,
PPLJT.LABEL_ID,
MACH.NAME
Comments:
-
Thanks, is there a way to do the same filtering but by monthly scans/deployments? - hector_munoz_za 11 years ago
-
You can play around with the detect/deploy date fields.
Patch detected in the past month.
PLMS.STATUS_DT > DATE_SUB(NOW(), INTERVAL 1 MONTH)
Patch Deployed in the past month.
PLMS.DEPLOY_STATUS_DT > DATE_SUB(NOW(), INTERVAL 1 MONTH) - dugullett 11 years ago
