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
... View more