Dashboards & Visualizations

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

Engager

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.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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 solution in original post

SplunkTrust
SplunkTrust

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 solution in original post

Engager

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

Cheers.

Splunk Employee
Splunk Employee

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