How can I find network traffic at consecutive time intervals?

[MonkeyK]

I am trying to write some beaconing reports/dashboards. I have a few of them figured out, but now I am stuck trying to get a decent continuous beacon query.
What I would like to do is rate connections by the number of consecutive time intervals in which they appear. Ultimately, I will use multiple intervals (1m, 15m, 30m, 1h), but for now I am just trying to solve 1 minute intervals.

example data would look something like
time, src_ip, dest_ip, dest_port
10/29/17 05:01 192.168.1.15 8.8.8.8 53
10/29/17 05:02 192.168.1.15 8.8.8.8 53
10/29/17 05:03 192.168.1.15 8.8.8.8 53
10/29/17 05:04 192.168.1.15 8.8.8.8 53
10/29/17 05:05 192.168.1.15 8.8.8.8 53

10/29/17 05:01 192.168.1.21 7.7.7.7 53
10/29/17 05:04 192.168.1.21 7.7.7.7 53
10/29/17 05:05 192.168.1.21 7.7.7.7 53

in the above sample data,
the connection 192.168.1.15 8.8.8.8 53 should get a rank of 5 because it has traffic during 5 consecutive minutes
while
the connection 192.168.1.21 7.7.7.7 53 should only get a rank of 2 since its traffic only happens for 2 consecutive minutes (even though it breaks and starts back up)

I tried this

| tstats summariesonly=t min(_time) as sessionStart
    from datamodel=Network_Traffic.All_Traffic where All_Traffic.session_id!=0
    by All_Traffic.user All_Traffic.src_ip All_Traffic.dest_ip All_Traffic.session_id All_Traffic.src_port 
| rename All_Traffic.dest_ip as dest_ip, All_Traffic.src_ip as src_ip, All_Traffic.dest_port as dest_port  
| eval _time=sessionStart 
| bin _time span=1m 
| stats count as connCount by src_ip dest_ip dest_port _time 
| streamstats count by src_ip dest_ip dest_port reset_on_change=true

(session ID is just there because both session start and session end from my firewall get mapped to Network_Traffic)
This is not good enough because the streamstats doesn't know that the adjacent events are not really consecutive intervals.

I could create a sequence,

eval seq=_time/60

But I still don't know how to compare the current against last to figure out the count

Any better ideas on how to do this?

[MonkeyK]

I think that i found a solution. If I use seq as defined above, I can add that to my sort and then streamstats to find if the last sequence. If the difference between them is 1, then they are sequential. So I did this:

| tstats summariesonly=t min(_time) as sessionStart
     from datamodel=Network_Traffic.All_Traffic where All_Traffic.session_id!=0
     by All_Traffic.user All_Traffic.src_ip All_Traffic.dest_ip All_Traffic.session_id All_Traffic.src_port 
| rename All_Traffic.dest_ip as dest_ip, All_Traffic.src_ip as src_ip, All_Traffic.dest_port as dest_port  
| eval _time=sessionStart 
| bin _time span=1m 
| stats count as connCount by src_ip dest_ip dest_port _time 
| streamstats count by src_ip dest_ip dest_port reset_on_change=true
| eval seq=_time/60 
| sort 0 src_ip , dest_ip, dest_port seq 
| streamstats last(seq) as lastseq by src_ip dest_ip dest_port window=1 current=f 
| search lastseq=* 
| eval seqDiff=seq-lastseq 
| search seqDiff=1 

so now everythin in the result set is at least a sequence of 2. But those may not be sequential, so I do it again and then count up until the sequence difference changes.
Then I can do stats to get the max count for each connection and that will give me the longest number of consecutive periods

| sort 0 source, dest_ip, dest_port seq 
| streamstats last(seq) as lastseq by src_ip dest_ip dest_port window=1 current=f 
| eval seqDiff=seq-lastseq 
| sort 0 src_ip , dest_ip, dest_port seq 
| streamstats count by src_ip dest_ip dest_port seqDiff reset_on_change=t 
| search count>1 
| stats max(count) as longestSeq by src_ip , dest_ip, dest_port

I'll hold out for a while for any better answers.