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)
Please log in to answer
Posted by:
chucksteel
5 years ago
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 5 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 5 years ago
