calculate average for every 6 events. is it possible?

[vengat4043]

Dear Team,

We have a dataset which having the calculated percentage value as below:

what we need to calculate average of Eff_% for every 6 events like,  Event 1-6,2-7,3-8 and 4-9, etc.,

Here the code we tried, Please check and suggest:

index="*WF*" index!="wf_summary" WindFarm="Amerali"
|eval PitchAngle = round(PitchAngle, 0)
|eval RotorSpeed = round(RotorSpeed, 1)
|eval WindSpeed = round(WindSpeed_10AV, 2)
|eval ActivePower = round(PowerKW_10AV, 2)
|eval AmbientTemperature = round(AmbientTemperature, 2)
|eval GeneratorSpeed_PLCFilter = round(GeneratorSpeed_PLCFilter,2)
|eval NacellPosition = round(NacellePosition_10AV,2)
|stats values(PitchAngle) as PitchAngle values(RotorSpeed) as RotorSpeed values(WindSpeed) as WindSpeed values(ActivePower) as ActivePower values(AmbientTemperature) as AmbTemp values(GeneratorSpeed_PLCFilter) as GeneratorSpeed values(NacellPosition) as NacellePosition by _time,WindFarm, Turbine
| eval Category = case(PitchAngle < 20 AND RotorSpeed >= 9.7, "Run", PitchAngle >= 20 AND RotorSpeed < 9.7, "NotRunning", PitchAngle >= 20 AND RotorSpeed > 9.7, "Transition", PitchAngle < 20 AND RotorSpeed < 9.7, "UnKnown")
| search Category = "Run"
| lookup Power WS AS WindSpeed OUTPUTNEW EST_Power AS EstimatedPower
|eval CM_Status = case(WindSpeed > 0 AND WindSpeed < 10,"0",WindSpeed >= 10 AND WindSpeed <= 12, "1", WindSpeed > 12,"0")
|eval Eff_% = (ActivePower/EstimatedPower)*100
|fillnull value=0 Eff_%
|fields - PitchAngle, RotorSpeed, AmbTemp, GeneratorSpeed, NacellePosition, Category
|sort Turbine
|streamstats count(Eff_%) as Ecount
|eval alert_New = mvrange(1, Ecount+1, 6)
| eventstats avg(Eff_%) as Alert by alert_New
|eval Alert = round(Alert,2)
|table _time, WindFarm,Turbine, WindSpeed, ActivePower, EstimatedPower, CM_Status, Eff_%, Ecount, Alert, alert_New

Please Suggest.......

[twesty · Accepted solution]

you should be able to use streamstats to do this

streamstats avg(<<FIELD_NAME>>) as "90 min average" time_window=90m

[vengat4043]

@twesty  thanks for your input and we got the output what we expected. Really thank you.

[vengat4043]

@twesty  once again thanks for your support. and One more thing,

Is it possible to fetch the 6 events when the given condition met?

For Ex:

As per the above query we are calculating the average of every 6 events, if the average value is less that 97 means we need to show the all 6 events which we taken for calculating the average. is it possible?

Please suggest something.