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

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2

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
Answered 03/26/2013 by: dugullett
Red Belt

  • I didn't see PPS in this query. I used PS. It can be changed to PPS if needed.
  • Works like a charm. Cant believe I missed that. Thank you!
  • I plugged your code in and it didn't return anything... Is this because I'm in MySQL Workbench?
    • 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-%'
  • Ahhh, sorry I see it now.
  • Works like a charm!
Please log in to comment
Answer this question or Comment on this question for clarity