On this one, there’s some good news, and there’s some bad news.
The bad news is that, so far, I have not been able to successfully create a smart label with a REGEX query. Even after talking with support, their answer was mostly directing me to tweak the SQL queries. Since the GUI is completely inadequate for writing anything remotely complex, REGEX would’ve been the next best choice to minimize the verbosity of the query.
At this point, I am resorting to 2 methods: wild cards with the “LIKE” operator (that is the same as using the “contain” operator in the GUI)
To create a query that is a little bit more flexible than a static query, we can do the following to create a smart label for a subnet (in the GUI):
MACHINE.IP contains 192.168.1.%
You can add up to 4 of these statements in the GUI. Unfortunately, to add more, you have to jump to the query itself.
This is where my challenge was initially. You see the internal query wizard seem to add quite a bit of information and complexity to a query that doesn’t really need to be that complex.
Note: before continuing, I want to mention that the following applied in my environment as I have only one Organization setup in my case (which I suspect is most people’s case anyway); so if you have more than one organization, this query may not work as expected.
If you take a Smart Label as is, it will look something like this:
SELECT *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME, UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS FROM ORG1.MACHINE> LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID
AND KUID_ORGANIZATION.ORGANIZATION_ID = 1 WHERE (( (1 in (select 1 from ORG1.MACHINE M2 where M2.ID = MACHINE.ID and M2.IP like '%10.40.55.%%' union select 1 from ORG1.MACHINE_NICS where MACHINE.ID = MACHINE_NICS.ID and MACHINE_NICS.IP like '%10.40.55.%%')) ))
This is quite a convoluted SQL query, and for some reason go back into the KBSYS database, which, as far as I can tell, doesn’t really do anything for the purposes of the smart label. So, the syntax that I used seemed so much simpler, yet produced the same exact results as the complex query. Here’s the same query with the simple syntax:
SELECT *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME, UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS FROM ORG1.MACHINE WHERE (MACHINE.IP LIKE '10.40.55.%')
from here one out, you can add or remove whatever arguments to the query, while being fairly confident of the results.
Posted in: KACE Frequently Asked Questions