/build/static/layout/Breadcrumb_cap_w.png

Custom smart label query not matching results in KACE

Hi, i've created a label of users, eligible to receive a software. After that, i've created a smart label with the following custom query

SELECT MACHINE.NAME AS SYSTEM_NAME,SYSTEM_DESCRIPTION,MACHINE.IP, MACHINE.MAC,MACHINE.ID AS TOPIC_ID
FROM MACHINE
JOIN ASSET on ASSET.MAPPED_ID = MACHINE.ID
JOIN USER OWNER on OWNER.ID = ASSET.OWNER_ID
JOIN USER_LABEL_JT on OWNER.ID = USER_LABEL_JT.USER_ID
WHERE USER_LABEL_JT.LABEL_ID = 306
that is working in mysqlworkbench, returning the correct results, but it's not working in the smart label, also after triggering manually the inventory in each machine that should be involved. what am i doing wrong? i don't see the possibility to do this query from the wizard. BTW, this is not the only query that it's not working. are there any limits on the kace app engine side for the DB queries?

0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 4 years ago
Red Belt
1

A couple of things:

When joining to the asset table, be sure to specify the asset_type_id, just in case.

Your join to the USER_LABEL_JT has OWNER.ID first, I'm not sure if that's an issue but it is different from the standard I normally follow.

Here is the query that I posted to the KACE Slack yesterday that works:

SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM ORG1.MACHINE
JOIN ASSET on ASSET.MAPPED_ID = MACHINE.ID and ASSET.ASSET_TYPE_ID = 5
JOIN USER on USER.ID = ASSET.OWNER_ID
JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = USER.ID
JOIN LABEL on LABEL.ID = USER_LABEL_JT.LABEL_ID
WHERE LABEL.NAME = "Special People"

If you are using ORGs, then be sure to change the ORG1.MACHINE to the correct org number. 


I don't think there are any limits on the number of smart labels.


Comments:
  • Thank you so much. I guess that in the where condition i can use the Label ID instead of the its name, correct? - matteo.durini@appway.com 4 years ago
    • Yes, you could use LABEL.ID = 306

      I prefer to use the name to make it easier for others to understand what the smart label is doing without having to lookup the ID. - chucksteel 4 years 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