How to calculate moving average and graph it as an overlay on a bar chart of actual values?

[krwinters11]

I am trying to calculate a moving average and overlay those values on a bar chart of actual values. This is what I have:

| streamstats window=7 avg(Value) AS AvgValue | chart values(AvgValue) AS "Moving Average", values(Value) AS "Actual Value" by _time

The window function doesn't seem to be working. Every point has a different value for moving average; it should be every 7 points, unless I am misunderstanding how splunk is calculating the value.

Any suggestions would be great! Regards.

[bmunson_splunk]

If all you want is a smoother graph, that can be achieved with an average of averages. It is mathematically questionable but does reduce the jagged effect when plotted. Try something like this.

earliest=-24h@h sourcetype=access*| timechart  span=10m sum(bytes) as bytes | streamstats avg(bytes) as av1 window=5 | streamstats avg(av1) as av2 window=3

[bmunson_splunk · Accepted solution]

No you are misunderstanding the way these averages are calculated. Point 7 will be the average of points 1-7 but point 8 is the average of 2-8, point 9 is the average of 3-9 and so on. Point 14 is 8-14 but there are 6 points between 7 and 14 giving the jerky appearance.

[krwinters11]

You are right, streamstats and trendline both work. I misunderstood the way the averages are calculated. Thanks!

[krwinters11]

If you convert it to an answer, I'll accept it.

[bmunson_splunk]

streamstats and trendline can both create moving averages but they do not reduce the granularity of the line. It will still look as "jerky" but they are accurate averages. Increasing the window will make it look smoother.

earliest=-24h@h sourcetype=access*| timechart  span=10m sum(bytes) as bytes | streamstats avg(bytes) as av1 window=5 | streamstats avg(bytes) as av2 window=50

[HattrickNZ]

@bmunson_splunk I tried this and eventually got it to work. But can you clarify what as av1 window=5 will that be the average of tje 1st 10min slot to the 5th 10min slot, and this average is put in the 5th slot, and not put in the 6th slot.

Slots10min  totalPerslot    av1     
slot1   10  10  <<-average of slot1 
slot2   11  10.5    <<-average of slot1-slot2   
slot3   12  11  <<-average of slot1-slot3   
slot4   13  11.5    <<-average of slot1-slot4   
slot5   14  12  <<-average of slot1-slot5   
slot6   15  13      
slot7   16  14      
slot8   17  15      
slot9   18  16      
slot10  19  17  

[BobM]

The as av1 just tells splunk to name the average av1.

window=5 says take the average over 5 events (by default) including this one. So the average of slot 1-5 goes in slot 5 , 2-6 in slot 6 and so on. But there is an extra option you can say, current=false. This will then over ride the default and use the previous 5 not including the current one. So slot6 has the average of 1-5, slot7 has 2-6 and so on.

[aljohnson_splun]

Sounds like you're looking for trendline. See the docs here.

[krwinters11]

trendline seems to react the same as the stream stats function above
still jerky like bmunson_splunk mentioned in the above comment. Unless I am misunderstanding the way that Splunk is calculating, it should be averaging 7 points and the 14 and then 21 so the plots should only be every 7 events.

[krwinters11]

It looks like a central moving average would use a window with the point in the center. I'm not sure splunk is doing this(?). It looks as though it is calculating the average using all data up to that point. which is why it is different for each point. http://en.wikipedia.org/wiki/Moving_average