How do I edit my search to display multiple averages for different time ranges on one dashboard panel?

[france24]

Hello,

Newbie here, I am trying to get the following on one dashboard panel.

Average number of events per day over the last 60 days.
Average number of events per day over the last 7 days.
Total number of events over the last 24 hours.

This is the search I've got so far...

host="192.168.1.1" earliest=-60d Action=block | stats count as N | fieldformat N=N/60 | appendcols [search Action=block earliest=-7d | stats count as M] | fieldformat M=M/7 | appendcols [search Action=block earliest=-1d | stats count as O] | rename N as "Last 60 Days", M as "Last 7 Days", O as "Last 24 Hours"

So the numbers are way out. I think it has something to do with the extra searches and appendcols. This is probably obvious to most, but I am just starting out 🙂

Thanks.

[MuS · Accepted solution]

Hi france24,

no need to use appendcols to do this. Just use stats and some time tricks:

host="192.168.1.1" earliest=-60d Action=block 
| bucket _time span=1d 
| stats last(_time) AS last_time count AS per_day_count by _time
| eval 60days_ago = if(last_time > exact(relative_time(now(),"-60d@d")) AND last_time <= exact(relative_time(now(),"-0d@d")) , per_day_count ,"0") 
| eval 7days_ago = if(last_time > exact(relative_time(now(),"-7d@d")) AND last_time <= exact(relative_time(now(),"-0d@d")) , per_day_count ,"0") 
| eval 24h_ago = if(last_time > exact(relative_time(now(),"-1d@d")) AND last_time <= exact(relative_time(now(),"-0d@d")) , per_day_count ,"0")
| stats max(last_time) AS _time, avg(24h_ago) AS 24h_ago, avg(7days_ago) AS 7days_ago, avg(60days_ago) AS 60days_ago

This will perform much better and should bring the expected results; maybe some tweaking will be needed, but it should help you to get started.

cheers, MuS

[france24]

Awesome, thanks man. I'll play around with and see how it goes.

Cheers.

[piebob]

great! please be sure to come back and accept MuS's answer if it solved your problem!