Transaction with multiple startswith conditions

[dbcase]

Hi,

I'm looking to get a duration for a transaction that has multiple startswith conditions they are

BUFFERING
CONNECTED
CONNECTING
PREPARED
RECONNECTING
STREAMING

There is only 1 endswith condition

STOPPED

The data looks like this

{ [-]
Properties: { [-]
args: [ [-]
BUFFERING

]

category: Event

index: 2

}

analyticType: DynamicChoice

buildTarget: fred

clientSessionId: DXDYVVP-ACERSJC

Any thoughts?

[elliotproebstel · Accepted solution]

Here's an approach, assuming those values are stored in a field called condition_field:

your search 
| eval transaction_start=if(in(condition_field, "BUFFERING", "CONNECTED", "CONNECTING", "PREPARED", "RECONNECTING", "STREAMING"), _time, NULL), transaction_end=if(like(condition_field, "STOPPED"), _time, NULL) 
| stats earliest(transaction_start) AS start_time latest(transaction_end) AS end_time BY clientSessionId 
| eval duration=tostring((end_time-start_time), "duration")

[dbcase]

Hi Elliotproebstel,

I could never seem to get this to work. Kept complaining about missing quotes (found that) and a missing ending ")" that I could never seem to find

[elliotproebstel]

Sorry for the missing quotation mark. Fixing that now in the OP. But as for the missing ) <- any chance that's somewhere in the data? Here's some run-anywhere code that creates two events and finds the duration of time between them, as an example:

| makeresults 
| eval condition_field="BUFFERING", clientSessionId=1234 
| append 
[| makeresults 
| eval condition_field="STOPPED", _time=_time+100, clientSessionId=1234]
| eval transaction_start=if(in(condition_field, "BUFFERING", "CONNECTED", "CONNECTING", "PREPARED", "RECONNECTING", "STREAMING"), _time, NULL), transaction_end=if(like(condition_field, "STOPPED"), _time, NULL) 
| stats earliest(transaction_start) AS start_time latest(transaction_end) AS end_time BY clientSessionId  
| eval duration=tostring((end_time-start_time), "duration")