Splunk Search

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

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.

Tags (5)
1 Solution  Splunk Employee

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.  Splunk Employee

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
``````  Splunk Employee

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. Path Finder

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

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

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
`````` Motivator

@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
`````` Builder

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.  Splunk Employee

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

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. Path Finder

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 .conf21 CFS Extended through 5/20!

### Don't miss your chance to share your Splunk wisdom in-person or virtually at .conf21!Call for Speakers hasbeen extended throughThursday, 5/20! Submit Now! >

Get Updates on the Splunk Community!