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
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
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.