Filtering NULL values from multivalue field

[dfraseman]

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?

[ololdach]

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]

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

[richgalloway · Accepted solution]

"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.

[technocrat007]

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

Category!=NULL