Splunk Search

stats query help !

kc_prane
Path Finder

Hello,  I am searching to get results for each hour  top 1 max URL hits.  Iam using the below search but not getting results for each hour.

index=*  | fields Request_URL _time
| stats count as hits by Request_URL _time |bucket span=1h _time
| sort by hits desc
| head 1

Thanks in advance!

Labels (1)
Tags (1)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

1. index=* is something that is very rarely a good idea. Be as specific about your search as you can to use resources effectively

2. It makes no sense to stats by time and only afterwards splitting into time-based buckets. For such case you should either bin first and then stats by _time or simply use timechart with a proper span.

3. As was already pointed out, head is not tbe way to go. The alternative to using dedup could be using stats first or last

So your final search could look like this

index=<be_specific_here>
| bin span=1h _time
| stats count by Request_URL _time
| sort _time count
| stats last(*) as * by _time

As an exercise you could try to solve the same problem using another approach - adding stats with eventstats and filtering with where

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

1. index=* is something that is very rarely a good idea. Be as specific about your search as you can to use resources effectively

2. It makes no sense to stats by time and only afterwards splitting into time-based buckets. For such case you should either bin first and then stats by _time or simply use timechart with a proper span.

3. As was already pointed out, head is not tbe way to go. The alternative to using dedup could be using stats first or last

So your final search could look like this

index=<be_specific_here>
| bin span=1h _time
| stats count by Request_URL _time
| sort _time count
| stats last(*) as * by _time

As an exercise you could try to solve the same problem using another approach - adding stats with eventstats and filtering with where

yuanliu
SplunkTrust
SplunkTrust

Aside from mistaken use of head as @richgalloway points out, what is the reason to perform stats on _time before bucketing if your goal is to find maximum per hour?

index=*
| bucket _time span=1h
| stats count by _time Request_URL
| sort - count
| dedup _time
| sort _time

 

richgalloway
SplunkTrust
SplunkTrust

Rather than head 1, which returns the first of all results, try dedup _time, which will return the first result from each hour (because of the bucket and sort commands).

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...