Not getting results for custom SQL query in Smart Labels, but get them in MySQL Workbench.
I'm trying to create a custom label that will tell me machines that had an error 8151 while patching. I've written the following query:
SELECT DISTINCT M.NAME AS MACHINE, LAST_INVENTORY
FROM PATCH_MACHINE_STATUS MS
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
WHERE DETECT_ERROR_CODE = 8151;
Getting results back in MySQL workbench, but when adding the SQL to a Smart Label (tried to create as both Device and Patching), I get no results.
I've contacted KACE Support, but have not heard anything back other than the initial tech support call where I demonstrated it wasn't working.
Answers (2)
I read somewhere that in addition to requiring those columns mentioned by ChuckSteel, you also can't use table abbreviations. I made those changes in mine and the label started to apply to machines.
Here is the post that I saw that mentioned, which shows his query before and after making the change to fix.
https://www.itninja.com/question/sql-smart-label-doesn-t-apply-to-any-machine
Comments:
-
Yes, I can confirm that. Table aliases will not work inside of KACE. - RD94 4 years ago
If you create a smart label using the wizard it always selects these columns:
MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID
I always make sure I include them in my smart labels.
But, question, if you go to Reporting › Reports, create a new SQL report, and open it as HTML, Do you see any results? - Channeler 4 years ago