/build/static/layout/Breadcrumb_cap_w.png

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.


2 Comments   [ + ] Show comments
  • hi, please specify your KACE SMA version, the patching module and tables, changed a lot in version 10.0 - Channeler 1 year ago
    • Latest, 10.0.290, but if I can run the query in MySQL Workbench against my KACE DB, then it shouldn't matter. - RD94 1 year ago
  • I don't have devices with that error to test,

    But, question, if you go to Reporting › Reports, create a new SQL report, and open it as HTML, Do you see any results? - Channeler 1 year ago
    • Yes, it does. Sorry, forgot to include that detail. - RD94 1 year ago

Answers (2)

Posted by: ajones88 1 year ago
White Belt
0

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 1 year ago
Posted by: chucksteel 1 year ago
Red Belt
0

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. 


Comments:
  • Thanks. I'm out of the office today, but will try that when I get a chance. - RD94 1 year ago
  • Ah, I see. Thanks! - RD94 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