All Apps and Add-ons

Calculate the time between a transaction event's starttime and the endtime of the PREVIOUS event?

ryanholland
Explorer

I have session information for wireless clients and have grouped them into transactions. Within the transactions, I've calculated the start and end times using the event timestamp and the duration field. Here's my search.

index=ocio_networking sourcetype=aruba_stm host=*-w* src_mac=$mac$ | transaction ap_bssid startswith=eval(ap_event="Assoc success") endswith=eval(match(ap_event,"\s+sta")) keepevicted | eval "Start Time"= strftime(_time, "%m/%d/%y %H:%M:%S") | eval "End Time"=strftime((_time+duration), "%m/%d/%y %H:%M:%S") | eval "Session Length"=tostring(duration, "duration") | eval "Session Length (sec)"=duration

What I need to calculate is the amount of time that occurred BETWEEN events. In other words, what's the duration of time from an event's endtime and the following starttime? (Or, in other words, what's the duration of time from an event's starttime and the PRECEDING endtime?)

1 Solution

somesoni2
Revered Legend

Try this

index=ocio_networking sourcetype=aruba_stm host=-w src_mac=D0:22:BE:EE:C6:2B | transaction ap_bssid startswith=eval(ap_event="Assoc success") endswith=eval(match(ap_event,"s+sta")) keepevicted | eval StartTime=_time | eval EndTime=_time+duration | eval "Session Length"=tostring(duration, "duration")| streamstats window=1 current=f last(StartTime) as next_starttime | eval delta=next_starttime-StartTime  | eval "StartTime"= strftime(StartTime, "%m/%d/%y %H:%M:%S") | eval "EndTime"=strftime(EndTime, "%m/%d/%y %H:%M:%S")

View solution in original post

somesoni2
Revered Legend

Try this

index=ocio_networking sourcetype=aruba_stm host=-w src_mac=D0:22:BE:EE:C6:2B | transaction ap_bssid startswith=eval(ap_event="Assoc success") endswith=eval(match(ap_event,"s+sta")) keepevicted | eval StartTime=_time | eval EndTime=_time+duration | eval "Session Length"=tostring(duration, "duration")| streamstats window=1 current=f last(StartTime) as next_starttime | eval delta=next_starttime-StartTime  | eval "StartTime"= strftime(StartTime, "%m/%d/%y %H:%M:%S") | eval "EndTime"=strftime(EndTime, "%m/%d/%y %H:%M:%S")

ryanholland
Explorer

This got what I needed. Only difference is that I needed:

delta=next_starttime-EndTime

Thank you so much!!!!

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Assuming you have events with an epoch starttime and an epoch endtime field sorted by time in descending order, you can do this:

... | streamstats window=1 current=f last(starttime) as next_starttime | eval delta = next_starttime - starttime

martin_mueller
SplunkTrust
SplunkTrust

Well, your StartTime field is a strftime()'d string, so subtraction isn't going to work. Do the math while it's still an epoch number.

Oh, and I mixed up the substraction - you'll want to do next_starttime - endtime instead of next_starttime - starttime...

0 Karma

ryanholland
Explorer

No dice. next_starttime is calculated, but no delta field is shown.

index=ocio_networking sourcetype=aruba_stm host=-w src_mac=D0:22:BE:EE:C6:2B | transaction ap_bssid startswith=eval(ap_event="Assoc success") endswith=eval(match(ap_event,"\s+sta")) keepevicted | eval "StartTime"= strftime(_time, "%m/%d/%y %H:%M:%S") | eval "EndTime"=strftime((_time+duration), "%m/%d/%y %H:%M:%S") | eval "Session Length"=tostring(duration, "duration") | eval "Session Length (sec)"=duration | streamstats window=1 current=f last(StartTime) as next_starttime | eval delta=next_starttime-StartTime

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...