Splunk Search

Filter out results for geolocations using a eventstats where clause

alaorath
Path Finder

I've been struggling with this one for a while now, I think I need to use a sub search to filter out the results, but the solution eludes me.

I have a ping test script that connects to remote sites and (after validating the IP address is valid) tries to ping that route. The purpose being to verify the primary/backup/diual-up and VSAT routes are active for a particular site.

We initially just plotted the results on a table via csv, but I thought it would be better to use latitude and longitude data to plot it in Splunk... which works great, however, end-users think the results are too "noisey", they want to filter out sites that only fail more than two "routes" to the same location.

Here's the (sanitized) query I'm using for the first pass:
| lookup geolocations Field_Device_Name AS Name OUTPUT Latitude Longitude
| geostats latfield=Latitude, longfield=Longitude, globallimit=0 count by route_name

Which works... but I need to remove locations with only one result. Here's my attempt to filter those out using eventstats where the count > 2:

| eventstats count by site_location
| where count >2
| lookup geolocations Field_Device_Name AS Name OUTPUT Latitude Longitude
| table route_name Latitude, Longitude
| geostats latfield=Latitude, longfield=Longitude, globallimit=0 count by route_name

Which returns far fewer results than expected (only a couple results on the map). If I chop off just the "geostats" part, it seems to show the correct result in the table.

0 Karma
1 Solution

alaorath
Path Finder

I think this is one of those "Talk to the Duck" cases...

Thinking more about it and trying to troubleshoot, I found the following works exactly as I want:

| eventstats count by site_location | where count >2 | lookup geolocations Field_Device_Name AS Name OUTPUT Latitude Longitude | eventstats count by route_name | geostats latfield=Latitude, longfield=Longitude, globallimit=0 count by route_name

I'm still not clear on why it works that way... but it's Friday afternoon and I have results my client asked for. 🙂

View solution in original post

0 Karma

alaorath
Path Finder

I think this is one of those "Talk to the Duck" cases...

Thinking more about it and trying to troubleshoot, I found the following works exactly as I want:

| eventstats count by site_location | where count >2 | lookup geolocations Field_Device_Name AS Name OUTPUT Latitude Longitude | eventstats count by route_name | geostats latfield=Latitude, longfield=Longitude, globallimit=0 count by route_name

I'm still not clear on why it works that way... but it's Friday afternoon and I have results my client asked for. 🙂

0 Karma

lguinn2
Legend

I think the problem is that geostats groups the results based on the "zoom" of the map. So when you look at individual sites, there are may be many that have only 1 result. But when you look at the results with geostats, it combines "nearby" results based on lat-long and your zoom level.

So site_location may not be the appropriate grouping for your eventstats.

You might look at some of the options for the geostats command itself to bin results.

0 Karma

alaorath
Path Finder

I've always equated "geostats" to "stats", so maybe I'm thinking about this the wrong way.

For example, if I replace the geostats from my above query and adding "stats count by site_location", the results I get are exactly what I expect: about 200 "route_name" results, but all of them from sites with at least 3 results.

I don't see why changing the "stats" mode from normal to geostats suddenly truncates the results to only 5. 😕

It's a bit hard to describe exactly what I'm faced with while maintaining data security.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...