Average of value during last running state

[aseadmin]

I am having data as shown in the below image,

Is there a way i can get the avg of output considering the data for state is running; but only from the last time state was changed to running and not for a specific time period

The data to be considered for finding the avg is marked in grey in the image attached

[somesoni2 · Accepted solution]

Give this a try

your current searching fetching data with timestamp(_time), state and output fields
| eventstats max(eval(if(state="start",_time,null()))) as lastStart
| where _time>=lastStart AND state="running"
| stats avg(output) as Avg_Output

[aseadmin]

thank you. it was my mistake. i was having field sent over http input as json and i was accessing the field as message.state but infact i had to have single qoutes around it as 'message.state'. thank you once again

[aseadmin]

thank you for giving me something to start with but eventstats max(eval(if(state="start",_time,null()))) as lastStart is always giving null. i am sure i have entries with state = start; infact i tried changing to eventstats max(eval(if(state="running",_time,null()))) as lastStart to see if i am getting some match on if condition