/build/static/layout/Breadcrumb_cap_w.png

Need assistance getting patch report to ONLY include active patches.

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


0 Comments   [ + ] Show comments

Answers (1)

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

Comments:
  • I didn't see PPS in this query. I used PS. It can be changed to PPS if needed. - dugullett 11 years ago
  • Works like a charm. Cant believe I missed that. Thank you! - jparkins 11 years ago
  • I plugged your code in and it didn't return anything... Is this because I'm in MySQL Workbench? - GeekSoldier 11 years ago
    • 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-%' - dugullett 11 years ago
  • Ahhh, sorry I see it now. - GeekSoldier 11 years ago
  • Works like a charm! - GeekSoldier 11 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

View more:

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