Splunk Search

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

gearmana
Explorer

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

0 Karma
1 Solution

jplumsdaine22
Influencer

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

View solution in original post

ralphnowitzki
New Member

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

0 Karma

jplumsdaine22
Influencer

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
Explorer

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

0 Karma

jplumsdaine22
Influencer

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

Add | filldown 30d_average after the last ]

gearmana
Explorer

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

This is absolutely perfect! 🙂

jplumsdaine22
Influencer

You're welcome

0 Karma

gearmana
Explorer

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

0 Karma
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 ...