Hello, I'm looking for a recommendation on how to create a KBox smart label that will return all the computers that have a certain software title installed that are less than a certain display version. The GUI permits me to query for each of these values, but does not allow me to join the queries so that the results are exclusive instead of inclusive. So I'm assuming that means I will need to manually edit the SQL, but I'm no SQL guru. This kind of query was easy to do in SMS2003 (which I used prior to KBox) since the GUI allowed you to enclose both query statements in brackets to make them exclusive.

For reference I've attached the SQL statements that were generated from both KBox and SMS2003 to help give a proper idea of what I'm trying to accomplish. Thank you to anyone who can provide helpful suggestions!

***SMS 2003 query that returns correct (exclusive) results***

select * from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where (SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "McAfee Host Intrusion Prevention" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "7.00.0800")

***KBox 1000 query that returns too many (inclusive) results***

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 (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_VERSION < '7.00.0800')) ))
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0

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.
Answered 03/08/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Nevermind....
Answered 03/08/2011 by: dchristian
Red Belt

Please log in to comment
0
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!
Answered 03/09/2011 by: Frugal
Senior Yellow Belt

Please log in to comment
0
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]')
Answered 03/09/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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')
Answered 03/10/2011 by: Frugal
Senior Yellow Belt

Please log in to comment
0
Trust me, there is a need for the Regex. You are getting lucky at the moment because there are no versions like 10.x
Answered 03/10/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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.
Answered 03/11/2011 by: dchristian
Red Belt

Please log in to comment
0
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.
Answered 03/11/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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
Answered 03/11/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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.
Answered 03/11/2011 by: dchristian
Red Belt

Please log in to comment
0
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
Answered 03/14/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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.
Answered 03/14/2011 by: dchristian
Red Belt

Please log in to comment
0
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
Answered 03/15/2011 by: WhitzEnd
Seventh Degree Black Belt

Please log in to comment
0
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.
Answered 03/15/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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!
Answered 03/17/2011 by: Frugal
Senior Yellow Belt

Please log in to comment
0
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.
Answered 03/17/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity