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.
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.
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
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.
You are right, streamstats and trendline both work. I misunderstood the way the averages are calculated. Thanks!
If you convert it to an answer, I'll accept it.
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
@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
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.
Sounds like you're looking for trendline
. See the docs here.
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.
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