• v9.x
_checkif
• Contributors
• Dark
Light
• PDF

# _checkif

• Dark
Light
• PDF

_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 Stream is FIREWALL. The result set is grouped by unique values of $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). Note: Use the exclude keyword in the _checkif directive to exclude from the result set the rows for which a condition is not satisfied. When comparing integers: > implies greater than < implies less than = implies equal to != implies not equal to >= implies greater than or equal to <= implies less than or equal to ## str_compare 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 (Duration=6h) and have 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: Note IP addresses are stored as strings in the DNIF database. When comparing strings: eq implies equal to neq implies not equal to. ### substr 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 ($Duration=6h) and have 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 a value. 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. Note: The result set currently in the pipeline refers to the result set of the current (last) query function executed/displayed. In this context, it refers to the query function immediately preceding the checkif query function begin explained. 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 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) present in the fetch result set are also present in the specified eventstore DstPrt_cnt (using the join keyword). This is done for each row in the _fetch result set. For each row, we check whether the$DstPort, is present in the store (condition 1). For such rows, additionally, we check (using the int_comparekeyword) 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.

Note:

We can specify the column/field name in the format join Field2 where, $Field1 refers to the column in the eventstore and$Field2 refers to the column in the result set currently in the pipeline.
In the above example, use int_compare $DstPort =$DstPort (along with the include keyword) if the join keyword is sufficient and you do not need any additional logic while joining.