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

[ryanholland]

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?)

[somesoni2 · Accepted solution]

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]

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

delta=next_starttime-EndTime

Thank you so much!!!!

[martin_mueller]

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]

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...

[ryanholland]

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