topic Re: How do I filter out countries when using geostats? in Getting Data In

How do I filter out countries when using geostats?

jsisko1873 — Thu, 16 Mar 2017 14:00:24 GMT

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?

Re: How do I filter out countries when using geostats?

somesoni2 — Thu, 16 Mar 2017 14:25:11 GMT

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

Re: How do I filter out countries when using geostats?

jsisko1873 — Thu, 16 Mar 2017 14:37:57 GMT

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?

Re: How do I filter out countries when using geostats?

somesoni2 — Thu, 16 Mar 2017 14:42:09 GMT

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"

Re: How do I filter out countries when using geostats?

jsisko1873 — Thu, 16 Mar 2017 15:04:25 GMT

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.

Re: How do I filter out countries when using geostats?

somesoni2 — Thu, 16 Mar 2017 15:19:01 GMT

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

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

Re: How do I filter out countries when using geostats?

jsisko1873 — Tue, 29 Sep 2020 13:17:41 GMT

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

Re: How do I filter out countries when using geostats?

somesoni2 — Thu, 16 Mar 2017 15:49:10 GMT

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

Re: How do I filter out countries when using geostats?

jsisko1873 — Thu, 16 Mar 2017 16:06:22 GMT

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

Re: How do I filter out countries when using geostats?

somesoni2 — Thu, 16 Mar 2017 16:08:42 GMT

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

Re: How do I filter out countries when using geostats?

aaraneta_splunk — Fri, 17 Mar 2017 00:43:29 GMT

@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!