Alerting
Highlighted

calculate average for every 6 events. is it possible?

Explorer

Dear Team,

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

Capture.JPG

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

Labels (1)
Tags (1)
0 Karma
Highlighted

Re: calculate average for every 6 events. is it possible?

SplunkTrust
SplunkTrust

you should be able to use streamstats to do this

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

View solution in original post

Highlighted

Re: calculate average for every 6 events. is it possible?

Explorer

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

Tags (1)
0 Karma
Highlighted

Re: calculate average for every 6 events. is it possible?

Explorer

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

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.