_checkif

_checkif is a query directive used to apply conditional logic on a result set.

A quick example is given below:

_fetch * from event where $Stream=FIREWALL group count_unique $SrcCN limit 2
>>_checkif int_compare count_unique <= 30000 include

Here:
The _fetch query directive retrieves all fields for each event where SrcCN along with a count (count_unique) for each group. The result set is sorted in the descending order of count_unique (by default). It is then limited to 2 rows. The output is as shown below:

In the pipelined query, the _checkif directive uses the keyword int_compare to check if the count (count_unique) of any group is less than or equal to 30000. Only rows where this condition is satisfied are included in the final result set. The output is as shown below:

The _checkif directive allows you to:

• Compare two strings, integers, times and timestamps (date and time) and filter the result set based on the comparison
• Filter rows in which no value exists for a specified field.
• Compare the result set of a _fetch query function and an existing event store.

SYNTAX

The generic syntax of the _checkif directive is as shown below:

_checkif [function] [include | exclude]
function:
int_compare | str_compare | lookup
int_compare $field1 > | < | = | != | >= | <= integer
str_compare $field1 [ [ eq | neq | substr ] 'string' | regex ‘regular expression’ ]
key_exists $field1
lookup eventstore_name join $field1 = $field2 [int_compare | str_compare | key_exists ] [include | exclude]

Here:
int_compare: Compares two integer values.
str_compare: Compares two string values (or a string against a regular expression).
key_exists: Checks and filters rows where no value exists for the specified field.
lookup: Compares the result set of a _fetch query function and an eventstore based on specified fields (keyword join) and the condition specified using the above functions.
include/exclude: Includes or excludes rows, from the result set, which satisfy the condition.

int_compare

Used to compare integer values in a result set

The _checkif directive can be used to compare integer values in a result set with a specified integer value using the int_compare keyword.

Take a look at the example given below:

_fetch * from event where $Stream=FIREWALL group count_unique $SrcIP limit 10
>>_checkif int_compare count_unique > 3000 include

Here:

1. The _fetch directive retrieves all fields for each event where stream is Firewall. The  result set is grouped by unique values of  SrcIP along with a distinct count (count_unique) for each group. The result set is sorted in the descending order of count_unique (by default). It is then limited to 10 rows. The output is as shown below:

1. In the pipelined query function, the _checkif directive uses the int_compare keyword to compare two integer values. It checks whether the value in the count_unique field is less than 3000 using the < symbol/keyword. The keyword include is used to include in the result set all the rows where this condition is satisfied. The output is as shown below:

As shown, only those rows where count_unique is less than 3000 are included in the result set of the _checkif function (final result set).

The _checkif directive can be used to compare string values in a result set with a specified string (or regular expression) using the str_compare keyword along with eq/neq/substr/regex keywords.

eq/neq

Result set are equal to or not equal to a specified string

The eq/neq keywords are used along with the str_compare keyword to check if the string values in a result set are equal to or not equal to a specified string. These keywords perform a full and exact match.

Take a look at the example given below:

_fetch * from event where $Duration=6h AND $Stream=FIREWALL group count_unique $SrcIP limit 10
>>_checkif str_compare $SrcIP eq '180.XX.XX.126' include

Here:
The _fetch directive retrieves all fields for each event that have been received (stored) in the last 6 hours (Stream** as FIREWALL. The result set is grouped by unique values of $SrcIP(source IP) along with with a count (count_unique) for each group. The result set is sorted in the descending order of count_unique (by default). It is then limited to 10 rows. The output is as shown below:

In the pipelined query function, the _checkif directive uses the keyword str_compare to compare two string values. It checks whether the value in the $SrcIP field is equal to 180.XX.XX.126 using the eq keyword. The keyword include is used to include in the result set the rows where this condition is satisfied. The output is as shown below:

Result set contain the specified string.

The substr keyword is used along with the str_compare keyword to check if the string values in a result set contain the specified string. Hence, this keyword checks if the specified string is substring of the string in the result set.
Take a look at the example given below:

_fetch * from event where $Duration=6h AND $Stream=FIREWALL limit 10
>>_checkif str_compare $Proto substr 'TCP' include

Here:

The _fetch directive retrieves all fields for each event that have been received (stored) in the last 6 hours (Stream** as FIREWALL. The output is as shown below:

In the pipelined query function, the _checkif directive uses the str_compare keyword to compare two string values. It checks whether $Proto contains the string ‘TCP’ as a substring using the substr keyword. The keyword include is used to include in the result set, the rows where this condition is satisfied.The output is as shown below:

regex

Result set satisfy a specified regular expression

The regex keyword is used along with the str_compare keyword to check if the string values in a result set satisfy a specified regular expression. This keyword offers the flexibility to create and specify your own regular expression to filter results.
Take a look at the example given below:

_fetch * from event where $Duration=6h AND $Stream=FIREWALL limit 10
_checkif str_compare $SrcISP regex '^[Cc]loud' include

Here:

The _fetch directive retrieves all fields for each event that have been received (stored) in the last 6 hours (Stream** as FIREWALL. The output is as shown below:

In the pipelined query function, the _checkif directive uses the str_compare keyword to compare string values against a regular expression. It checks whether the values in the $SrcISP field satisfy the regular expression ^[Cc]loud using the substr keyword. This regular expression checks for strings that begin with Cloud* or cloud. The keyword include is used to include in the result set, the rows where this condition is satisfied.The output is as shown below:

Although the _fetch result set returned two rows where $SrcISP contained the word Cloud, only one of the two cases begin with the word Cloud (as specified in the regular expression).Hence only one row is included in the final result set.

key_exists

Used to filter rows where not value exists for specified field.

The _checkif directive can be used to filter rows where no value exists for the specified field using the key_exists keyword. Here, no value refers to both, null values (column/value does not exists) and empty strings (typically created using “”).
Take a look at the example given below:

_fetch $SrcCN, $Stream from event limit 7
>>_checkif key_exists $SrcCN include

Here,
The _fetch directive retrieves 2 fields: SrcCN and Stream for each event. The result set is limited to the latest 7 events. The output is as shown below:

In the pipelined query function, the _checkif directive uses the key_exists keyword to identify rows where the SrcCN field has . In this case, as we use the include keyword, all such rows are included from the result set. Rows where the SrcCN field has no value (i.e. the $SrcCN field itself is either null/blank or an empty string) are included. The output is as shown below:

As shown, the 1 row where $SrcCN was blank (in the fetch result set) is included in the final result set.

lookup

The _checkif directive, using the lookup keyword, can be used to include/exclude rows from the result set currently in the pipeline (result set of the query function preceding this checkif query function) by looking up to check whether these rows are present in the specified eventstore (created using the _store directive). This lookup/comparison between the result set currently in the pipeline and the eventstore is performed on the fields specified in the checkif query function (using the join keyword). Additionally, another condition is also specified in the same query function using the above keywords (int_compare, str_compare, or key_exists).

Finally, rows are included or excluded, from the final result set, using the include/exclude keywords as follows:

• include: Include a row of the result set currently in the pipeline, in the final result set, where the value of the field (specified in the join condition) in the fetch result set has:
  • Condition 1 - The same value in the field (specified in the join condition) in the eventstore (in any of its rows) AND
  • Condition 2 - That row (or any of those rows) of the eventstore satisfying condition 1 also satisfy that second condition specified using these keywords: int_compare, str_compare, key_exists. Hence for condition 2 to be satisfied, condition 1 must be satisfied. When this condition (condition 2) is also satisfied, the row is included.
• exclude: Exclude a row of the result set currently in the pipeline where the innermost condition (condition 2) explained above is satisfied. The final result set contains those rows where the innermost condition (condition 2) is not satisfied. These rows may have failed condition 1 or, satisfied condition 1 but failed condition 2.

The rows which are present in the final result set (of the checkif query function) when using the include keyword, will not be present in it when using the exclude keyword. Similarly, rows that are present in the final result set when using the exclude keyword, will not be present when using the include keyword. Basically, they are opposites of each other. 

We have created a eventstore with the name DstPrt_cnt, for our example as shown below:

Now, take a look at the example given below:

_fetch * from event group count_unique $DstPort limit 7
>>_checkif lookup DstPrt_cnt join $DstPort = $DstPort int_compare count_unique = count_unique include

Here:

The _fetch directive retrieves all fields for each event. The result set is grouped by unique values of $DstPort (destination port) along with a count (count_unique) for each group. The result set is sorted in the descending order of count_unique (by default). It is then limited to 7 rows. The output is as shown below:

1. In the pipelined query function, the _checkif directive uses the lookup keyword to check whether the destination ports (DstPort, is present in the store (condition 1). For such rows, additionally, we check (using the int_compare keyword) whether the value of count_unique of a row in the _fetch result set is the same as the value of count_unique in the corresponding row of the eventstore which satisfied condition 1 (condition 2). Only rows, where condition 2 is satisfied, are are included.

You can compare the result set of the fetch query function and the eventstore. Five destination ports - 67, 23, 445, 443, 22 satisfied condition 1. However, out of these 5, only 2 destination ports - 67 and 22 satisfied condition 2 as well (both had same values of count_unique). Hence they are included in the final result set (shown above)
You can use the exclude keyword (instead of the include keyword) in the checkif query function to include all other rows of the fetch result set, in the final result set except these two shown above. The output is as shown below:

Destination ports 1433 and 5060 did not satisfy condition 1. Destination ports 23, 445, 443, satisfied condition 1 but did not satisfy condition 2.

stream=stream_name | checkif stream_column_name in / not in eventstore_name.eventstore_column_name

stream=firewall | checkif srcport in src_ports.$SrcPort | limit 10

stream=firewall | checkif srcport not in src_ports.$SrcPort | limit 10