I'm not sure if the title is clear, so hopefully this helps.
I've got a dashboard with a search:
host=hostname cs_uri_stem=uri sourcetype=iis | timechart span=5m avg(time_taken) by sourcetype
This is for the last hour.
What I am looking to do is overlay a straight line showing the average for the last maybe day, or week.
How would I go about this? I have searched quite a bit, and have found plenty of answers, but none seem to apply directly to this. I'm also usually not a dashboard guy, so that probably doesn't help, either. 🙂
Thanks so much, everyone! 🙂
PS: If it makes a difference*, I am displaying this as an area chart. Thanks again! 🙂
eventstats
is usually the best way to do this. However, as you are using a split by field you would end up with multiple values for for the 30day average. EG here's a run anywhere example without a split by field (Don't be put off by the gentimes command - thats just so I can generate fake data)
| gentimes start=-30 increment=1d | eval _time=starttime | eval time_taken=random() | eventstats avg(time_taken) as 30d_avg| timechart span=7d avg(time_taken) max(30d_avg)
Instead you can use appendcols
to run a second search over your data to calculate the 30 day average and add that as a separate field. Here's a run anywhere example for you:
| gentimes start=-30 increment=1d | eval _time=starttime | eval time_taken=random() | eventstats avg(time_taken) as 30d_avg| timechart span=7d avg(time_taken) | appendcols [|makeresults count=5 |eval 30d_avg=1000000000]
Your actual search will probably look like this:
earliest=-1h@h host=hostname cs_uri_stem=uri sourcetype=iis
| timechart span=5m avg(time_taken) by sourcetype
| appendcols [
earliest=-30d@d host=hostname cs_uri_stem=uri sourcetype=iis
| stats avg(time_taken) AS 30d_average
]
Then on the visualisation tab you format the visualisation and select the 30d_average field as a chart overlay
Hello,
This discussion helped me solve a problem with a chart, thanks for that.
I don't understand one part of the first suggested solution from jplumsdaine22 :
| gentimes start=-30 increment=1d | eval _time=starttime | eval time_taken=random() | eventstats avg(time_taken) as 30d_avg| timechart span=7d avg(time_taken) max(30d_avg)
I don't get why "max(30d_avg)" actually returns the overall average from the data that the 7 day blocks are compared to (30 days back).
Sorry for digging this old thread out, but I am scratching my head hard for some time now.
I use it successfully and it is for me much more performant than the other option. But I want to understand why it works.
Thanks
Ralph
eventstats
is usually the best way to do this. However, as you are using a split by field you would end up with multiple values for for the 30day average. EG here's a run anywhere example without a split by field (Don't be put off by the gentimes command - thats just so I can generate fake data)
| gentimes start=-30 increment=1d | eval _time=starttime | eval time_taken=random() | eventstats avg(time_taken) as 30d_avg| timechart span=7d avg(time_taken) max(30d_avg)
Instead you can use appendcols
to run a second search over your data to calculate the 30 day average and add that as a separate field. Here's a run anywhere example for you:
| gentimes start=-30 increment=1d | eval _time=starttime | eval time_taken=random() | eventstats avg(time_taken) as 30d_avg| timechart span=7d avg(time_taken) | appendcols [|makeresults count=5 |eval 30d_avg=1000000000]
Your actual search will probably look like this:
earliest=-1h@h host=hostname cs_uri_stem=uri sourcetype=iis
| timechart span=5m avg(time_taken) by sourcetype
| appendcols [
earliest=-30d@d host=hostname cs_uri_stem=uri sourcetype=iis
| stats avg(time_taken) AS 30d_average
]
Then on the visualisation tab you format the visualisation and select the 30d_average field as a chart overlay
This is really close to what I needed! The only issue I have is that it isn't displaying as a line - it's showing a little square off to the side, but not an actual line across the graph.
Any ideas on that front?
Thanks so much, sir! 🙂
ah I see. Yes that's because there will be only a single result.
Add | filldown 30d_average
after the last ]
You are the MAN. Thank you so, so much, my friend!
This is absolutely perfect! 🙂
You're welcome
Thank you so much! I will be trying this out here in just a bit and will post back with the results! 🙂