How do I filter out countries when using geostats?

[jsisko1873]

When I run this line I get the results mapped on the cluster map, but I want to filter out the US.

action=allowed | stats count by src_ip |iplocation src_ip |geostats latfield=lat longfield=lon count by Country 

I've tried using the where clause below but it will not work.

where Country != "United States"

Any suggestions?

[somesoni2 · Accepted solution]

Move the where clause to just after iplocation and before geostats command.

action=allowed | stats count by src_ip |iplocation src_ip | where Country != "United States"|geostats latfield=lat longfield=lon count by Country

[jsisko1873]

So I've tried putting it where you suggested and at the end. When I have it in the middle, I'll get events but I won't get statistics or the visualization option which I would like to have. Do you have any other ideas?

[somesoni2]

So, when you just run below, you can see some results in statistics tab, but when you add the geostats you don't get anything?

action=allowed | stats count by src_ip |iplocation src_ip | where Country != "United States"

[jsisko1873]

Correct. When I run what you posted (and actually its action=blocked, no difference though) I will get results, but as soon as I try to add geostats I can't find a way to filter countries. And that's why I'm not sure if its a placement thing unto where it goes, of if I should be using a different command to filter my results.

[somesoni2]

You've already filtered the countries before geostats, so what's the purpose of filtering again?

The thing is after geostats command, there is no field called Country, so you can't filter it after geostats. With geostats, each Country value will become a field in itself (your output will have fields geobin, latitude, longitude and one column for each Country). So, if you want to show data only for a specific country you'd need to use table or fields command.

   action=allowed | stats count by src_ip |iplocation src_ip | where Country != "United States"|geostats latfield=lat longfield=lon count by Country | table geobin, latitude, longitude, EnterCountryNameThatYouWantToShow

OR

action=allowed | stats count by src_ip |iplocation src_ip | where Country != "United States"|geostats latfield=lat longfield=lon count by Country | fields -  EnterCountryNameThatYouDon'tWantToShow

[jsisko1873]

Perfect, thank you! The second block was what I wanted. Much appreciated! If I may ask one more quick question since you've been so helpful. I'm trying to rename a result in a field (There are only two results). I was trying to use the eval command to do that and haven't gotten it to work.
For the firewall there is two rules for the "rule" field: out_to_in and in_to_out

How do I need to change the eval or should I use something else?

eval rule=case(rule==out_to_in,"Layer 3 Core access in",rule==in_to_out,"Subnet out to Layer 3 Core")

[somesoni2]

Do you get two rows one column or one row two columns, when you look at result in statistics tab?

[jsisko1873]

Rule and count are the columns, and then I have two rows, one for each rule.

[aaraneta_splunk]

@jsisko1873 - Did the answer provided by somesoni2 help provide a working solution to your original question? If yes, please don't forget to resolve this post by clicking "Accept". If no, please leave a comment with more feedback. Thanks!

[somesoni2]

Then the eval you had should work. Just need to enclose value of the rules in double quotes.

...| eval rule=case(rule=="out_to_in","Layer 3 Core access in",rule=="in_to_out","Subnet out to Layer 3 Core")