How to overlay a straight line showing the average time taken over an existing timechart?

[gearmana]

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! 🙂

[ralphnowitzki]

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

[jplumsdaine22 · Accepted solution]

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

[gearmana]

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! 🙂

[jplumsdaine22]

ah I see. Yes that's because there will be only a single result.

Add | filldown 30d_average after the last ]

[gearmana]

You are the MAN. Thank you so, so much, my friend!

This is absolutely perfect! 🙂

[jplumsdaine22]

You're welcome

[gearmana]

Thank you so much! I will be trying this out here in just a bit and will post back with the results! 🙂