Is there any way to create a report that would show me either the last date a computer was patched or show me the number of patches a computer is missing?  I'm not necessarily interested in details like which patches they are, just how many or how infrequently so I know who I need to talk to about getting patches done.  Thanks!
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

try this:


SELECT MACHINE.NAME as MACHINE_NAME, PATCHLINK_MACHINE_STATUS.MAX_DEPLOY_ATTEMPT - PATCHLINK_MACHINE_STATUS.DEPLOY_ATTEMPT_COUNT as DEPLOY_REMAINING, DEPLOY_STATUS_DT, DEPLOY_ERROR_CODE, DEPLOY_STATUS, STATUS_DT  FROM PATCHLINK_MACHINE_STATUS left join KBSYS.PATCHLINK_PATCH on KBSYS.PATCHLINK_PATCH.UID = PATCHLINK_MACHINE_STATUS.PATCHUID left join MACHINE on MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID    GROUP BY PATCHLINK_MACHINE_STATUS.MACHINE_ID,PATCHLINK_MACHINE_STATUS.PATCHUID ORDER BY MACHINE_NAME

Answered 06/18/2014 by: mramsdell
Orange Senior Belt

  • Thanks for the reply and trying to help! Unfortunately I got the following error: mysql error: [1690: BIGINT UNSIGNED value is out of range in '(`ORG1`.`PATCHLINK_MACHINE_STATUS`.`MAX_DEPLOY_ATTEMPT` - `ORG1`.`PATCHLINK_MACHINE_STATUS`.`DEPLOY_ATTEMPT_COUNT`)'] in EXECUTE("SELECT MACHINE.NAME as MACHINE_NAME, PATCHLINK_MACHINE_STATUS.MAX_DEPLOY_ATTEMPT - PATCHLINK_MACHINE_STATUS.DEPLOY_ATTEMPT_COUNT as DEPLOY_REMAINING, DEPLOY_STATUS_DT, DEPLOY_ERROR_CODE, DEPLOY_STATUS, STATUS_DT FROM PATCHLINK_MACHINE_STATUS left join KBSYS.PATCHLINK_PATCH on KBSYS.PATCHLINK_PATCH.UID = PATCHLINK_MACHINE_STATUS.PATCHUID left join MACHINE on MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID GROUP BY PATCHLINK_MACHINE_STATUS.MACHINE_ID,PATCHLINK_MACHINE_STATUS.PATCHUID ORDER BY MACHINE_NAME")
    • What version of the K1000 do you have?
    • Try this


      SELECT distinct MACHINE.NAME as MACHINE_NAME, PATCHLINK_MACHINE_STATUS.MAX_DEPLOY_ATTEMPT - PATCHLINK_MACHINE_STATUS.DEPLOY_ATTEMPT_COUNT as DEPLOY_REMAINING FROM PATCHLINK_MACHINE_STATUS left join KBSYS.PATCHLINK_PATCH on KBSYS.PATCHLINK_PATCH.UID = PATCHLINK_MACHINE_STATUS.PATCHUID left join MACHINE on MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID WHERE ((PATCHLINK_MACHINE_STATUS.DEPLOY_ATTEMPT_COUNT > '0')) GROUP BY PATCHLINK_MACHINE_STATUS.MACHINE_ID,PATCHLINK_MACHINE_STATUS.PATCHUID ORDER BY MACHINE_NAME
      • This content is currently hidden from public view.
        Reason: Removed by member request
        For more information, visit our FAQ's.
      • Sorry, got this error: mysql error: [1690: BIGINT UNSIGNED value is out of range in '(`ORG1`.`PATCHLINK_MACHINE_STATUS`.`MAX_DEPLOY_ATTEMPT` - `ORG1`.`PATCHLINK_MACHINE_STATUS`.`DEPLOY_ATTEMPT_COUNT`)'] in EXECUTE("SELECT distinct MACHINE.NAME as MACHINE_NAME, PATCHLINK_MACHINE_STATUS.MAX_DEPLOY_ATTEMPT - PATCHLINK_MACHINE_STATUS.DEPLOY_ATTEMPT_COUNT as DEPLOY_REMAINING FROM PATCHLINK_MACHINE_STATUS left join KBSYS.PATCHLINK_PATCH on KBSYS.PATCHLINK_PATCH.UID = PATCHLINK_MACHINE_STATUS.PATCHUID left join MACHINE on MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID WHERE ((PATCHLINK_MACHINE_STATUS.DEPLOY_ATTEMPT_COUNT > '0')) GROUP BY PATCHLINK_MACHINE_STATUS.MACHINE_ID,PATCHLINK_MACHINE_STATUS.PATCHUID ORDER BY MACHINE_NAME")
Please log in to comment
0

OK. Im going to try one more time. I sanitized the data so hopefully it works. It works great on my box but I don't do many updates.


SELECT distinct MACHINE.NAME as MACHINE_NAME, Cast(PATCHLINK_MACHINE_STATUS.MAX_DEPLOY_ATTEMPT - PATCHLINK_MACHINE_STATUS.DEPLOY_ATTEMPT_COUNT as UNSIGNED) as DEPLOY_REMAINING


FROM PATCHLINK_MACHINE_STATUS


left join KBSYS.PATCHLINK_PATCH


on KBSYS.PATCHLINK_PATCH.UID = PATCHLINK_MACHINE_STATUS.PATCHUID


left join MACHINE on MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID


WHERE ((PATCHLINK_MACHINE_STATUS.DEPLOY_ATTEMPT_COUNT > '0'))


GROUP BY PATCHLINK_MACHINE_STATUS.MACHINE_ID,PATCHLINK_MACHINE_STATUS.PATCHUID


ORDER BY MACHINE_NAME

Answered 06/18/2014 by: mramsdell
Orange Senior Belt

  • Thanks for trying, unfortunately I'm still getting this: mysql error: [1690: BIGINT UNSIGNED value is out of range in '(`ORG1`.`PATCHLINK_MACHINE_STATUS`.`MAX_DEPLOY_ATTEMPT` - `ORG1`.`PATCHLINK_MACHINE_STATUS`.`DEPLOY_ATTEMPT_COUNT`)'] in EXECUTE("SELECT distinct MACHINE.NAME as MACHINE_NAME, Cast(PATCHLINK_MACHINE_STATUS.MAX_DEPLOY_ATTEMPT - PATCHLINK_MACHINE_STATUS.DEPLOY_ATTEMPT_COUNT as UNSIGNED) as DEPLOY_REMAINING FROM PATCHLINK_MACHINE_STATUS left join KBSYS.PATCHLINK_PATCH on KBSYS.PATCHLINK_PATCH.UID = PATCHLINK_MACHINE_STATUS.PATCHUID left join MACHINE on MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID WHERE ((PATCHLINK_MACHINE_STATUS.DEPLOY_ATTEMPT_COUNT > '0')) GROUP BY PATCHLINK_MACHINE_STATUS.MACHINE_ID,PATCHLINK_MACHINE_STATUS.PATCHUID ORDER BY MACHINE_NAME ")
    • You can try this one...will have to setup a filter or other in Excel to find what you are looking for but will give you the numbers of machines missing each patch.

      SELECT GROUP_CONCAT(DISTINCT O.DESCRIPTION SEPARATOR '\n') AS OS_NAMES, (IF(V.ATTRVALUE <> '', V.ATTRVALUE, 'Not Available')) AS VENDOR_RATING, SUM(P.STATUS='NOTPATCHED') AS NOTPATCHED, SUM(P.STATUS='PATCHED') AS PATCHED, CASE ifnull(PATCHLINK_PATCH_STATUS.STATUS,'') WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown' END AS PP_STATUS, TITLE FROM KBSYS.PATCHLINK_PATCH LEFT JOIN KBSYS.PATCHLINK_LST_PATCH_JT ON KBSYS.PATCHLINK_LST_PATCH_JT.PATCHUID=KBSYS.PATCHLINK_PATCH.UID LEFT JOIN KBSYS.PATCHLINK_LST O ON O.ID=KBSYS.PATCHLINK_LST_PATCH_JT.LST_ID LEFT JOIN KBSYS.PATCHLINK_VENDORATTRIBUTE V ON V.PATCHUID=PATCHLINK_PATCH.UID AND V.ATTR = 'MaximumSeverityRating' LEFT JOIN PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID LEFT JOIN PATCHLINK_PATCH_STATUS ON PATCHLINK_PATCH_STATUS .PATCHUID = PATCHLINK_PATCH.UID GROUP BY KBSYS.PATCHLINK_PATCH.ID ORDER BY VENDOR_RATING, NOTPATCHED
      • Thanks for giving it another go. I tried that, and it ran, but it didn't give me the computer names. I don't really care about what patches they have or don't have, I just want to know how many they are missing. Either that or the date of when patching last ran on the machine.
Please log in to comment
Answer this question or Comment on this question for clarity