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".
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?
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...
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.
|sort limit=0 StoreID
|delta time as durations
|stats earliest(time) as earliestTime ,latest(_time) as LatestTime ,min(durations) as durations by StoreID
|eval SearchStartedAt=now()| eval SearchEndAt=now()-60*60
|eval DurationFromSearchStart = SearchStartedAt - LatestTime
|eval DurationFromSearchEnd = earliestTime - SearchEndAt
|table StoreID DurationFromSearchStart DurationFromSearchEnd Durations