Splunk Search

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

SY715
Explorer

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?

0 Karma

lguinn2
Legend

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
Explorer

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)

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...