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.
... View more