topic Re: Count for multiple timeframes in a single query in Splunk Search

Count for multiple timeframes in a single query

ipicbc — Wed, 22 Mar 2017 08:30:34 GMT

I am looking for a table where each row has the count of a value, in this case errors for each host. I need 2 value columns, 1 for no. errors in the last day, the other for errors in the last hour. Maybe a 3rd with errors in the last 10 minutes. Is there a way that I can have a single query which delivers the output from multiple count statements in a way that I can use them to populate a table?

Thanks

Re: Count for multiple timeframes in a single query

rjthibod — Wed, 22 Mar 2017 11:14:54 GMT

Try this. You will need to add your base search to it, and you will need to change fields if your base search returns more events than only the error ones you are interested in.

You could do this with eventstats, but that is probably more efficient since you are going over the data set multiple times.

<BASE_SEARCH> earliest=-1d 
| eval marker = if(_time >= relative_time(now(), "-10min"), "ten", if(_time >= relative_time(now(), "-1h"), "hour", "day")) 
| chart count over host by marker 
| fillnull value=0 day hour ten 
| eval day = day + ten + hour 
| eval hour = hour + ten
| table host day hour ten

Re: Count for multiple timeframes in a single query

woodcock — Sat, 25 Mar 2017 05:28:51 GMT

Like this:

... | eval age = now() - _time | stats count(eval(age<86400)) AS errors_last_24_hours count(eval(age<3600)) AS errors_last_hour count(eval(age<600)) AS errors_last_10_minutes BY host

Re: Count for multiple timeframes in a single query

ipicbc — Mon, 27 Mar 2017 13:52:15 GMT

This is a thing of beauty, for which I am extremely grateful and from which I have learned a lot!!!

Many thanks

Re: Count for multiple timeframes in a single query

Sab — Tue, 27 Apr 2021 20:47:24 GMT

How to search for today's timeframe from the below,

eval age = now() - _time | stats count(eval(age<86400)) AS errors_last_24_hours count(eval(age<3600)) AS errors_last_hour count(eval(age<600)) AS errors_last_10_minutes BY host