Scripting

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