https://community.splunk.com/t5/Splunk-Search/iplocation-How-to-replace-null-or-empty-string-City-field-with/m-p/188461#M54300 <P>Gerne! <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Wed, 20 Aug 2014 14:37:17 GMT martin_mueller 2014-08-20T14:37:17Z https://community.splunk.com/t5/Splunk-Search/iplocation-How-to-replace-null-or-empty-string-City-field-with/m-p/188458#M54297 <P>Hello,</P> <P>i have several search results where the City Field ist after IPLocation not filled up. i recognized it already that simply for such an IP there is no City Information available - only Country. </P> <P>In Splunk no values are shown as "blank" or whatever values - i like to convert them to "Unknown" in the report. i tried already fill null but it did not work. can someone help? maybe with eval City=if(City="")? </P> <P><IMG src="http://answers.splunk.com//storage/Bildschirmfoto_2014-08-20_um_15.37.09.png" alt="alt text" /></P> <P>Thx a lot<BR /> Matthias</P> Wed, 20 Aug 2014 13:42:21 GMT https://community.splunk.com/t5/Splunk-Search/iplocation-How-to-replace-null-or-empty-string-City-field-with/m-p/188458#M54297 Matthias_BY 2014-08-20T13:42:21Z https://community.splunk.com/t5/Splunk-Search/iplocation-How-to-replace-null-or-empty-string-City-field-with/m-p/188459#M54298 <P>The mean thing here is that <CODE>City</CODE> sometimes is null, sometimes it's the empty string. Apparently it's null only if there is no location info whatsoever, but the empty string if there is some location info but no city. Here's an example:</P> <PRE><CODE>| stats count | eval clientip = "127.0.0.1 8.8.8.8" | makemv clientip | mvexpand clientip | iplocation clientip | eval null = if(isnull(City), "yes", "no") </CODE></PRE> <P>For me it translates Google's public DNS into "somewhere in the US" with <CODE>City=""</CODE>, and it doesn't know anything about localhost leaving <CODE>City=null</CODE>. That's why your <CODE>fillnull</CODE> fails, and short-hand functions such as <CODE>coalesce()</CODE> would fail as well.</P> <P>To address both cases you could do this:</P> <PRE><CODE>... | eval City = if(isnull(City) OR City="", "Unknown", City) </CODE></PRE> <P>Maybe move that to a macro and do the same for <CODE>Country</CODE>.</P> <P>You could also use this, but it's not a lot prettier than the <CODE>if()</CODE> expression above.</P> <PRE><CODE>... | fillnull value="Unknown" City | replace "" with "Unknown" in City </CODE></PRE> Wed, 20 Aug 2014 13:59:53 GMT https://community.splunk.com/t5/Splunk-Search/iplocation-How-to-replace-null-or-empty-string-City-field-with/m-p/188459#M54298 martin_mueller 2014-08-20T13:59:53Z https://community.splunk.com/t5/Splunk-Search/iplocation-How-to-replace-null-or-empty-string-City-field-with/m-p/188460#M54299 <P>perfect </P> <P>eval City = if(isnull(City) OR City="", "Unknown", City)</P> <P>is doing it! thanks for your nearly real time support and solution!</P> Wed, 20 Aug 2014 14:29:20 GMT https://community.splunk.com/t5/Splunk-Search/iplocation-How-to-replace-null-or-empty-string-City-field-with/m-p/188460#M54299 Matthias_BY 2014-08-20T14:29:20Z https://community.splunk.com/t5/Splunk-Search/iplocation-How-to-replace-null-or-empty-string-City-field-with/m-p/188461#M54300 <P>Gerne! <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Wed, 20 Aug 2014 14:37:17 GMT https://community.splunk.com/t5/Splunk-Search/iplocation-How-to-replace-null-or-empty-string-City-field-with/m-p/188461#M54300 martin_mueller 2014-08-20T14:37:17Z