Calculate average time taken for transactions.

[newbie77]

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.

[yuanliu]

---

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:

• Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search that volunteers here do not have to look at.
• Illustrate the desired output from illustrated data.
• Explain the logic between illustrated data and desired output without SPL.
• If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious.

[tscroggins · Accepted solution]

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