topic How to calculate individual fail time? in Splunk Search

How to calculate individual fail time?

WK — Mon, 13 Nov 2023 07:08:42 GMT

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.

Re: How to calculated individual fail time?

gcusello — Mon, 13 Nov 2023 07:14:43 GMT

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

Re: How to calculated individual fail time?

WK — Mon, 13 Nov 2023 07:30:55 GMT

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.

Re: How to calculated individual fail time?

yuanliu — Mon, 13 Nov 2023 17:46:02 GMT

@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.

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

Re: How to calculated individual fail time?

WK — Tue, 14 Nov 2023 02:45:27 GMT

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.