_checkif


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

Example:

A quick example is given below:

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

Here:

1. The _fetch query directive retrieves all fields for each event in the event index where $LogType 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:

aggregated resultset based on country from where the traffic is originating

2. 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:

resultset of the checkif query function

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 a stored profile.

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’ ]
dt_compare  $field1 > | < | = | != | >= | <= ‘YYYY-MM-DDThh:mm:ss’
tm_compare  $field1 > | < | = | != | >= | <= ‘hh:mm:ss’
key_exists  $field1
lookup profile_name join $field1 = $field2 [int_compare | str_compare | dt_compare | tm_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).
  • dt_compare: Compares two timestamps (date and time)
  • tm_compare: Compares two time values (only time)
  • 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 a stored profile 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.

NOTE: The substr and regex keywords are available only on DNIF versions 7.8 and above.

Also available in the above versions is the option of combining multiple comparisons using the AND keyword (with one final include/exclude keyword). Hence you can write: _checkif int_compare $field1 [> | < | = | != | >= | <=] integer AND tm_compare $field1 > | < | = | != | >= | <= ‘hh:mm:ss’ [include | exclude]

int_compare

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 $LogType=WEBSERVER group count_unique $SrcIP limit 10
>>_checkif int_compare count_unique > 2000 include

Here:

1. The _fetch directive retrieves all fields for each event in the event index where $LogType is WEBSERVER. 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:

aggregated result set based on source ip addresses

2. 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 greater than 2000 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:

resultset of second query function

As shown, only those rows where count_unique is greater than 2000 are included in the result set of the _checkif query 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.

NOTE: 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

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 $LogType=WEBSERVER group count_unique $SrcIP limit 10
>>_checkif str_compare $SrcIP eq '180.XX.XX.126' include

Here:

1. The _fetch directive retrieves all fields for each event in the event index that have been received (stored) in the last 6 hours ($Duration=6h) and have $LogType as WEBSERVER. 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:

resultset of first query function

2. 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:

resultset of checkif directive

NOTE: IP addresses are stored as strings in the DNIF database.

NOTE: When comparing strings: eq implies equal to neq implies not equal to

substr

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 $LogName = 'CHECKPOINT' limit 2
>>_checkif str_compare $AtkDesc substr 'malware' include

Here:

1. The _fetch directive retrieves all fields for each event in the event index where $LogName is CHECKPOINT. The result set is limited to the latest 2 events. The output is as shown below:

all events for whom logname is checkpoint

2. In the pipelined query function, the _checkif directive uses the str_compare keyword to compare two string values. It checks whether $AtkDesc contains the string ‘malware’ 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:

searching for string named malware

regex

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 $LogName = 'CHECKPOINT' limit 4
>>_checkif str_compare $Daemon regex '^[Aa]nti' include

Here:

1. The _fetch directive retrieves all fields for each event in the event index where $LogName is CHECKPOINT. The result set is limited to the latest 4 events. The output is as shown below:

output of the fetch query

2. 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 $Daemon field satisfy the regular expression ^[Aa]nti’ using the substr keyword. This regular expression checks for strings that begin with Anti or anti. The keyword include is used to include in the result set, the rows where this condition is satisfied.The output is as shown below:

output of the checkif query function

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

tm_compare

The _checkif directive can be used to compare times in a result set with a specified time using the tm_compare keyword. You can specify the time in the format hh:mm:ss. In this case, even when the field in the result set is a timestamp (date and time) field, during comparison with the specified time, the date is ignored (only times are compared).

Take a look at the example given below:

_fetch $CNAMTime, $LogType from event limit 1
>>_checkif tm_compare $CNAMTime > '08:00:00' include

Here:

1. The _fetch directive retrieves 2 fields: $CNAMTime and $LogType for each event in the event index. The result set is limited to the latest event. The output is as shown below:

resultset with selected fields

2. In the pipelined query function, the _checkif directive uses the tm_compare keyword to compare two times. It checks whether $CNAMTime (ignoring the date) is greater than the specified time using the > operator. The keyword include is used to include in the result set the rows where this condition is satisfied.The output is as shown below:

time comparison example

$CNAMTime is stored in UTC (timezone) and displayed in IST. This query compares the stored $CNAMTime UTC time 09:08:13 (am) with the time specified in the query 8:00:00 (am). As $CNAMTime has passed the specified time, it is considered greater than it. Hence this row is included in the final result set.

dt_compare

The _checkif directive can be used to compare timestamps (date and time) in a result set with a specified timestamp using the dt_compare keyword. You can specify the timestamp in the format YYYY-MM-DDThh:mm:ss

Take a look at the example given below:

_fetch $CNAMTime, $LogType from event limit 1
>>_checkif dt_compare $CNAMTime > '2018-08-11T08:00:00' include

Here:

1. The _fetch directive retrieves 2 fields: $CNAMTime and $LogType for each event in the event index. The result set is limited to the latest event. The output is as shown below:

resultset of the first query function

2. In the pipelined query function, the _checkif directive uses the dt_compare keyword to compare two timestamps. It checks whether $CNAMTime is greater than the specified timestamp using the > operator. The keyword include is used to include in the result set the rows where this condition is satisfied.The output is as shown below:

resultset of the second query function

key_exists

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, $LogType from event limit 7
>>_checkif key_exists $SrcCN exclude

Here,

1. The _fetch directive retrieves 2 fields: $SrcCN and $LogType for each event in the event index. The result set is limited to the latest 7 events. The output is as shown below:

resultset of the first query function

2. 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 exclude keyword, all such rows are excluded 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:

using key exists keyword

As shown, the 3 rows where $SrcCN was blank (in the fetch result set) are included in the final result set. As $SrcCN was blank in these rows, the column (column name) does not appear 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 profile (created using the _store directive). This lookup/comparison between the result set currently in the pipeline and the profile 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, dt_compare, tm_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 stored profile (in any of its rows) AND
    • Condition 2 - That row (or any of those rows) of the stored profile satisfying condition 1 also satisfy that second condition specified using these keywords: int_compare, str_compare, dt_compare, tm_compare or 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 profile with the name test_ibruk_dstprt_cnt, for our example as shown below:

filteres result based on destination port

Now, take a look at the example given below:

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

Here:

1. The _fetch directive retrieves all fields for each event in the event index. 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:

resultset of the first query function

2. 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 stored profile test_ibruk_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_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 stored profile which satisfied condition 1 (condition 2). Only rows, where condition 2 is satisfied, are are included.

resultset of the second query function

You can compare the result set of the fetch query function and the stored profile. 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:

checkif query with join

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 $Field1=$Field2 where, $Field1 refers to the column in the profile 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.