topic Re: stats query help ! in Splunk Search

stats query help !

kc_prane — Fri, 13 Oct 2023 17:36:30 GMT

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.

Thanks in advance!

Re: stats query help !

richgalloway — Fri, 13 Oct 2023 18:10:47 GMT

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

Re: stats query help !

yuanliu — Sat, 14 Oct 2023 06:50:26 GMT

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?

Re: stats query help !

PickleRick — Sat, 14 Oct 2023 07:49:10 GMT

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