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)
Please log in to answer
Posted by:
chucksteel
10 years ago
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 10 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 10 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 7 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 2 years ago
