How to get duration for the product A in 2 occurrences ?

[moinyuso96]

So what I have now from my search so far

Product     Status    Time

A                   Start        8.00 AM

A                    A1            8.05 AM

A                    A2            8.15 AM

A                    End          8.20 AM

A                   Start         8.40 AM

A                    End          9.40 AM

Right now I only get the second start-end duration = 60 minutes only. How can I get it to show the first start-end duration = 20 minutes as well? The "Product" name needs to be the same.

|makeresults |eval product="A"|eval status="Start A1 A2 End Start End"|makemv status|mvexpand status
|appendcols [|makeresults|eval time="08:00 08:05 08:15 08:20 08:40 09:40"|makemv time|mvexpand time ]
|rename COMMENT as "Dummy data ends here"
|eval time=strptime(time,"%H:%M")
|stats max(eval(if(status=="Start",time,null()))) as Start_Time,max(eval(if(status=="End",time,null()))) as End_Time by product
|eval duration=round((End_Time-Start_Time)/60)

[moinyuso96]

Thank you for the clear and thorough explanation. 

[bowesmana · Accepted solution]

streamstats may be your friend

| makeresults 
| eval product="A" 
| eval status="Start A1 A2 End Start End" 
| makemv status 
| mvexpand status 
| appendcols 
    [| makeresults 
    | eval time="08:00 08:05 08:15 08:20 08:40 09:40" 
    | makemv time 
    | mvexpand time ] 
| rename COMMENT as "Dummy data ends here" 
| eval time=strptime(time,"%H:%M") 
| streamstats reset_on_change=t reset_after="status=\"End\"" min(time) as Start_Time max(time) as End_Time by product 
| eval duration=round((End_Time-Start_Time)/60)
| where status="End"
| table product duration *_Time

but you would need to handle the cases where there is no End and also ensure that there is no interleaving of "starts" for the same product before an end - but you would need some other correlation field in that case.

This example shows products A and B, where before the streamstats you sort by product and time

| makeresults 
| eval product="A" 
| eval status="Start A1 A2 End Start End" 
| makemv status 
| mvexpand status 
| appendcols 
    [| makeresults 
    | eval time="08:00 08:05 08:15 08:20 08:40 09:40" 
    | makemv time 
    | mvexpand time ] 
| append [
  | makeresults 
  | eval product="B" 
  | eval status="Start B1 B2 End Start End" 
  | makemv status 
  | mvexpand status 
  | appendcols 
    [| makeresults 
    | eval time="08:02 08:07 08:15 08:50 08:55 09:30" 
    | makemv time 
    | mvexpand time ] 
]
| sort product time
| rename COMMENT as "Dummy data ends here"
| eval time=strptime(time,"%H:%M") 
| streamstats reset_on_change=t reset_after="status=\"End\"" min(time) as Start_Time max(time) as End_Time by product 
| eval duration=round((End_Time-Start_Time)/60)
| where status="End"
| table product duration *_Time