Splunk Search

## How do I write a search to calculate a moving average using the current value, previous 2 values, and the next value?

Builder

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?

Tags (3)
Esteemed Legend

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.

Builder

Thank you so much. we want keep the first Moving_Average value in 3rd row. Can you please adjust query and share it.

Builder

We want to show the first 4 numbers average in 3rd row Moving_Average column. Can you please tell us how to do that.

Esteemed Legend

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

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,

Esteemed Legend

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).

Esteemed Legend

Did this work?

Esteemed Legend

Just append something like this:

``````| table Month Value Moving_Average
``````
Get Updates on the Splunk Community!

#### Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

#### Troubleshooting the OpenTelemetry Collector

In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...

#### Adoption of Infrastructure Monitoring at Splunk

Splunk's Growth Engineering team showcases one of their first Splunk product adoption-Splunk Infrastructure ...