Trying to write a label to match if Flash version is less than 21.0.0.213
Hello all,
This question seems to come up semi regularly here;
I've written a query using a REGEX RLIKE statement, this REGEX statement seems to work in RegexBuddy in that it seems to match Software Version up to 21.0.0.212 but not 213 and higher, exactly what I want.
However, this doesn't seem to work in MySQL or match REGEX when using the webui label builder;
SELECT
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID as TOPIC_ID,
Last_Sync
FROM MACHINE
WHERE
(
(
( exists
(select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and ((SOFTWARE.DISPLAY_NAME like '%Adobe Flash Player%') AND ((SOFTWARE.DISPLAY_NAME like '%ActiveX')))
)
) AND
(MACHINE.NAME like '%LMS%') AND
(
(
( exists
(select 1 from SOFTWARE, MACHINE_SOFTWARE_JT
where
MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_VERSION RLIKE '(^[0-9][.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9]))|(^1[0-9][.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9]))|(^20[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9]))|(^21[.]0[.]0[.]([0-9]|[0-9][0-9]|[0-2][0-1][0-2])$)')
)
)
)
)
)
I have tried replacing [.] with [[.period.]] but that doesn't help.
What am I doing wrong?
Also, will KACE ever build in an easier method to write labels for software versions?
Regards
Nigel
This question seems to come up semi regularly here;
I've written a query using a REGEX RLIKE statement, this REGEX statement seems to work in RegexBuddy in that it seems to match Software Version up to 21.0.0.212 but not 213 and higher, exactly what I want.
However, this doesn't seem to work in MySQL or match REGEX when using the webui label builder;
SELECT
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID as TOPIC_ID,
Last_Sync
FROM MACHINE
WHERE
(
(
( exists
(select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and ((SOFTWARE.DISPLAY_NAME like '%Adobe Flash Player%') AND ((SOFTWARE.DISPLAY_NAME like '%ActiveX')))
)
) AND
(MACHINE.NAME like '%LMS%') AND
(
(
( exists
(select 1 from SOFTWARE, MACHINE_SOFTWARE_JT
where
MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_VERSION RLIKE '(^[0-9][.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9]))|(^1[0-9][.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9]))|(^20[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9])[.]([0-9]|[0-9][0-9]|[0-9][0-9][0-9]))|(^21[.]0[.]0[.]([0-9]|[0-9][0-9]|[0-2][0-1][0-2])$)')
)
)
)
)
)
I have tried replacing [.] with [[.period.]] but that doesn't help.
What am I doing wrong?
Also, will KACE ever build in an easier method to write labels for software versions?
Regards
Nigel
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
chucksteel
8 years ago
I have two approaches to this. The first is to use the INET_ATON function in MySQL. This works most of the time for Flash because the version numbers have four parts and generally each part is less than 255:
SELECT * FROM ORG1.SOFTWARE WHERE DISPLAY_NAME like "Adobe Flash Player%"
and INET_ATON(DISPLAY_VERSION) < INET_ATON("21.0.0.213")
ORDER BY DISPLAY_VERSION
I also created a query that will find the maximum version based on the last part of the version number. This needs to be updated to reflect the major version of Flash Player but it can be useful:
SELECT MAX(concat(SUBSTRING_INDEX(DISPLAY_VERSION, ".", 3), ".", LPAD(SUBSTRING_INDEX(DISPLAY_VERSION, ".", -1), 3, "0"))) FROM SOFTWARE WHERE DISPLAY_NAME like "Adobe Flash Player%" and DISPLAY_VERSION like "21.0%"
You can use this select statement to find computers that have version 21 but not the most up to date subversion.
Comments:
-
Thanks Chuck, how do I turn this into a query suitable for a smart label however ? I tried ;
SELECT
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID as TOPIC_ID,
Last_Sync
FROM MACHINE
WHERE
(
(
( exists
(
select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and ((SOFTWARE.DISPLAY_NAME like '%Adobe Flash Player%') AND ((SOFTWARE.DISPLAY_NAME like '%ActiveX')))
)
)
AND
(MACHINE.NAME like 'WOK-%') AND
(
(
( exists
(
select 1 from SOFTWARE, MACHINE_SOFTWARE_JT
where
MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID AND INET_ATON(SOFTWARE.DISPLAY_VERSION) < INET_ATON("21.0.0.213")
)
)
)
)
)
But Mysql doesn't accept this. - nbs 8 years ago-
My bad I had a missing parenthesis
SELECT
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID as TOPIC_ID,
Last_Sync
FROM MACHINE
WHERE
(
(
( exists
(
select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and ((SOFTWARE.DISPLAY_NAME like '%Adobe Flash Player%') AND ((SOFTWARE.DISPLAY_NAME like '%ActiveX')))
)
)
AND
(MACHINE.NAME like '%LMS%') AND
(
(
( exists
(
select 1 from SOFTWARE, MACHINE_SOFTWARE_JT
where
MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID AND (INET_ATON(SOFTWARE.DISPLAY_VERSION) < INET_ATON("21.0.0.213"))
)
)
)
)
)
)
But the query is returning PCs with Adobe Flash version 21.0.0.213 - nbs 8 years ago
Posted by:
chucksteel
8 years ago
I posted this yesterday but it didn't go through for some reason:
SELECT
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID as TOPIC_ID,
Last_Sync
FROM MACHINE
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
JOIN SOFTWARE on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and
DISPLAY_NAME like "Adobe Flash Player%"
and INET_ATON(DISPLAY_VERSION) < INET_ATON("21.0.0.213")
If you want to limit this to specific machine names like in your example then add:
WHERE MACHINE.NAME like '%LMS%'
to the end of the query.
