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!

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