Splunk Search

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

dhavamanis
Builder

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?

0 Karma

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

dhavamanis
Builder

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

0 Karma

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

0 Karma

woodcock
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
0 Karma

dhavamanis
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,

0 Karma

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

0 Karma

woodcock
Esteemed Legend

Did this work?

0 Karma

woodcock
Esteemed Legend

Just append something like this:

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

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

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