How to overlay an average as a line over a timeseries

[Splunkster45]

I've looked around for an answer. Indeed, I modeled my answer after the following question:
https://answers.splunk.com/answers/225230/how-to-display-the-average-of-the-week-as-a-straig.html?ut...

I didn't get it to work, so I will ask my questions.

I have a search that gives me a timeseries of an event and a search that gives me an average. I can use an appendcols item to join the two, however, when I look at the graph, the timeseries shows up, but the average is a single dot for one timestamped value. I'd like the average to be a line across the chart.

Here's the command I'm running:

<query> | stats SUM(COUNT_ONE) by StartTime, ODATE, MODEL | sort by ODATE, StartTime | appendcols [ search <query> | stats AVG(COUNT_ONE) as new by MODEL]

As you can see, the last column only has one value for the average. If I can repeat this value on each row, I'll get what I want in my graph.

Thoughts?

[sundareshr · Accepted solution]

You don't need the appendcols. Try this

<query> | stats SUM(COUNT_ONE) by StartTime, ODATE, MODEL | eventstats AVG(COUNT_ONE) as new by MODEL | sort by ODATE, StartTime 

[Splunkster45]

By putting the eventstats option first and the stats optino second, I was able to get it.

<query>| eventstats AVG(COUNT_ONE) as new by MODEL | stats SUM(COUNT_ONE), avg(new) by StartTime, ODATE, MODEL