/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Why is my custom SQL Smart Label not working properly to inventory devices?

04/06/2020 120 views

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.

1 Comment   [ + ] Show comment

Comments

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

All Answers

0

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

Answered 04/07/2020 by: Hobbsy
Red Belt

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

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')


Answered 04/07/2020 by: chucksteel
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

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