How to get duration for the product A (only for start and end status)?

[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

Is there anyway I can get the duration (End - Start) = 20 minutes and then display as Duration for Product A as 20 minutes?

[moinyuso96]

Thanks, it worked for me

[renjith_nair · Accepted solution]

Try this run anywhere example

|makeresults |eval product="A"|eval status="Start A1 A2 End"|makemv status|mvexpand status
|appendcols [|makeresults|eval time="08:00 08:05 08:15 08:20"|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)

change stats to eventstats if you want to see all records in the result