Splunk Search

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

Matthias_BY
Communicator

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

alt text

Thx a lot
Matthias

Tags (2)
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

Gerne! 🙂

0 Karma

Matthias_BY
Communicator

perfect

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

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

Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!