Splunk Search

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

yuanliu
SplunkTrust
SplunkTrust

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?

1 Solution

Ayn
Legend

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.

View solution in original post

Ayn
Legend

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
SplunkTrust
SplunkTrust

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?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...