How to calculate the average duration of each steps within a transaction?

[RobertEttinger8]

Hi, I have events indexed in the following format:

type=a transactionID=xxxxxxxxxxx status=Created lastUpdateTime=_time
type=a transactionID=xxxxxxxxxxx status=Processing lastUpdateTime=_time
type=a transactionID=xxxxxxxxxxx status=Held lastUpdateTime=_time
type=a transactionID=xxxxxxxxxxx status=Completed lastUpdateTime=_time

type=b transactionID=yyyyyyyyyyy status=Created lastUpdateTime=_time
type=b transactionID=yyyyyyyyyyy status=Processing lastUpdateTime=_time
type=b transactionID=yyyyyyyyyyy status=Held lastUpdateTime=_time
type=b transactionID=yyyyyyyyyyy status=Completed lastUpdateTime=_time

Although it's easy to calculate the duration of each step (status change) for one transaction (I can use delta or autoregress lastUpdateTime on a eval'ed duration), how can I calculate the average duration of each step per type for a given day, so I can plot an average line on a chart against a particular transaction?

[renjith_nair · Accepted solution]

@RobertEttinger80 ,

Try and verify against your data

"your search"
|sort transactionID,lastUpdateTime
|streamstats current=f window=1 last(lastUpdateTime) as prev by transactionID,type
|eval diff=lastUpdateTime-prev|fillnull value=0 diff
|eventstats avg(eval(if(status=="Created",diff,null()))) as Created
            ,avg(eval(if(status=="Processing",diff,null()))) as Processing
            ,avg(eval(if(status=="Held",diff,null()))) as Held
            ,avg(eval(if(status=="Completed",diff,null()))) as Completed
            by type                
|fields - diff,prev

You may replace eventstats with stats if you want to display only the avg.