Dashboards & Visualizations

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

france24
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

MuS
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

MuS
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

france24
Engager

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

Cheers.

piebob
Splunk Employee
Splunk Employee

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

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...