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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share