topic Re: Why does isnotnull command return true for blank Country field added by iplocation? in Splunk Search

Why does isnotnull command return true for blank Country field added by iplocation?

frbuser — Thu, 05 Sep 2019 19:26:13 GMT

I am using the iplocation command on an IP based field to add new fields to each event, most importantly the Country field. I want to then filter the output to only entries where the Country field is not blank. I tried using:

Country=*

but entries with blank values still are returned.

I also tried using:

isnotnull(Country)

but it returns true where the field is clearly blank. Can anyone explain this behavior?

My query:

index::proxy host::proxyhost sourcetype::bcoat_log 
| regex cs_host="\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}" 
| top cs_host limit=0 
| iplocation cs_host 
| search Country=*
| eval null=if(isnotnull(Country),"true","false")

Re: Why does isnotnull command return true for blank Country field added by iplocation?

richgalloway — Thu, 05 Sep 2019 20:31:44 GMT

Country=* searches for all values of Country, including blank. To find non-blank values, try NOT Country = "".

Re: Why does isnotnull command return true for blank Country field added by iplocation?

frbuser — Thu, 05 Sep 2019 21:04:30 GMT

@richgalloway Entries with blank values still show up with that.

Re: Why does isnotnull command return true for blank Country field added by iplocation?

richgalloway — Thu, 05 Sep 2019 21:17:54 GMT

So it does. Sorry about that. Try where instead as in this run-anywhere example:

| makeresults annotate=t 
| eval cs_host="8.8.8.8" 
| iplocation cs_host 
| where isnotnull(Country)

Re: Why does isnotnull command return true for blank Country field added by iplocation?

frbuser — Mon, 09 Sep 2019 17:15:16 GMT

@richgalloway where also does not work. Per my original question, the problem is that the isnotnull() function is returning true for some fields that are blank.

Re: Why does isnotnull command return true for blank Country field added by iplocation?

richgalloway — Mon, 09 Sep 2019 17:46:01 GMT

Blank is not the same as null so isnotnull(blank) is correct.

Re: Why does isnotnull command return true for blank Country field added by iplocation?

starcher — Mon, 09 Sep 2019 17:53:51 GMT

I agree an empty string is not a NULL which is absence of any value. You can do an isnotnull or Len = 0

Re: Why does isnotnull command return true for blank Country field added by iplocation?

frbuser — Mon, 09 Sep 2019 17:58:50 GMT

@richgalloway what is isnotnull(blank)?

Re: Why does isnotnull command return true for blank Country field added by iplocation?

frbuser — Mon, 09 Sep 2019 18:01:52 GMT

@starcher how do you check that the len of a field is not 0?

Re: Why does isnotnull command return true for blank Country field added by iplocation?

starcher — Mon, 09 Sep 2019 18:03:47 GMT

https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/TextFunctions

Re: Why does isnotnull command return true for blank Country field added by iplocation?

frbuser — Mon, 09 Sep 2019 18:21:27 GMT

@starcher eval length=len(Country) doesn't return any numeric value for some fields that have no visible value. These appear to be the null values. If I combine isnotnull(Country) AND NOT len(Country)=0 this appears to work.

Re: Why does isnotnull command return true for blank Country field added by iplocation?

richgalloway — Wed, 11 Sep 2019 12:17:56 GMT

By that I mean a field with blanks for a value is not null. Therefore, isnotnull() will correctly return true for that field.