/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide Comments

Comments

Please log in to comment

Answer this question or Comment on this question for clarity

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

Share