Need your help,
Please refer the below data structure. We want to calculate the and display moving average of the current value, previous 2 values, and the next value.
Input:
Month, Value
201501,100
201502,50
201503,50
201504,100
201505,50
201506,100
Output:
Month, Value,Moving_Average
201501,100,
201502,50,
201503,50,75
201504,100,62.5
201505,50,75
201506,100,
So the moving average per row would be calculated using the current row's value, previous 2 values, and the next value, displaying the average in the current row. (100+50+50+100=300/4=75). Can you please help form the Splunk search for this structure?
This one was FUN!
... | streamstats current=f last(Value) AS Value_f1 | reverse | autoregress Value p=1-2 | table Month Value* | reverse | eval numValueFields=0 | foreach Value* [eval numValueFields = numValueFields + if(isnull(<<FIELD>>),0,1)] | fillnull value=0 | eval Moving_Average = (Value_p1 + Value_p2 + Value + Value_f1) / numValueFields
You might need a | reverse
in the beginning depending on how your dataset is sorted.
Thank you so much. we want keep the first Moving_Average value in 3rd row. Can you please adjust query and share it.
We want to show the first 4 numbers average in 3rd row Moving_Average column. Can you please tell us how to do that.
Seriously? I just told you how!
... | streamstats current=f last(Value) AS Value_f1 | reverse | autoregress Value p=1-2 | table Month Value* | reverse | eval numValueFields=0 | foreach Value* [eval numValueFields = numValueFields + if(isnull(<<FIELD>>),0,1)] | fillnull value=0 | eval Moving_Average = (Value_p1 + Value_p2 + Value + Value_f1) / numValueFields | table Month Value Moving_Average
We need the output in the below format, here first 2 column of Moving_Average is empty, 3rd row having the average value of first 4 Values. Please adjust the query and share. with the above query its listing
Output:
Month, Value,Moving_Average
201501,100,
201502,50,
201503,50,75
201504,100,62.5
201505,50,75
201506,100,
OK, then this (but really all the hard parts were already there):
... | reverse | streamstats current=f last(Value) AS Value_f1 | reverse | autoregress Value p=1-2 | table Month Value* | reverse | eval numValueFields=0 | foreach Value* [eval numValueFields = numValueFields + if(isnull(<<FIELD>>),0,1)] | fillnull value=0 | eval Moving_Average = if((numValueFields==4),(Value_p1 + Value_p2 + Value + Value_f1) / numValueFields, null()) | table Month Value Moving_Average
The leading | reverse
is there assuming that your events are sorted as you presented them (backwards, with the oldest first, at the top).
Did this work?
Just append something like this:
| table Month Value Moving_Average