How to calculate for what duration the status was in a particular state

[ak9092]

Hi,

I have a transaction that goes through multiple Status before its completed.

Now the challenge I am facing here is , one status can be mapped multiple time before the transaction is completed and in some cases the same status can keep repeated until its move to the next state -

For example ,my logs look something like below for one transaction ID -

(Note-there can be many more statuses other than the one's below)

2020-08-27 08:00:40.000, ID="20",  STATUS="CREATE"

2020-08-27 08:01:11.000, ID="20",  STATUS="POST"

2020-08-27 08:01:42.000, ID="20",  STATUS="POST"

2020-08-27 08:02:24.000, ID="20",  STATUS="POST"

2020-08-27 08:03:46.000, ID="20",  STATUS="REPAIR"

2020-08-27 08:03:56.000, ID="20",  STATUS="PENDING"

2020-08-27 08:04:00.000, ID="20",  STATUS="UPDATE"

2020-08-27 08:04:12.000, ID="20",  STATUS="UPDATE"

2020-08-27 08:04:30.000, ID="20",  STATUS="POST"

2020-08-27 08:04:46.000, ID="20",  STATUS="COMPLETE"

2020-08-27 08:04:56.000, ID="20",  STATUS="COMPLETE"

Now , What i want to do is calculate the total duration of time a transaction spent in a particular status.

So the final results should look something like below - 

ID STATUS max(_time) duration (sec)

20	CREATE	2020-08-27 08:00:40.487	31
20	POST	2020-08-27 08:02:24.265	155
20	REPAIR	2020-08-27 08:03:46.529	10
20	PENDING	2020-08-27 08:03:56.097	4
20	UPDATE	2020-08-27 08:04:12.715	30
20	POST	2020-08-27 08:04:30.366	16
20	COMPLETE	2020-08-27 08:04:56.517

As of now, with below query I am able to map the status according to time but the duration is not being calculated accurately. Can someone please help me figure this out.

my search ... | sort 0 _time
| streamstats current=false last(STATUS) as newstatus by ID
| reverse
| streamstats current=false last(_time) as next_time by ID
| eval duration=next_time-_time
| reverse
| streamstats count(eval(STATUS!=newstatus)) as order BY ID | stats max(_time) as _time, sum(duration) as "duration(sec)" BY ID order STATUS

Thanks in advance.

[thambisetty · Accepted solution]

had to use sort many times.

| stats count as count  by _time, ID,STATUS
| streamstats count   reset_on_change=true  by ID, STATUS
| eventstats max(count) as max_count by ID,STATUS
| where (count=max_count) OR (count=1)
| sort 0 - _time
| streamstats last(_time) as prev_time by ID current=false
| eval duration=prev_time-_time, time=_time
| sort 0 _time
| streamstats sum(duration) as sum_duration reset_on_change=true by ID, STATUS
| sort 0 - _time
| dedup ID,STATUS consecutive=true
| sort 0 _time 
| table ID STATUS _time sum_duration
| eval sum_duration=if(STATUS=="COMPLETE","",sum_duration)

[ak9092]

@thambisetty this helps.. Thank You!!