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