Null value locator (nvl)

[ 1 Description ] [ 2 How does it work in the search window? ] [ 3 How does it work in LINQ? ]

Description

Adds a new field that returns the value specified as the second argument if the first one is null.

How does it work in the search window?

Select Create field in the search window toolbar, then select the Null value locator operation. This operation needs two arguments:

Argument	Data type

Argument	Data type
When not null ^mandatory	Any
else ^mandatory	Same as When not null

The data type of the new field depends on the result of the operation.

Example

In thesiem.logtrust.web.activity table, we want to create a field that labels the reputation of the IP addresses in the srcHost using the IP Reputation Score (reputationscore) operation, and then assign a specified value in a new field for null occurrences.

Step 1: Label IP addresses using IP Reputation Score

First, transform the srcHost field into IP data type using the To IPv4 operation (the IPs in this field are shown as strings).

Then, create a new column using the IP Reputation Score operation by adding the newly transformed IP column as argument. We will get in this column, which you can call reputation, some null values when the IP addresses are not found in any reputation list.

Step 2: Assign a specific value to all the null occurrences

Now, create a new field using the Null value locator operation. We want the new field to show 0 when the values in the reputation field are null. Call the new field nvl and add both arguments and enter the following values:

When not null - reputation field
else - Click the pencil icon and enter -1

Click Create field and you will see the following result:

As the reputation score ranges from 0 to 100, null values will be easily identified with the -1 value.

How does it work in LINQ?

Use the operator select... as... and add the operation syntax to create the new field. This is the syntax for the Null value locator operation:

nvl(field_to_check, value_when_null)
field_to_check ?: value_when_null

Example

You can copy the following LINQ script and try the above example on the siem.logtrust.web.activity table.

from siem.logtrust.web.activity
  select ip4(srcHost) as IP,
    reputationscore(IP) as IP_reputation,
    nvl(IP_reputation, -1) as nvl

or

from siem.logtrust.web.activity
  select ip4(srcHost) as IP,
    reputationscore(IP) as IP_reputation,
    IP_reputation ?: -1 as nvl