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

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!

Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...