Splunk Search

stats and timechart in same query, different duration?

pjc
Engager

I'm fairly new to Splunk queries, so apologies if this is overly simplistic.

I have a query looking at apache logs in combination with some geoip lookups. It only returns the clientip when that IP has more than 50 hits per Apache log in the given duration (5 mins) and isn't from Texas:

index=myIndex | stats count(host) as HitsByIP by clientip,source | search HitsByIP > 50 | dig clientip | lookup geoip clientip as clientip | search NOT (client_country = "United States" AND client_region = "TX")

I really want to be able to see this in a timechart (or equivalent) so that I can see only the IP's with > 50 hits, but look at counts for those IPs by minute or 5 minutes but over a longer course of time, say 1 hour. When I add a timechart to the end, I end up with no results.

The below gets me a little closer with a subquery, but its still not what I'm looking for but it does give results:

index=myIndex [search index=myIndex | stats count(host) as HitsByIP by clientip,source | search HitsByIP > 50 | dig clientip | lookup geoip clientip as clientip | search NOT (client_country = "United States" AND client_region = "NC")| fields + clientip] | timechart span=5m count by clientip

So maybe I need a different duration for the subquery than the timechart?

Any info is much appreciated.

pjc

Tags (1)

jonuwz
Influencer

+1 Well worded question with example code.

0 Karma

jonuwz
Influencer

You can modify the subquery slightly.

search index=myIndex | where _time>now()-300 | stats count(host) as HitsByIP by clientip,source | ... etc

This'll only consider results in the last 5 minutes (300 seconds) and feed those clientips into the main search

You can of course just set earliest=-5m in your subquery, but you'll get a blue bar informing you that the timespan was substituted.

If its a custom dashboard, thats not a problem, since you can just set the message module (forget what its called) to only show warnings and higher

0 Karma

jonuwz
Influencer

cool - you caught the answer before i realised there was a much simpler (and more accurate) solution.

The problem with my first response is that the 5 minute sample would 'snap' to 00, 05, 10 , 15 minutes past the hour. So if your search ran at 09:12 (for example) then you'd be looking for > 50 hits in only 2 minutes.

The updated answer (with now()-300 ) looks back 5 minutes from the time the search was kicked off, and is a far more accurate sample window

0 Karma

pjc
Engager

jonuwz,

Trying your initial response:
First we round _time to 5 minute intervals
Then we do the stats command over _time too
This allows us to find the most recent 5 minute interval with eventstats
Then only we can filter in clientips with > 50 hits in the latest 5 minutes.

With only the removal of the eventstats as I want to get the full duration (say 1 hour) by 5 min increments. I tried and got data, but I need to verify the data. looks promising. I have to run for the day, but will get back to this Monday.

thanks for quick response and hopefully solution(s).

pjc

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