Reworked script
I have an old patching report that has broken with the update to ver. 10 - want to check which systems have a specific patch installed. This is the current one:
SELECT MACHINE_ID, PMS.PATCHUID, PMS.STATUS, PMS.STATUS_DT, P.TITLE, P.IMPACTID, M.NAME
FROM ORG1.PATCHLINK_MACHINE_STATUS PMS
JOIN KBSYS.PATCHLINK_PATCH P on P.UID = PMS.PATCHUID
JOIN MACHINE M on M.ID = PMS.MACHINE_ID
WHERE PMS.STATUS = "NOTPATCHED"
and P.TITLE LIKE "%KB2565057%"
Right now this comes back with a mysql error:
mysqli error: [1142: SELECT command denied to user 'R1'@'localhost' for table 'PATCHLINK_PATCH'] in EXECUTE(\n"SELECT MACHINE_ID, PMS.PATCHUID, PMS.STATUS, PMS.STATUS_DT, P.TITLE, P.IMPACTID, M.NAME\nFROM ORG1.PATCHLINK_MACHINE_STATUS PMS\nJOIN KBSYS.PATCHLINK_PATCH P on P.UID = PMS.PATCHUID \nJOIN MACHINE M on M.ID = PMS.MACHINE_ID\nWHERE PMS.STATUS = "NOTPATCHED"\nand P.TITLE LIKE "%KB2565057%" LIMIT 0")\n
Answers (1)
Top Answer
Here is a KB that lists changes that were made to accommodate the new patching module in 10.x.
https://support.quest.com/kb/309572/updating-custom-sql-queries-for-10-0
This is a modification to your SQL you posted. I hope this helps you in understanding the required changes.
SELECT MACHINE_ID, PMS.PATCH_ID, PMS.DETECT_STATUS, PMS.STATUS_DT, P.TITLE, M.NAME
FROM ORG1.PATCH_MACHINE_STATUS PMS
JOIN KBSYS.PATCH P on P.ID = PMS.PATCH_ID
JOIN MACHINE M on M.ID = PMS.MACHINE_ID
WHERE PMS.DETECT_STATUS = "NOTPATCHED"
and P.TITLE LIKE "%KB2565057%"
Comments:
-
Thanks, the link is very helpful - appreciate your assistance. - tstrub2020 4 years ago
