/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (1)

Posted by: dugullett 10 years ago
Red Belt
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 10 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 10 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ