Getting Data In

How do I filter out countries when using geostats?

Explorer

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?

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

SplunkTrust
SplunkTrust

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

View solution in original post

Explorer

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?

0 Karma

SplunkTrust
SplunkTrust

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"
0 Karma

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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

Explorer

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: outtoin and intoout

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

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Explorer

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

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

SplunkTrust
SplunkTrust

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