stats query help !

[kc_prane]

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!

[PickleRick · Accepted solution]

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]

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]

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