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!

#### Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

#### Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

#### Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...