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.
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.
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.
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,
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:
|
Function | A set of predefined functions. For details, see
Predefined EQL 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,
|
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:
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.
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):
|
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 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):
|
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:
|
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,
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. |