topic Re: Filtering NULL values from multivalue field in Splunk Search

Filtering NULL values from multivalue field

dfraseman — Wed, 06 May 2020 13:39:15 GMT

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?

Re: Filtering NULL values from multivalue field

richgalloway — Wed, 06 May 2020 14:03:14 GMT

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

Re: Filtering NULL values from multivalue field

ololdach — Wed, 06 May 2020 14:07:08 GMT

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.

Re: Filtering NULL values from multivalue field

dfraseman — Wed, 06 May 2020 14:26:42 GMT

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

Re: Filtering NULL values from multivalue field

technocrat007 — Sat, 10 Apr 2021 09:34:07 GMT

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

Category!=NULL