Count for multiple timeframes in a single query

[ipicbc]

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

[woodcock · Accepted solution]

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

[Sab]

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

[ipicbc]

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

Many thanks

[rjthibod]

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