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