Smart label filters <,> do not work with Software Version (4286255)

Smart label filters <,> do not work with Software Version

When creating a smart label or using advanced search, SQL operators "<,>" (lesser than or greater than) do not work.

This happens because the Software Version field is kept as a text rather than a numeric value.

NOTE: Regex query and example is provided as a courtesy by Quest KACE to its customers. We make no promises as to workability or functionality under certain circumstances. In default situations under normal setup these work as designed. Quest KACE does not support modification nor usage.

Quest KACE highly recommend testing all queries no matter the source before using in a production environment.

 

It is possible to work around this by using REGEX (see below for a example)

 

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

 

'  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

 

 

Machine Smart label example:

 

Select

  MACHINE.ID

From

  MACHINE Join

  MACHINE_SOFTWARE_JT MS On MS.MACHINE_ID = MACHINE.ID Join

  SOFTWARE S On MS.SOFTWARE_ID = S.ID

Where

  S.DISPLAY_NAME Like '%flash%' And

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

 

__________________________________________

 

 

Creating or modifying a SQL/Regex query, report, script or ticket rule is considered a "Customization" and is not a KACE support feature.  We do, however, offer "KACE Professional Services" where they can create the Regex query for you based on your needs.  If you would like to talk to KACE Professional Services team, please contact them using the link below.
 

KACE Consulting & Solutions Architecture (quest.com)
 
Before any work is done, they will speak with you and build a statement of work; as well as provide you with a quote.

For more information on regex expressions see : http://dev.mysql.com/doc/refman/5.1/en/regexp.html Article created by jverbosk. Note: Writing regex expressions is not supported by KACE support.