How to calculate individual fail time?

[WK]

I have following data:
02:00:00 Item=A Result=success
02:00:05 Item=B Result=success
02:05:00 Item=A Result=fail
02:05:05 Item=B Result=success
02:10:00 Item=A Result=fail
02:10:05 Item=B Result=success
02:15:00 Item=A Result=success
02:15:05 Item=B Result=fail
02:20:00 Item=A Result=success
02:20:05 Item=B Result=fail
02:25:00 Item=A Result=success
02:25:05 Item=B Result=success
02:30:00 Item=A Result=success
02:30:05 Item=B Result=success
02:35:00 Item=A Result=success
02:35:05 Item=B Result=success
02:40:00 Item=A Result=success
02:40:05 Item=B Result=fail
02:45:00 Item=A Result=success
02:45:05 Item=B Result=success
02:50:00 Item=A Result=success
02:50:05 Item=B Result=success
02:55:00 Item=A Result=success
02:55:05 Item=B Result=success

My desired results:
Item StartTime EndTime Duration
A       02:05:00    02:15:00 00:10:00
B       02:15:05    02:25:05 00:10:00
B       02:40:05    02:45:05 00:05:00

I had tried transaction and streamstats but got wrong results.
Can anybody here help me to solve this problem?
Thank you.

[gcusello]

Hi @WK,

what's the condition fro grouping?

How can I recognize StartTime and EndTime?

this is one of the few situation where to use the transactin command.

if you want to trace when there's a Fail and a following Success, you could try somethin like this:

<your_search>
| transaction Item StartsWith="Result=Fail" EndsWith="Result=Success"
| eval 
   StartTime=strftime(_time,"%H:%M:%S),
   EndTime=strftime(_time+duration,"%H:%M:%S), 
   Duration=tostring(duration,"duration")
| table Item StartTime EndTime Duration

Ciao.

Giuseppe

[WK]

Thanks for your swift response.
I need to calculate the duration between first "fail" to first "success" for every Item.
Unfortunately the result is incorrect:
Item StartTime EndTime Duration
B        02:40:05 02:45:05 00:05:00
B        02:20:05 02:25:05 00:05:00
B        02:15:05 02:30:05 00:15:00     ==> should be "B  02:15:05 02:25:05 00:10:00"
A        02:10:00 02:15:00 00:05:00
A        02:05:00 02:20:00 00:15:00     ==> should be "A  02:05:00 02:15:00 00:10:00"

I'd tried this method before, however consecutive "Result=fail" causes overlapped results.

[yuanliu]

@gcusello's answer should give you the desired output.  Is it possible that some events come out of order?  You can use sort to make sure events are in perfect revere time order.

| sort - _time
| transaction Item startswith="Result=fail" endswith="Result=success"
| eval EndTime = _time + duration
| fieldformat EndTime = strftime(EndTime, "%F %T")
| fieldformat duration = tostring(duration, "duration")
| fields Item _time EndTime duration

Here you can rename _time as StartTime if you wish, then format it for display.  For large number of events, sort can be expensive.  This is one of the costs of transaction when raw events are not perfectly in order.

Here is an emulation you can play with and compare with raw data

| makeresults
| eval data = split("02:00:00 Item=A Result=success
02:00:05 Item=B Result=success
02:05:00 Item=A Result=fail
02:05:05 Item=B Result=success
02:10:00 Item=A Result=fail
02:10:05 Item=B Result=success
02:15:00 Item=A Result=success
02:15:05 Item=B Result=fail
02:20:00 Item=A Result=success
02:20:05 Item=B Result=fail
02:25:00 Item=A Result=success
02:25:05 Item=B Result=success
02:30:00 Item=A Result=success
02:30:05 Item=B Result=success
02:35:00 Item=A Result=success
02:35:05 Item=B Result=success
02:40:00 Item=A Result=success
02:40:05 Item=B Result=fail
02:45:00 Item=A Result=success
02:45:05 Item=B Result=success
02:50:00 Item=A Result=success
02:50:05 Item=B Result=success
02:55:00 Item=A Result=success
02:55:05 Item=B Result=success", "
")
| mvexpand data
| rename data as _raw
| rex "^(?<_time>\S+)"
| eval _time = strptime(_time, "%H:%M:%S")
| extract
``` data emulation above ```

Combining the two, I get

Item	_time	EndTime	duration	_raw
B	2023-11-13 02:40:05	2023-11-13 02:45:05	00:05:00	02:40:05 Item=B Result=fail 02:45:05 Item=B Result=success
B	2023-11-13 02:20:05	2023-11-13 02:25:05	00:05:00	02:20:05 Item=B Result=fail 02:25:05 Item=B Result=success
B	2023-11-13 02:15:05	2023-11-13 02:30:05	00:15:00	02:15:05 Item=B Result=fail 02:30:05 Item=B Result=success
A	2023-11-13 02:10:00	2023-11-13 02:15:00	00:05:00	02:10:00 Item=A Result=fail 02:15:00 Item=A Result=success
A	2023-11-13 02:05:00	2023-11-13 02:20:00	00:15:00	02:05:00 Item=A Result=fail 02:20:00 Item=A Result=success

[WK]

Hi,

Thanks for your response.
My desired results:
Item StartTime EndTime Duration
A       02:05:00    02:15:00 00:10:00
B       02:15:05    02:25:05 00:10:00
B       02:40:05    02:45:05 00:05:00

I had tried similar methods like your but got wrong results.
Fail duration should be calculated from first fail to first success.
Thus actual record count should be 3 instead of 5.
Sorting may not be the root cause for my question.

It seems that if there are 2 "fail" events, "transaction" commands generates 2 overlapped records.
B      02:20:05(fail)     02:25:05(success)  00:05:00
B      02:15:05(fail)     02:30:05(success)  00:15:00
Time duration of first one is included in second one.
02:30:05(success) should not be considered as the end of fail event.
02:25:05(success) is the correct one.