Splunk Cloud Platform

Calculate cumulative time from first event in group for each event

smahoney
Explorer

Basically data looks like this.  I want to calculate the average time to complete an order across many orders each with different amounts of items.  

 

_time,orderId,"receivedOrder"
_time,itemId,orderId,"completedItemProcessing"
_time,itemId,orderId,"completedItemProcessing"
_time,itemId,orderId,"completedItemProcessing"

I have a query that works, but its hugely inefficient and throws errors due to hitting the stats limit.  There has to be a better way to do this, streamstats or delta just gave me the duration from the previous event in the chain and I need to have that evaluate against the order time for all items in an order, not the previous item.


 

index=index "@mt"="itemComplete" OR @mt="orderReceived" 
| fillnull value=orderReceived itemComplete
| stats earliest(_time) as orderReceived list(itemId) as itemId,list(_time) as prepTime by orderId 
| mvexpand prepTime
| eval timeToPrep=prepTime-orderReceived 
| stats avg(timeToPrep) as avgItemCompletedDuration
| where startOfPreperation>0

 

   

Labels (1)
0 Karma

ITWhisperer
Legend

What about streamstats earliest(_time) as start latest(_time) as end by orderId, Then each event should have the start and end time of the whole order.

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!