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!

#### 2024 Splunk Career Impact Survey | Earn a \$20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

#### Optimize Cloud Monitoring

TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

#### What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...