KACE Product Support : Creating a report to show machines not being patched

Creating a report to show machines not being patched

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

Answers (2)

Posted by: mramsdell 9 years ago

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

Comments:

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") - cdurward 9 years ago
- What version of the K1000 do you have? - mramsdell 9 years ago
  - 6.0.101863 - cdurward 9 years ago
- 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 - mramsdell 9 years ago
  - 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") - cdurward 9 years ago

Posted by: mramsdell 9 years ago

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

Comments:

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 ") - cdurward 9 years ago
- 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 - bnerison 9 years ago
  - 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. - cdurward 9 years ago

Answer this question

Posted by:

View more:

KACE Product Support Questions

question