iplocation: How to replace null or empty string City field with "Unknown"?

[Matthias_BY]

Hello,

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.

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="")?

Thx a lot
Matthias

[martin_mueller · Accepted solution]

The mean thing here is that City 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:

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

For me it translates Google's public DNS into "somewhere in the US" with City="", and it doesn't know anything about localhost leaving City=null. That's why your fillnull fails, and short-hand functions such as coalesce() would fail as well.

To address both cases you could do this:

... | eval City = if(isnull(City) OR City="", "Unknown", City)

Maybe move that to a macro and do the same for Country.

You could also use this, but it's not a lot prettier than the if() expression above.

... | fillnull value="Unknown" City | replace "" with "Unknown" in City

[martin_mueller]

Gerne! 🙂

[Matthias_BY]

perfect

eval City = if(isnull(City) OR City="", "Unknown", City)

is doing it! thanks for your nearly real time support and solution!