calculate baseline for chart in different time range

[disha]

I am plotting timechart avg(secs) by city for timerangepicker time range. I want to add a baseline in this chart as avg(secs) over last 30 days.
I am doing :

mysearch |timechart avg(secs) by city|append [search mysearch earliest=-30d latest=now |stats avg(secs) as baseline|eval SLA=baseline]

it is just giving one point in chart as baseline. not a horizontal line as timerange is different. I just need to calculate avg(secs) for last 30 days as baseline and add to the timechart.

Please suggest.

[musskopf · Accepted solution]

Would eval + return work for you?

For example:

mysearch |timechart avg(secs) by city|eval baseline=[search mysearch earliest=-30d latest=now |stats avg(secs) as baseline|return $baseline]

The other option might be appendcols, but I never used it...

[rashid47010]

hi

I try above query but the baseline value is static. my query is

my query  | timechart count(src_ip) by dest | eval baseline=[search myquery earliest=-30d latest=now | stats count(src_ip) as baseline | return $baseline]

[martin_mueller]

While that should work, I'd recommend storing the thirty-day average in a lookup (or maybe 6.2's newfangled KV store?) with a reasonably frequently scheduled update and loading that whenever this search is run. Depending on how much data you have it'll be a lot faster to not have to calculate the thirty-day average on the fly over and over again.

Some background on using lookups for that: http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/