What is the difference between user="NULL" and isnull(user) in multivalue fields?

[yuanliu]

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?

[Ayn · Accepted solution]

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.

[yuanliu]

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?

[yuanliu]

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.

[yuanliu]

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.