FILTER Statement

A filter is an expression that specifies the conditions that events must satisfy to be returned by this query.

The filter criteria can be in form of free text search of the entire body or value of a particular preparsed or parsed column.

Tip: The system (event metadata) columns are indexed so searching is faster on the system columns.

The list of available columns is determined by list of event sources. In case the list of event sources are not available, the system does the best to extract those columns using heuristics algorithms. For queries, the filter should contain a time condition, otherwise the default is used.

Note: When defining column names in a search query, follow the guidelines described in the COLUMNS Statement section.

A filter statement is any expression that evaluates to a result of type Boolean. Any event that does not satisfy this condition is eliminated from the results. An event that satisfies the condition if it returns true when the actual event values are substituted for any variable references.

The following table explains the types of filter statements that can be used.

Types of FILTER statements
Operator Description
AND Narrows your search results by only returning those events where each one of the AND conditions evaluates to true.

For example, use AND to return results containing all specified keywords. When AND is used, the results contain all specified keywords and do not contain entries with just one of the specified keywords.

OR Expands your search results by returning events where either of the OR conditions evaluates to true.

For example, use OR to return results containing any and all specified keywords. OR is ideal when you have common synonyms for a keyword. To narrow results as much as possible, combine OR statements with AND statements.

Full text search Full text search on the body of each event can be performed by simply providing the phrase that needs to be enclosed in double quotes. For example, use system | "authentication failed" to retrieve all events that contain the phrase.

The EQL full text search (specifically on sys_body) is exactly the same as the CONTAINS statement on the sys_body (so "use system | 'Bob'" is exactly the same as "select * from system where sys_body CONTAINS 'Bob'").

Equals (=),

Not equals (<> ), (!=),

Natural equal to (==),

Natural not equal to (!==)

Lower than (<),

Lower or equal (<=),

Greater than (>),

Greater or equal (>=)

A comparison condition compares two expressions using the operator specified in the comparison, which might be one of seven possible comparison operators with well-known meanings, and two more with special meanings. The comparison condition evaluates to true only if the comparison condition is satisfied. This can be used to narrow search results. These operators are case sensitive. For example, col1 > col2/100.

Most of the operators behave as expected in SQL. If one or both values being compared is NULL, the result is NULL, otherwise the comparison is performed. However, the special operators == (natural equal to) and !== (natural not equal to) always return either true or false. If either value being compared is NULL, "natural equal to" returns false. If both values being compared are NULL, "natural equal to" returns true. If neither value being compared is NULL, the comparison is performed as expected. "Natural not equal to" returns the negation of "natural equal to".

The operators == and !== can also be specified in functional form as NaturalEqualTo(val1, val2) and NaturalNotEqualTo(val1, val2).

Note: If the field type is string, the comparison operators less than (<) and greater than (>) compare the data lexicographically (as strings) even if the data is numerical. For example, if the field type is string, ’21’ is considered less than ’3’.
Plus (+),

Minus (-),

Multiply (asterisk (*)),

Divide (forward slash (/),

String concatenation (||)

The arithmetic (+, -, *, /) and string concatenation (||) operators can be used to create parts of other conditions.

For example, "column1 + column2 < 5" or "col3 * 4 - 1000 > col5"

The order of evaluation of the operators in an expression is according to the following precedence rules, from highest to lowest, with the highest precedence implying earlier evaluation:

  • Functions
  • Multiplication and division: both have equal priority and the evaluation order is from left to right
  • Addition and subtraction: both have equal priority and the evaluation order is also from left to right
  • String concatenation
  • Comparators (>, < and so on)

    For example, if you have an expression of the form "col1 > col2 + col3*col4", then col3*col4 is evaluated first, and then the result is added to col2. The col1 is then compared against the final result to see if it is greater.

    You can use a floating point number with the divide operator (/), to obtain a floating point number as the result. For example, <Number> / 1024.0.

Function A set of predefined functions. For details, see Predefined Functions. They can be used in filter, column expressions, or as part of Data Model expressions.
Note: The parameters of the functions can be expressions themselves and is evaluated before the function is called.

For example, "ToInt(col1 + col2)" adds the contents of the columns of the event named col1 and col2, and pass the result to the ToInt function and the result of the function is used.

BETWEEN Narrows your search results by only selecting those events where the left hand side expression evaluates to a value that is between the two right hand side target expressions.

Supports Timestamps, Long, and Integers.

For time range syntax details, see Time Range Expressions.

IN, NOT IN Narrows your search results. This is case sensitive.

Checks whether the value matches any one of the values in a set or not.

For example:
  • "eventID IN ('id1', 'id2', 'id3')"
  • ll_eventID NOT IN ("6279","4749")

Supports all data types. For time range syntax details, see Time Range Expressions.

IS NULL, IS NOT NULL Narrows your search results by accepting or rejecting the event based on whether the evaluated expression is null or not null. An expression most frequently becomes null if a column named in the expression has no value for the current event.

Supports all data types.

LIKE, NOT LIKE Expands your search results. Returns true if it matches the supplied pattern. This is case sensitive. The following rules are used to interpret the supplied string.
  • The character percent (%) is the wildcard character (matches zero or more characters).
  • The character underscore (_) means that it matches exactly one character.
  • The backslash character (\) is used to escape itself and the two characters mentioned earlier, if a literal search for any is desired.
Note: Since string literals in EQL and SQL require backslashes (\) to be escaped, note that additionally escaping for the LIKE statement doubles the escaping requirement. The simple rule to follow is to construct the match string using the rules stated earlier, then simply double up each backslash.

The following examples show the actual syntax (not the escaping needed for Java):

  • col1 LIKE "a_b" - produces a match for "acb", "adb" and so on
  • col1 LIKE "a\\_b" - produces a match for "a_b" but not "acb". Note the double backslashes.
  • col1 LIKE "a\\\\_b" - produces a match for "a\cb" and "a\db"
  • col1 LIKE "a%b" - produces a match for "ab", "acb", "accb" and so on
  • col1 LIKE "a\\%b" - produces a match for "a %b" but not "acb"
CONTAINS, NOT CONTAINS Expands your search results. Returns true when at least a part of the string matches the supplied pattern. This is not case sensitive. The sys_body column is special, because the supplied pattern is used to do a full text search on the event body. For all other columns, the following rules are used to interpret the supplied string.
  • The character asterisk (*) is the wildcard character (matches zero or more characters).
  • The character question mark (?) means that it matches exactly one character.
  • The backslash character (\) is used to escape itself and the two characters mentioned earlier, if a literal search for any is desired.

The CONTAINS statement for columns starting with sys_ uses a full text search.

Note: Since string literals in EQL and SQL require backslashes (\) to be escaped, note that additionally escaping for the CONTAINS statement doubles the escaping requirement. The simple rule to follow is to construct the match string using the rules mentioned earlier, then simply double up each backslash.

The following examples show the actual syntax (not the escaping needed for Java):

  • col1 CONTAINS "a?b" - produces a match for "ccc acb jjj", "adb" and so on
  • col1 CONTAINS "a\\?b" - produces a match for "a?b" but not "acb". Note the double backslashes.
  • col1 CONTAINS "a\\\\?b" - produces a match for "a\cb" and "a\db"
  • col1 CONTAINS "a*b" - produces a match for "ab", "acb", "accb" and so on
  • col1 CONTAINS "a\\*b" - produces a match for "a*b" but not "acb"
REGEXP, NOT REGEXP Narrows your search results. By default, this is case sensitive but can be changed in the regular expression using the embedded flag (?i).

Returns true if it matches the supplied pattern. The pattern syntax uses POSIX syntax. Since string literals in EQL/SQL require backslashes (\) to be escaped, note that all the backslashes inside a regular expression pattern must be doubled up, similar to the LIKE statement.

Examples:

  • col1 REGEXP "[a-z]b" - produces a match for "ab", "cb" but not "Ab" or "_b"
  • col1 REGEXP "\\w*" - produces a match for a word, for example "this" or "that", but not "this and that"
DISTINCT Fetches only the distinct values from data. The DISTINCT statement can be used in all advanced search queries.
If your search query includes a DISTINCT clause and the data includes multiple records with NULL values, all NULL values are included as distinct values in the search result. For example: 
USE TIBCO_Hawk_Agent | COLUMNS DISTINCT ll_collectHostName, ll_sensorName

In this example, ll_collectHostName and ll_sensorName can have NULL values.

As a workaround, add the IS NOT NULL clause for all DISTINCT projections in the query. However, all results with NULL values are excluded from the search result. 

USE TIBCO_Hawk_Agent | COLUMNS DISTINCT ll_collectHostName, ll_sensorName | ll_collectHostName IS NOT NULL AND ll_sensorName IS NOT NULL

Examples

Filter Expression Definition
"Authentication" and sys_eventTime in -1y The result displays all events that contain Authentication from the last one year. 
use sample | 
ll_sourceUser = 'SiteSvrAdmin' | 
sys_eventTime in '2014-02-02'
 The result displays all events that contain column 'll_sourceUser' and value is 'SiteSvrAdmin' on the 2 February 2014.