How to figure out max transaction stop time for each store in a certain time range?

[SY715]

Hi,

I want to figure out max transaction stop time from each store in a certain time range.
If the time range of search is 10:00 to 11:00 and transactions happen like below, I want to get the result as "StoreA: 30min, StoreB: 30min, StoreC: 50min".

10:10, StoreA
10:30, StoreB
10:40, StoreA
10:45, StoreB
10:50, StoreC

In this case, I do not want the result to be "StoreA: 30min, StoreB: 15min, StoreC: null" , since I do want to take the duration between "search start time(earliest)" and "the earliest event", and between "search end time(latest)" and "the latest event" taken into account.

If I don't have to take the search start time and end time into account, the search string should be like below, but it still doesn't work for StoreC anyway.

sourcetype=*   | sort  StoreID |delta _time as durations| table  StoreID duration

Could someone tell the best solution for this?

[lguinn2]

Try this

sourcetype=* 
| stats earliest(_time) as earliestTime latest(_time) as LatestTime  range(_time) as EventDuration by StoreID
| eval SearchStartedAt=now()
| eval DurationFromSearchStart = SearchStartedAt - LatestTime
| table StoreID earliestTime LatestTime SearchStartedAt EventDuration DurationFromSearchStart

I think you want DurationFromSearchStart, but at least you can see how it can all be calculated...

[SY715]

Hi, sorry for late reply. In this case, I have to use "delta" command since sometimes transactions happen more than twice during search terms, but your answer helped me a lot. Finally, I could get the result which I really wanted with following search string. This seems to consume a lot of power of splunk resource and take some times. If you know more effective way to search, please let me know.

sourcetype=*
|sort limit=0 StoreID

|delta _time as durations
|stats earliest(_time) as earliestTime ,latest(_time) as LatestTime ,min(durations) as durations by StoreID
|eval Durations=abs(durations)
|eval SearchStartedAt=now()| eval SearchEndAt=now()-60*60
|eval DurationFromSearchStart = SearchStartedAt - LatestTime
|eval DurationFromSearchEnd = earliestTime - SearchEndAt
|table StoreID DurationFromSearchStart DurationFromSearchEnd Durations
|eval MaxStop=max(DurationFromSearchStart,DurationFromSearchEnd,Durations)