Field1=Start
Field2=Finish
Field1 and Field2 have multiple events with values Start and Finish for a given uid respectively. I want to pick earliest event for Fiield1 and latest event for Field2 and find the duration.
Field3=uid which is the common field.
….| transaction uid startswith=“Start” ends with=“Finish” | stats avg(duration)
It’s not giving the expected result.
Hi @newbie77,
If an instance of Field1=Start is always the earliest event by uid and Field2=Finish is always the latest event by uid, you can use the stats range() function:
| stats range(_time) as duration by uid
Otherwise, use the stats min() and max() or earliest() and latest() functions with an eval expression:
| stats min(eval(case(Field1=="Start", _time))) as start_time max(eval(case(Field2=="Finish"))) as finish_time by uid
| eval duration=finish_time-start_time
It’s not giving the expected result.
This is a lot better than a phrase we hear too often: "It doesn't work."
This said, what is the expected result? To ask an answerable data analytics question, follow these golden rules; nay, call them the four commandments:
Hi @newbie77,
If an instance of Field1=Start is always the earliest event by uid and Field2=Finish is always the latest event by uid, you can use the stats range() function:
| stats range(_time) as duration by uid
Otherwise, use the stats min() and max() or earliest() and latest() functions with an eval expression:
| stats min(eval(case(Field1=="Start", _time))) as start_time max(eval(case(Field2=="Finish"))) as finish_time by uid
| eval duration=finish_time-start_time