/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Not getting results for custom SQL query in Smart Labels, but get them in MySQL Workbench.

12/02/2019 331 views

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

Comments

  • hi, please specify your KACE SMA version, the patching module and tables, changed a lot in version 10.0
    • Latest, 10.0.290, but if I can run the query in MySQL Workbench against my KACE DB, then it shouldn't matter.
  • 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?
    • Yes, it does. Sorry, forgot to include that detail.

All Answers

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



Answered 01/21/2020 by: ajones88
White Belt

  • Yes, I can confirm that. Table aliases will not work inside of KACE.
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. 

Answered 12/03/2019 by: chucksteel
Red Belt

  • Thanks. I'm out of the office today, but will try that when I get a chance.
  • This content is currently hidden from public view.
    Reason: Removed by member request For more information, visit our FAQ's.
  • Ah, I see. Thanks!
  • This content is currently hidden from public view.
    Reason: Removed by member request For more information, visit our FAQ's.

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