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.
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
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
Awesome, thanks man. I'll play around with and see how it goes.
Cheers.
great! please be sure to come back and accept MuS's answer if it solved your problem!