/build/static/layout/Breadcrumb_cap_w.png

Using REGEX in Smart Labels to Find Lower Versioned Software (w/Java Example)

Using REGEX in Smart Labels to Find Lower Versioned Software (w/Java Example)

As discussed in multiple posts, the Software Version "number" is not stored as a number by the KBOX, so using "<" in SQL queries does not work.  However, it is possible to work around this by using REGEX.  Hopefully after reading this breakdown and explanation, you will be able to construct your own REGEX statements fairly easily.  Please be aware that this is not the only way to do this and that you will see different REGEX syntax variations, but this is working well in my environment.

_______________________________________________________________________

I researched this a few times here previously and always saw the "use REGEX" recommendation, but didn't see any decent breakdowns of the few examples posted.  For example:

http://www.itninja.com/question/patching-strategy-in-combination-with-managed-installs

http://www.itninja.com/question/computer-smart-label-based-off-software-titles-and-software-version-number-less-than

http://www.itninja.com/question/smart-label-for-software-title-and-version _______________________________________________________________________

This recent blog is great for matching an exact software version:

http://www.itninja.com/blog/view/kace-k1000-how-to-smart-labels-creating-machine-smart-label-by-software-version

However, it does not cover the very useful trick of detecting lower versions.... _______________________________________________________________________

So, I thought I'd post my breakdown and explanation of an example GillySpy provided, as well as a Smart Label that I created to find all versions of Java (sans Updater) below the current 6u31 release.

First a few explanations of syntax used in the REGEX example done by GillySpy:

My paraphrasing of stuff from http://dev.mysql.com/doc/refman/5.1/en/regexp.html

'  starts and ends the regex statement

^  pattern must match the beginning of the value $  pattern must match the end of the value

(...)  begins and ends each pattern to be matched

|  separates each pattern in multi-pattern regex statements

[...]  matches any character within the brackets    

     * span example, “[0-9]” matches any digit from 0 through 9

[[.period.]] suspect this says use "." after the number(s) in pattern, but this is just my guess _______________________________________________________________________

GillySpy's example of a Smart Label to catch any machines with Flash player installs below version 10.3.181.26 (i.e. 10.3.181.25 & below):

Select MACHINE.ID from MACHINE JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID JOIN SOFTWARE S ON SOFTWARE_ID=S.ID Where DISPLAY_NAME LIKE '%flash%' AND DISPLAY_VERSION RLIKE '(^[0-9][[.period.]])|(^10[[.period.]][0-2][[.period.]])|(^10[[.period.]]3[[.period.]]([1-9]|[1-9][0-9]|1[0-7][0-9]|180)[[.period.]])|(^10[[.period.]]3[[.period.]]181[[.period.]]([0-9]|1[0-9]|2[0-5])$)' _______________________________________________________________________

Regex statement to catch anything below 10.3.181.26 (i.e. 10.3.181.25 & below):

'(^[0-9][[.period.]])|(^10[[.period.]][0-2][[.period.]])|(^10[[.period.]]3[[.period.]]([1-9]|[1-9][0-9]|1[0-7][0-9]|180)[[.period.]])|(^10[[.period.]]3[[.period.]]181[[.period.]]([0-9]|1[0-9]|2[0-5])$)' _______________________________________________________________________

Broken down:  

'(^[0-9][[.period.]])|  

     * find anything matching 0 through 9...

(^10[[.period.]][0-2][[.period.]])|  

     * find anything matching 10.0 through 10.2...

(^10[[.period.]]3[[.period.]]          ([1-9]|[1-9][0-9]|1[0-7][0-9]|180)    [[.period.]])|  

     * find anything matching 10.3.1 through 10.3.180...   

     * separated clause actually catches in groups    

          - group 1 -> 1 through 9      * should probably be [0-9]    

          - group 2 -> 10 through 99    

          - group 3 -> 100 through 179    

          - group 4 -> 180

(^10[[.period.]]3[[.period.]]181[[.period.]]          ([0-9]|1[0-9]|2[0-5])$)'   

     * find anything matching 10.3.181.0 through 10.3.181.25   

     * separated clause also catches in groups    

          - group 1 -> 0 through 9    

          - group 2 -> 10 through 19    

          - group 3 -> 20 through 25

_______________________________________________________________________

So building a regex for catching any versions below 6.0.310 (the current version of Java) would be:

'(^[0-5][[.period.]])|(^6[[.period.]]0[[.period.]]([0-2][0-9][0-9]|30[0-9])$)' _______________________________________________________________________

Broken down:

'(^[0-5][[.period.]])|  

     * find anything matching 0 through 5...

(^6[[.period.]]0[[.period.]]          ([0-2][0-9][0-9]|30[0-9])$)'  

     * find anything matching 6.0.0 through 6.0.309   

     * long separated clause catches in groups   

          - group 1 -> 0 through 299    

          - group 2 -> 300 through 309 _______________________________________________________________________

Corresponding smart label (for catching any machines with Java versions below 6.0.310):

Select MACHINE.ID from MACHINE JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID

JOIN SOFTWARE S ON SOFTWARE_ID=S.ID

Where DISPLAY_NAME RLIKE 'java'

AND DISPLAY_VERSION RLIKE '(^[0-5][[.period.]])|(^6[[.period.]]0[[.period.]]([0-2][0-9][0-9]|30[0-9])$)'

Note - Don't use this, it really doesn't work well... _______________________________________________________________________

However, I found that in practice (and an earlier Inventory - Software search) that this would end up missing the Java 2 & 5 installs and catch machines running the Java Updater application, as well as throwing in other things that I didn't want....  So I combined two statements to catch all Java versions without the Java Updater and merged them into a wizard-generated smart label.

The first statement uses:

1) Software Titles contains 'Java(TM)' 2) The regex statement above

The second statement (separated by "OR") uses:

1) A REGEX to match 'Java 2 Runtime Environment' or 'J2SE Runtime Environment 5.0' _______________________________________________________________________

Smart label to catch all pre-6u31 installs of Java ("wizard-built" modified smart label):

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 like '%Java(TM)%'

and SOFTWARE.DISPLAY_VERSION RLIKE '(^[0-5][[.period.]])|(^6[[.period.]]0[[.period.]]([0-2][0-9][0-9]|30[0-9])$)'))) )

OR (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 rlike 'Java 2 Runtime Environment|J2SE Runtime Environment 5.0')) ))

Note - this one works like a charm.  I cross-checked this against an earlier label that I built by hand listing all older Java versions and it's working like a charm. _______________________________________________________________________

For anyone wondering how I modified a "wizard-built" smart label:

1) Go to Inventory - Computers

2) Select the Create Smart Label tab on the right

3) Specify any criteria that won't create a match (i.e. Software Titles contains 'booger')  

     * for my example above, I specified two criteria:   

          Software Titles contains blueberries  *OR*   

          Software Titles matches REGEX blueberry pie|raspberry pie

4) Fill in the Choose label field with the actual name you want to use - I used javapre6u31

5) Click on Create Smart Label to create the smart label

6) Go to the smart label's screen - there's a Smart Label List link you can select after creating it, otherwise

Select Home > Label > Smart Labels > select the label you just created

7) Here's the wizard generated SQL code:

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 like '%bluberries%')) )

OR (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 rlike 'blueberry pie|raspberry pie')) ))

 

8) Replace the first statement with our first statement:

SOFTWARE.DISPLAY_NAME like '%bluberries%'

(SOFTWARE.DISPLAY_NAME like '%Java(TM)%' and SOFTWARE.DISPLAY_VERSION RLIKE '(^[0-5][[.period.]])|(^6[[.period.]]0[[.period.]]([0-2][0-9][0-9]|30[0-9])$)')

 

9) Replace the second statement with our second statement:

SOFTWARE.DISPLAY_NAME rlike 'blueberry pie|raspberry pie'

SOFTWARE.DISPLAY_NAME rlike 'Java 2 Runtime Environment|J2SE Runtime Environment 5.0'

 

10) Hit Save to save the SQL changes to the smart label

11) Force some machines to inventory, I recommend targeting both those that should match and those that don't.

12) Review and tweak as necessary. _______________________________________________________________________

Hope that helps somebody!

John  


Comments

  • In case this would be useful for anyone, here's another example for finding versions of Flash Player below 11.2.202.235 (the current version).

    John

    _____________________________________________________________

    SQL code:

    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 like '%Adobe Flash Player%'
    and SOFTWARE.DISPLAY_VERSION RLIKE '(^[0-9][[.period.]])|(^10[[.period.]])|(^11[[.period.]][0-1][[.period.]])|(^11[[.period.]]2[[.period.]]([0-1][0-9][0-9]|20[0-1])[[.period.]])|(^11[[.period.]]2[[.period.]]202[[.period.]]([0-1][0-9][0-9]|2[0-2][0-9]|23[1-4])$)')) ))

    _____________________________________________________________


    REGEX for catching any versions below 11.2.202.235 would be:

    '(^[0-9][[.period.]])|(^10[[.period.]])|(^11[[.period.]][0-1][[.period.]])|(^11[[.period.]]2[[.period.]]([0-1][0-9][0-9]|20[0-1])[[.period.]])|(^11[[.period.]]2[[.period.]]202[[.period.]]([0-1][0-9][0-9]|2[0-2][0-9]|23[1-4])$)'
    _____________________________________________________________


    Broken down:

    '(^[0-9][[.period.]])|
    * find anything matching 0 through 9...

    (^10[[.period.]])|
    * find anything matching 10...

    (^11[[.period.]][0-1][[.period.]])|
    * find anything matching 11.0 through 11.1...

    (^11[[.period.]]2[[.period.]]([0-1][0-9][0-9]|20[0-1])[[.period.]])|
    * find anything matching 11.2.0 through 11.2.201...
    - group 1 -> 0 through 199
    - group 2 -> 200 through 201

    (^11[[.period.]]2[[.period.]]202[[.period.]]([0-1][0-9][0-9]|2[0-2][0-9]|23[1-4])$)'
    * find anything matching 11.2.202.0 through 11.2.202.234
    - group 1 match -> 0 through 199
    - group 2 match -> 200 through 229
    - group 3 match -> 230 through 234
    _____________________________________________________________ - jverbosk 11 years ago
  • Awesome post! - jknox 11 years ago
  • This looks great but I still see false Positives. Todays latest Flash is 11.5.502.146 so my regex should be:

    '(^[0-10][[.period.]])|(^11[[.period.]][0-4][[.period.]])|(^11[[.period.]]5[[.period.]]([1-9]|[1-9][0-9]|[1-5]00|501)[[.period.]])|(^11[[.period.]]5[[.period.]]502[[.period.]]([0-9]|[1-9][0-9]|1[0-4][0-5])$)'

    Correct?

    When I create a smart label I say Software titles contains Adobe Flash and Software Version number matches Regex and us the regex above.

    I get false positives and I think it is because of my first line match (^[0-10][[.period.]]) one of the computers has another software called snag it installed and its version is 6.0 so that would match right even though he also had the latest Flash already installed.

    It seems to me that there is no way to get accurate smart labels for computers with old software versions. - Jwinsor566 11 years ago
  • Not Sure if this makes sense, But it may work better if to create a super label on saying

    Software titles contains Adobe Flash AND Software Version type = (Latest Version Number)

    Then once you have that label populated you create the second label

    Software Titles contains Adobe Flash AND Label Names != (Whatever you named first label) - Jwinsor566 11 years ago
  • The reason you're getting false positives is because your REGEX statement is not accurate (particularly the last group - [0-9]|[1-9][0-9]|1[0-4][0-5] - lots of holes here). Also, your second approach sounds good in theory, so it's just a matter of testing to see if it works.

    Just for the record, here's a query and the broken-down REGEX statement to catch versions of Adobe Flash Player below 11.5.502.146.

    Hope that helps!

    John
    _____________________________________________________________

    old-flash(pre-11.5.502.146) smart label

    Catches Flash Player versions below 11.5.502.146
    _____________________________________________________________

    Working SQL code:

    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 like '%Adobe Flash Player%'
    and SOFTWARE.DISPLAY_VERSION RLIKE '(^[0-9][[.period.]])|(^10[[.period.]])|(^11[[.period.]][0-4][[.period.]])|(^11[[.period.]]5[[.period.]]([0-4][0-9][0-9]|50[0-1])[[.period.]])|(^11[[.period.]]5[[.period.]]502[[.period.]]([0-9][0-9]|1[0-3][0-9]|14[0-5])$)')) ))

    REGEX for catching any versions below 11.5.502.146 would be:

    '(^[0-9][[.period.]])|(^10[[.period.]])|(^11[[.period.]][0-4][[.period.]])|(^11[[.period.]]5[[.period.]]([0-4][0-9][0-9]|50[0-1])[[.period.]])|(^11[[.period.]]5[[.period.]]502[[.period.]]([0-9][0-9]|1[0-3][0-9]|14[0-5])$)'

    Broken down:

    '(^[0-9][[.period.]])|
    * find anything matching 0 through 9...

    (^10[[.period.]])|
    * find anything matching 10...

    (^11[[.period.]][0-4][[.period.]])|
    * find anything matching 11.0 through 11.4...

    (^11[[.period.]]5[[.period.]]([0-4][0-9][0-9]|50[0-1])[[.period.]])|
    * find anything matching 11.5.0 through 11.5.501...
    - group 1 -> 0 through 499
    - group 2 -> 500 through 501

    (^11[[.period.]]5[[.period.]]502[[.period.]]([0-9][0-9]|1[0-3][0-9]|14[0-5])$)'
    * find anything matching 11.5.502.0 through 11.5.502.145
    - group 1 match -> 0 through 99
    - group 2 match -> 100 through 139
    - group 3 match -> 140 through 145 - jverbosk 11 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ