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

Comments

Please log in to comment

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

 
Answered 09/09/2013 by: dugullett
Red Belt

  • Thanks, is there a way to do the same filtering but by monthly scans/deployments?
    • 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)
Please log in to comment
Answer this question or Comment on this question for clarity