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!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...