Getting Data In

How do I filter out countries when using geostats?

jsisko1873
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

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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

jsisko1873
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

somesoni2
Revered Legend

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

0 Karma

somesoni2
Revered Legend

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

0 Karma

jsisko1873
Explorer

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

0 Karma

aaraneta_splunk
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

somesoni2
Revered Legend

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")
Get Updates on the Splunk Community!

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

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

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...