Why is my custom SQL Smart Label not working properly to inventory devices?
Hi all,
Long-time viewer, first-time poster here. I am by no means a SQL expert. Quite the contrary actually. However, I have a need to create a SQL query since KACE doesn't seem to handle Smart Labels with versions properly. That is, it doesn't query the version based on the software title but instead simply grabs any version it sees on a device for any software. Therefore, I have created a custom query to inventory devices if our VPN client is not installed or if it is not the latest version. This query works perfectly in MySQL Workbench and DataGrid but when I actually add the code to the "Edit SQL" within KACE, it seems to grab every single device in our environment. Here is the code:
select MACHINE.NAME as SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID from ORG1.MACHINE
where(exists(select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and LABEL.TYPE <> 'hidden' and LABEL.NAME = 'All Firm Mobile Devices'))
and(exists(select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and LABEL.TYPE <> 'hidden' and LABEL.NAME = 'Bench Machines'))
and(MACHINE.IP like '172.18%')
and(not exists(select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME = 'Cisco AnyConnect Secure Mobility Client'))
or(exists(select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and LABEL.TYPE <> 'hidden' and LABEL.NAME = 'All Firm Mobile Devices'))
and(exists(select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and LABEL.TYPE <> 'hidden' and LABEL.NAME = 'Bench Machines'))
and(MACHINE.IP like '172.18%')
and(exists(select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME = 'Cisco AnyConnect Secure Mobility Client' and SOFTWARE.DISPLAY_VERSION != '4.8.03036'))
Any help would be greatly appreciated since I simply can't seem to understand why this would work properly in SQL but not in KACE. I learned something a while ago (also posted on IT Ninja) about not using aliases for MACHINE since it doesn't work in KACE, but that's not the case here.
-
Are any of the other labels in your query smart labels? If so, then you need to make sure this one will be evaluated after the others. - chucksteel 4 years ago
-
Yes, there are. However, I have already made sure that the 'All Firm Mobile Devices' and 'Bench Devices' label are set to priority 99 whereas this one is set to 100. - LovelyFrenchToast 4 years ago
Answers (2)
Try removing all the "ORG1." from your SQL and run again. Also try this in an SQL report to see what values you get, as this will provide quicker feedback than a smart label
Comments:
-
I did remove the ORG1 from a couple of these queries but it doesn't seem to have made any impact. The report shows the correct amount of devices. However, I don't want to use a report in this case since I am scoping a distribution job to this group. Essentially, I am having the VPN software deployed if devices are detected on the LAN but without the VPN software or without the latest version of the software. - LovelyFrenchToast 4 years ago
Try simplifying the query:
select MACHINE.NAME as SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID
from ORG1.MACHINE
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
JOIN SOFTWARE on SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME = 'Cisco AnyConnect Secure Mobility Client'
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
WHERE SOFTWARE.DISPLAY_VERSION != '4.8.03036'
AND MACHINE.IP like '172.18%'
AND (LABEL.NAME = 'All Firm Mobile Devices' or LABEL.NAME = 'Bench Machines')
