I am looking for an efficient way to calculate the total bandwidth used per second on a device from our netflow data. The netflow data we receive contains a start and end time for the flow(timestamp...
See more...
I am looking for an efficient way to calculate the total bandwidth used per second on a device from our netflow data. The netflow data we receive contains a start and end time for the flow(timestamp and endtime respectively) as well as the total bytes that have been transferred. It is simple enough to calculate BPS for each flow, but I cannot figure out how to calculate total bandwidth in a usable manor.
Example netflow data:
{"endtime":"2020-03-02T17:35:31.850000Z","timestamp":"2020-03-02T17:04:51.630000Z","bytes_in":64,"dest_ip":"xxx.xxx.187.28","dest_mask":0,"dest_port":5061,"dest_sysnum":0,"event_name":"netFlowData","exporter_ip":"10.136.57.2","exporter_sampling_interval":1000,"exporter_sampling_mode":1,"exporter_time":"2020-Mar-02 17:35:22","exporter_uptime":1553552496,"flow_end_rel":1553562346,"flow_start_rel":1551722126,"ingress_vlan":103,"input_snmpidx":114,"netflow_version":9,"nexthop_addr":"0.0.0.0","observation_domain_id":0,"output_snmpidx":0,"packets_in":1,"protoid":6,"seqnumber":54418,"src_ip":"10.136.216.199","src_mask":0,"src_port":1028,"src_sysnum":0,"tcp_flags":16,"tos":184}
{"endtime":"2020-03-02T17:35:31.820000Z","timestamp":"2020-03-02T16:54:11.510000Z","bytes_in":68,"dest_ip":"xxx.xxx.187.28","dest_mask":0,"dest_port":5061,"dest_sysnum":0,"event_name":"netFlowData","exporter_ip":"10.136.57.2","exporter_sampling_interval":1000,"exporter_sampling_mode":1,"exporter_time":"2020-Mar-02 17:35:32","exporter_uptime":1553562496,"flow_end_rel":1553562316,"flow_start_rel":1551082006,"ingress_vlan":54,"input_snmpidx":49,"netflow_version":9,"nexthop_addr":"0.0.0.0","observation_domain_id":0,"output_snmpidx":0,"packets_in":1,"protoid":6,"seqnumber":54509,"src_ip":"10.136.189.15","src_mask":0,"src_port":1028,"src_sysnum":0,"tcp_flags":16,"tos":0}
I have been able to come up with a solution, but it only works with very small timeframes. I would like something that is significantly more robust. The code below will only work with a very limited number of events:
sourcetype=stream:netflow
| dedup src_ip,src_port,dest_ip,dest_port,timestamp,exporter_ip
| eval start_time = strptime(timestamp . "-0000", "%FT%T.%6QZ%z")
| eval end_time = strptime(endtime . "-0000", "%FT%T.%6QZ%z")
| eval diff_secs = end_time-start_time
| eval diff = tostring((diff_secs), "duration")
| eval bps=if(isnull(bytes_in/diff_secs),0,bytes_in/diff_secs)
| addinfo
| eval start_time_adj=if(start_time<info_min_time,info_min_time,start_time)
| eval temp=mvrange(start_time_adj,end_time)
| mvexpand temp
| rename temp AS _time
| bucket span=1s _time
| timechart sum(bps) as total_bps