For example, if all events in | transaction ID
contain ID
but only some carry user
, I want to capture those transactions in which user
is completely absent. | where user="NULL" AND mvcount(user)=1
gives the answer, but | where isnull(user) AND mvcount(user)=1
returns nothing.
I thought "NULL" was a string representation of null value (therefore carries a higher intrinsic overhead when used for selection), so isnull(user) would be a more natural representation. Why wouldn't it work?
If a field has the value "NULL", then it really has that string as a value. It could equally be "FOO", "ERMAHGERD" or anything. So, where user="NULL"
searches for events where the user
field really exists and has that value, whereas where isnull(user)
looks for events that doesn't have that value at all. You would achive the same with "search NOT user=*
". Because isnull(user)
means that the field doesn't exist, isnull(user) AND mvcount(user)=1
will never match.
If a field has the value "NULL", then it really has that string as a value. It could equally be "FOO", "ERMAHGERD" or anything. So, where user="NULL"
searches for events where the user
field really exists and has that value, whereas where isnull(user)
looks for events that doesn't have that value at all. You would achive the same with "search NOT user=*
". Because isnull(user)
means that the field doesn't exist, isnull(user) AND mvcount(user)=1
will never match.
This is not the case in my data. When I do |search user="NULL"
after transaction, it returns transactions in which any constituent event is missing user
, i.e., field user
doesn't exist. In fact, my data has no user named NULL. Maybe this is special to transaction
results?
As it turns out, I use fields user
before performing transaction in order to reduce data rate. In other words, the field is forced to acquire a non-null value as a side effect of fields
command. The value fields
forces for an event that doesn't contain a listed field is string NULL.
The side effect actually is not from fields
alone, but has to be combined with subsequent transaction
. In other words, if you have
| fields x y z
| transaction x
you'll end up having fields x, y, and z whose null values are replaced with string "NULL". However, simply using |fields x y z
would not have this effect.