/build/static/layout/Breadcrumb_cap_w.png

Does anyone have a report that shows the last date a machine was patched?

I have tried using the wizard and deploy date is there but it applies to patches not machines. What I am looking for is something that shows each machine and the last date patches were applied to it. I am pretty good at modifying reports so if you have something close I can probably make it work but I am still new to writing them outside the wizard and it just does not give the options I need for this. 

0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 9 years ago
Red Belt
0
This report shows the patching status date:
SELECT PATCHLINK_MACHINE_STATUS.MACHINE_ID, NAME, date(MAX(DEPLOY_STATUS_DT)) AS LAST_PATCH_INSTALL, date(LAST_RUN) as LAST_RUN_DATE,
IP, CLIENT_VERSION
FROM ORG1.PATCHLINK_MACHINE_STATUS 
JOIN MACHINE on MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID
JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS on PATCHLINK_SCHEDULE_MACHINE_STATUS.MACHINE_ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID and PATCHLINK_SCHEDULE_MACHINE_STATUS.PATCHLINK_SCHEDULE_ID = 4
GROUP BY PATCHLINK_MACHINE_STATUS.MACHINE_ID
ORDER BY LAST_PATCH_INSTALL ASC, LAST_RUN_DATE ASC;

This report only looks at a specific patching schedule so be sure to change the PATCHLINK_SCHEDULE_MACHINE_STATUS.PATCHLINK_SCHEDULE_ID = 4 line to match the ID for your patching schedule.


Comments:
  • Thanks for the answer but not quite what I was looking for. I need something that looks at every server whether it is in a patch schedule or not. I might be able to modify this one to work though. - ttucker 9 years ago
    • I'm not sure if KACE can determine when updates were last run if KACE isn't applying the updates. This post on stackoverflow.com shows the registry keys where you can find when Windows Update ran:
      http://stackoverflow.com/questions/9215326/check-when-last-check-for-windows-updates-was-performed

      You could use that information to create a custom inventory rule to pull in the LastSuccessTime for installed updates. The problem is that KACE updates won't appear here so if you have some machines being patched by KACE and others being patched manually you'll need to have two separate reports. - chucksteel 9 years ago
  • I'd been looking for something similar and came across this post, but wanted to use it as a label for machines that had not been patched in X number of days - 30 in this case. I figured I'd post what I ended up with in case it helps anyone else (as above, change schedule ID to match):

    SELECT
    *
    FROM
    (SELECT
    PATCHLINK_MACHINE_STATUS.SCHEDULE_ID AS SCHEDULE_ID,
    PATCHLINK_MACHINE_STATUS.MACHINE_ID,
    NAME,
    DATE(MAX(DEPLOY_STATUS_DT)) AS LAST_PATCH_INSTALL,
    DATE(LAST_RUN) AS LAST_RUN_DATE,
    IP,
    CLIENT_VERSION
    FROM
    ORG9.PATCHLINK_MACHINE_STATUS
    JOIN MACHINE ON MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID
    JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS ON PATCHLINK_SCHEDULE_MACHINE_STATUS.MACHINE_ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID
    AND PATCHLINK_SCHEDULE_MACHINE_STATUS.PATCHLINK_SCHEDULE_ID = 37
    GROUP BY PATCHLINK_MACHINE_STATUS.MACHINE_ID) AS temp_table
    WHERE
    (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(LAST_PATCH_INSTALL)) > 2592000
    AND SCHEDULE_ID = 37 - jones948 6 years ago
    • Slightly different (I use this for label generation only):

      SELECT *
      FROM (
      SELECT
      MACHINE.ID,
      MAX(PSMS.LAST_RUN) AS LAST_RUN
      FROM MACHINE
      INNER JOIN PATCH_SCHEDULE_MACHINE_STATUS AS PSMS
      ON MACHINE.ID = PSMS.MACHINE_ID
      INNER JOIN PATCH_SCHEDULE AS PS
      ON PSMS.PATCH_SCHEDULE_ID = PS.ID
      WHERE PS.PATCH_ACTION = 3
      AND OS_NAME NOT LIKE '%SERVER%'
      AND OS_NAME LIKE '%WINDOWS%'
      GROUP BY MACHINE.ID) AS PSTEMP
      WHERE LAST_RUN <= NOW() - INTERVAL 30 DAY - BNewland 1 year 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

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