"SELECT command denied to user R1@localhost"
Most of my patch labels and many reports have been broken since our last K1000 upgrade. I'm trying to fix them but for many when I run the sql in MySQL Workbench, I get the error:
"SELECT command denied to user R1@localhost".
I'm able to run other queries from some of my older reports, but for many I get this error.
For example, this query which use to dump a list of all computers and the missing patches now gives the Select error:
SELECT
M.NAME AS ComputerName,
IP,
OS_NAME,
M.USER_LOGGED AS USER_LOGGED,
PP.VENDOR,
PP.TITLE AS DISPLAY_NAME,
PP.IMPACTID,
PP.SEVERITY,
MS.STATUS,
PP.REBOOT,
PP.IS_SUPERCEDED,
PP.RELEASEDATE,
PP.IS_APP,
PP.HYPERLINK,
PP.TYPE
FROM
PATCHLINK_MACHINE_STATUS MS
JOIN
KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN
MACHINE M ON M.ID = MS.MACHINE_ID
WHERE
MS.STATUS != 'PATCHED'
AND PP.IS_SUPERCEDED = '0'
AND PP.IMPACTID = 'Critical'
ORDER BY PP.TITLE
Any ideas why I can 't run this in MySqlWorkbench?
Answers (1)
what version of the SMA?
If you are on 10.x, you may want to review this KB.
https://support.quest.com/kb/309572/updating-custom-sql-queries-for-10-0
Comments:
-
Thanks. That is helpful. - kpm8 4 years ago
-
This should get you started. Some entries are commented out that will require replacement SQL.
SELECT
M.NAME AS ComputerName,
IP,
OS_NAME,
M.USER_LOGGED AS USER_LOGGED,
P.PUBLISHER,
P.TITLE AS DISPLAY_NAME,
-- P.IMPACTID, --
P.SEVERITY,
MS.DETECT_STATUS,
-- PP.REBOOT, --
P.IS_SUPERCEDED,
-- P.RELEASEDATE, --
-- PP.IS_APP, --
P.INFO_URL,
P.PATCH_TYPE
FROM
PATCH_MACHINE_STATUS MS
JOIN
KBSYS.PATCH P ON P.ID = MS.PATCH_ID
JOIN
MACHINE M ON M.ID = MS.MACHINE_ID
WHERE
MS.DETECT_STATUS != 'PATCHED'
AND P.IS_SUPERCEDED = '0'
AND P.SEVERITY = 'Critical'
ORDER BY P.TITLE - KevinG 4 years ago-
Thank you! Again, very helpful! - kpm8 4 years ago
-
How can I limit this to a single smart label or multiple smart labels? - poisedforflight 4 years ago
