calculate duration of multiple seperate log lines

[AzJimbo]

Sometimes a single connection is interrupted in the logs, becoming two lines. (Lines 2 and 5) and sometimes the exact same tuples will occur again, but later than a logical connection would last (line 7).
I need to calculate and add duration of all logical sockets.

     Start_Time            End_time             src            spt    proto   dest           dpt   bytes

LINE 7 06-29-2014 6:20:09 06-29-2014 6:22:17 192.168.2.145 37603 TCP 192.168.0.139 80 32648

LINE 6 06-29-2014 5:18:58 06-29-2014 5:18:59 192.168.0.21 63254 TCP 192.168.0.139 80 2548

LINE 5 06-29-2014 5:18:57 06-29-2014 5:19:27 192.168.2.145 37603 TCP 192.168.0.139 80 42522

LINE 4 06-29-2014 5:18:51 06-29-2014 5:18:52 192.168.2.145 37604 TCP 192.168.0.139 80 2545

LINE 3 06-29-2014 5:18:51 06-29-2014 5:18:57 192.168.0.128 11023 TCP 192.168.0.137 80 6877

LINE 2 06-29-2014 5:18:49 06-29-2014 5:18:51 192.168.2.145 37603 TCP 192.168.0.139 80 265

LINE 1 06-29-2014 5:18:48 06-29-2014 5:18:48 192.168.5.210 60817 TCP 192.168.0.139 80 352

I added the LINE # for reference. That is not part of the log.

I'm stuck trying to extract the min max times from the transactions but keeping the maxpause.

I'm looking for results that look something like:

192.168.5.210:60817 192.168.0.139:80 2s

192.168.2.145:37603 192.168.0.139:80 40s

yada yada...

192.168.2.145:37603 192.168.0.139:80 128s

[yong_ly]

what's the difference between 2,5 and 7 here with regards to duration? they all show 3 different connections from the same src and host etc.. If you are wanting the minimum/max durations for each src/host combination that you should be able to:

[query]
| eval duration=strptime(End_Time, "%Y-%m-%d %H:%M:%S")-strptime(Start_Time, "%Y-%m-%d %H:%M:%S")
| stats max(duration), min(duration) by src,spt,dst,dpt

[AzJimbo]

Unfortunately, they are not three different connections. Sometimes a single connection is interrupted in the logs, becoming two lines. (Lines 2 and 5) and sometimes the exact same tuples will occur again, but later than a logical connection would last (line 7).

I think I need some combination of a maxspan from transaction and your suggestion with stats.
The transaction command uses only the _time field (not shown in sample) not the internal times from the log lines. stats doesn't group similar lines together.

[AzJimbo]

Thanks; I need the start time from line 2 and the end time from 5 and exclude 7.
All have the same src, spt, dst and dpt.

I need other durations too.
what's not working:

[query]
| eval src:spt = src +":"+ spt
| eval dst:dpt = dest +":"+ dpt
| eval starttime_epoch=strptime(Start_Time, "%Y-%m-%d %H:%M:%S")
| eval endtime_epoch=strptime(End_Time, "%Y-%m-%d %H:%M:%S")
| transaction src:spt, dst:dpt maxpause=20s
| stats min(starttime_epoch) as sstart max(endtime_epoch) as eend by src:spt, dst:dpt
| eval dur = eend - sstart
| eval Duration(Min)= dur/60
[table]

It doesn't separate 2&5 from 7

[aholzer]

What's the duration you are trying to calculate? I see that all your events have both a start and end time.

There's the duration of the event itself:
... | eval event_duration = end_time - start_time

There's the duration of the transaction which gets calculated automatically if you use the transaction command:
... | transaction src spt proto dest dpt

There's the sum of the event durations:
... | eval event_duration = end_time - start_time | stats sum(event_duration) as "total_duration" by src spt proto dest dpt

Hope this helps