Splunk Search

Filtering NULL values from multivalue field

dfraseman
Explorer

I have a transaction with mvlist set to true which results in a table where a number of fields display multiple NULL values:

Col1  Col2  Col3
12345 NULL  1111
NULL  XYZ   2222
NULL  NULL  3333

Note: this is all 1 row

I would like it filtered to the following:

Col1  Col2  Col3
12345 XYZ   1111
            2222
            3333

In splunk docs I read that mvfilter in combination with isnotnull or !isnull functions can be used when you want to return only values that are not NULL from a multivalue field. Neither of these appear to work for me:

y=mvfilter(isnotnull(x))
y=mvfilter(!isnull(x))

While this does:

y=mvfilter(x!="NULL"))

Any ideas on why the former doesn't work? Are there any performance differences between each method?

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

"NULL" is not NULL. The former is a value consisting of a string of four characters whereas the latter is the absence of a value. The isnotnull function tests if the field has a value so, since all of your example values are not NULL, will return all values.

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

ololdach
Builder

My guess would be that the transaction with mvlist=true does not return NULL as a value but rather "NULL" as a string. You can easily test the assumption by trying len(x). If it returns 4, then the transaction has written the word "NULL" into the result. If len(x) gives you nothing or 0, then it's the NULL value itself. From what you've observed, I think, it's the first.

dfraseman
Explorer

Your assumption is correct. len(x) returns 4 for the NULL occurrences. Thanks a lot!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

"NULL" is not NULL. The former is a value consisting of a string of four characters whereas the latter is the absence of a value. The isnotnull function tests if the field has a value so, since all of your example values are not NULL, will return all values.

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

technocrat007
New Member

Say eg I have Category field with NULL value so simply use below code,

Category!=NULL

Tags (1)
0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!