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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
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.

Answered 04/13/2016 by: chucksteel
Red Belt

  • 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.
    • 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
Please log in to comment

Answers

0
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.


Answered 04/15/2016 by: chucksteel
Red Belt

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

Share