smart label for software title and version
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')) ))
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
Answers (16)
Please log in to answer
Posted by:
GillySpy
13 years ago
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.
Posted by:
Frugal
13 years ago
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!
Posted by:
GillySpy
13 years ago
There is a problem in the logic of the query as well.
In your WHERE clause you are saying
So you want something like filters both at the same time like this:
In your WHERE clause you are saying
- where machines have software that matches 'McAfee Host Intrusion Prevention'
- and machines have software that matches the version less than 7
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]')
Posted by:
Frugal
13 years ago
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')
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')
Posted by:
GillySpy
13 years ago
Posted by:
dchristian
13 years ago
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?
When i add it as a label, it returns ALL records that have flash, ignoring my version.
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.
Posted by:
GillySpy
13 years ago
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.
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.
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.
Posted by:
GillySpy
13 years ago
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:
it would still take some work to compare it -- which is why I like still like regex
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
Posted by:
dchristian
13 years ago
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.
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.
Posted by:
GillySpy
13 years ago
Posted by:
dchristian
13 years ago
Posted by:
WhitzEnd
13 years ago
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
Posted by:
GillySpy
13 years ago
Posted by:
Frugal
13 years ago
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!
^(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!
Posted by:
GillySpy
13 years ago
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
so that the conversation will remain readable.