KACE Product Support


SOFTWARE.DISPLAY_VERSION < '7.00.0800'
That part is a problem since this field is a string then 19 is less than 7 because 1 is the first character in the alpha sort.

So you need a regex. This should do it:

SOFTWARE.DISPLAY_VERSION RLIKE '^[0-6]'

You could also cast it into a numeric value first but to do that you would have to take out the periods (e.g substring everything before the first period) so it doesn't work for most version comparisons.

Nevermind....

I modified the SQL statement at the very end to replace SOFTWARE.DISPLAY_VERSION < '7.00.0800' with the suggestion SOFTWARE.DISPLAY_VERSION RLIKE '^[0-6]' but I still get too many results, and they include machines that have version 7.00.0800 installed and machines that have an older version installed. Note that all computers that have the software program installed will be version 7, it is the last four digits in the display version that will differ based on which patch they have installed. Maybe using REGEX is the key to getting the results I want, so I will keep searching for a solution and post it once I find it. And if anyone else has any suggestions, I will give it a try and post the results of that also. Thanks!

There is a problem in the logic of the query as well.

In your WHERE clause you are saying

1. where machines have software that matches 'McAfee Host Intrusion Prevention'
2. and machines have software that matches the version less than 7
   

But, on the same machine if I have Application 1.0 installed and 'McAfee Host Intrusion Prevention 8' installed then that will match the query becauase Application 1.0 meets the first criteria and 'McAfee Host Intrusion Prevention' matches the second.

So you want something like filters both at the same time 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 ONKUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ONSMMP_CONNECTION.KUID = MACHINE.KUID ANDKUID_ORGANIZATION.ORGANIZATION_ID = 1
where 1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT whereMACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID andMACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME= 'McAfee Host Intrusion Prevention' and SOFTWARE.DISPLAY_VERSION RLIKE '^[0-6]')

This was the SQL code I needed to make it work, that is make both query statements exclusive instead of inclusive. There was no need to use REGEX for the software version number. This SQL statement returns results for McAfee Host Intrusion Prevent 7.00.0070, 7.00.0601, and 7.00.0005; but not for 7.00.0800 and not for machines that do not have the product installed. It does exactly what I want it to do. I would definitely recommend that the KBox team consider adding the option in the smart label wizard GUI to set multiple query statements exclusive to one another to improve its versatility.

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.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME = 'McAfee Host Intrusion Prevention' and SOFTWARE.DISPLAY_VERSION < '7.00.0800')

Trust me, there is a need for the Regex. You are getting lucky at the moment because there are no versions like 10.x

hey Gerald,

I've been playing around with what you said about parsing out the periods.

You can't just do a blanket remove because that would make some versions considerably larger then others.

So... I came up with this, which will remove all the periods except for the first one.

So if you were looking for anything less then version 10.1.2.3 the where would be version < 10.123

It seems to work in the query browser, but i can't convert it to a label.

Any ideas?
SELECT MACHINE.*,
UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
FROM MACHINE,
(
SELECT S.ID,
S.DISPLAY_NAME,
S.DISPLAY_VERSION,
ML.MACHINE_ID,
CONCAT(LEFT(DISPLAY_VERSION,INSTR(DISPLAY_VERSION,'.')),REPLACE(RIGHT(DISPLAY_VERSION,LENGTH(DISPLAY_VERSION)-INSTR(DISPLAY_VERSION,'.')),'.',')) AS VERSION
FROM SOFTWARE S,
MACHINE_SOFTWARE_JT ML
WHERE S.ID = ML.SOFTWARE_ID
)SOFTWARE
WHERE MACHINE.ID = SOFTWARE.MACHINE_ID
AND SOFTWARE.DISPLAY_NAME LIKE '%ADOBE%FLASH%X%'
AND SOFTWARE.VERSION < 10.215232
ORDER BY SOFTWARE.DISPLAY_NAME,
SOFTWARE.VERSION

When i add it as a label, it returns ALL records that have flash, ignoring my version.

There are still assumptions going on here. It's going to work most of the time but you wouldn't want to get burned by a minor inconvenience.

I would just always write a regex since we're comparing strings not numbers. OR you could break down the string into groups of numbers and compare each group.
select CAST(SUBSTRING_INDEX(VER,'.',1) as SIGNED) PART_1,
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(MID(VER,LOCATE('.',VER)+1,LENGTH(VER)),'.',2) ,'.',1) AS SIGNED) PART_2,
CAST(SUBSTRING_INDEX(VER,'.',-1) as SIGNED) PART_3
FROM (select '7.00.0800' VER ) T


If your version had 4 part then you could have to construct the 3rd part by expanding on the conventions used for the second part.

Had to type quick as I was losing power there, but here's some more info i wanted to share:

It's a similar problem to IP addresses -- I compare them with a regex. Althought MySQL does have a couple of convenience functions built in to make IPs specifically easier to handle.

Here's an example for IP's or any 4-part version number:

SELECT
`ip` ,
SUBSTRING_INDEX( `ip` , '.', 1 ) AS a,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', 2 ),'.',-1) AS b,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', -2 ),'.',1) AS c,
SUBSTRING_INDEX( `ip` , '.', -1 ) AS d
FROM (select '192.168.1.10' ip) T

it would still take some work to compare it -- which is why I like still like regex

Nice, I get were your going with that.

Any ideas on why it works in the query browser but not as a label?

Running my query in the query browser returns 10 rows.

Adding it as a label return everyone... (yes I'm making them check in)

I know regex is the way to go, but its kinda weird the same query gives me two different results depending on where its run.

That should be the same. only things I can suggest is:

1. make sure you are connecting to the same org in both tests
2. paste the query for your filter here
3. open a ticket
   

Just wanted to give an update.

I opened a case with support.

They realized that the order by in the select statement was causing trouble for the smart label.

For whatever reason the order by returns all results as true... Interesting to say the least.

ORIGINAL: dchristian

hey Gerald,

I've been playing around with what you said about parsing out the periods.

You can't just do a blanket remove because that would make some versions considerably larger then others.

So... I came up with this, which will remove all the periods except for the first one.

So if you were looking for anything less then version 10.1.2.3 the where would be version < 10.123

It seems to work in the query browser, but i can't convert it to a label.

Any ideas?
SELECT MACHINE.*,
UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
FROM MACHINE,
(
SELECT S.ID,
S.DISPLAY_NAME,
S.DISPLAY_VERSION,
ML.MACHINE_ID,
CONCAT(LEFT(DISPLAY_VERSION,INSTR(DISPLAY_VERSION,'.')),REPLACE(RIGHT(DISPLAY_VERSION,LENGTH(DISPLAY_VERSION)-INSTR(DISPLAY_VERSION,'.')),'.',')) AS VERSION
FROM SOFTWARE S,
MACHINE_SOFTWARE_JT ML
WHERE S.ID = ML.SOFTWARE_ID
)SOFTWARE
WHERE MACHINE.ID = SOFTWARE.MACHINE_ID
AND SOFTWARE.DISPLAY_NAME LIKE '%ADOBE%FLASH%X%'
AND SOFTWARE.VERSION < 10.215232
ORDER BY SOFTWARE.DISPLAY_NAME,
SOFTWARE.VERSION

When i add it as a label, it returns ALL records that have flash, ignoring my version.



I just wanted to throw my feedback in so that all can see.

The ORDER BY SOFTWARE.DISPLAY_NAME,
SOFTWARE.VERSION statement is not needed in a smart label. This is what is causing the smart label to return all machines. You may still run into issues in the future, as Gerald said, with using the "<" in the statment. If trying to filter out all versions that arent the most current then i would use. Rather than SOFTWARE.VERSION < 10.215232, I would use SOFTWARE.VERSION != '10.215232' or whatever the most current version is.

This query will also return machine results in a smart label based on software name and version

Select
SOFTWARE.DISPLAY_NAME,
SOFTWARE.DISPLAY_VERSION,
MACHINE.NAME
From
SOFTWARE Inner Join
MACHINE_SOFTWARE_JT On SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
Inner Join
MACHINE On MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
Where
SOFTWARE.DISPLAY_NAME = 'SOFTWARENAMEHERE' And
SOFTWARE.DISPLAY_VERSION != 'VERSIONHERE'

Glad we were able to get it fixed up for you David.

Regards,

Brandon Whitman

i didn't notice the order by -- you cannot have anything after the WHERE clause in a filter so that makes sense. Another thing is that you should not rename (alias) any tables and your query should return one row per item -- per machine in this case.

Converting <7.00.0800 to regex is easier said than done for someone who has never worked with regex before. But after studying the concept for the past couple days, this is what I have come up with:

^(7\.00\.0[0-7][0-9][0-9])$|^([0-6]\.[0-9][0-9]\.[0-9][0-9][0-9][0-9])$

And this seems to be working with the KBOX label that uses the SQL statement I posted earlier. At first I tried enclosing the groups with \b\b and then \A\Z, but these would not return results in the KBOX label. If anyone feels I'm overlooking something in my regex statement, feel free to comment. Thanks!

looks good ...to simplify it and write it in MYSQL's preferred format (called POSIX):

'^((7[[...]]00[[...]]0[0-7])|([0-6][[...]]))'

Hopefully seeing that equivalent will also help your learning of regex. If others have regex questions related to KBOX feel free to post.