topic Re: Calculate total bps from NetFlow data in Security
https://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415592#M13451
<P>Did you ever come up with a solution to this? I am attempting to solve the same problem and can't quite figure it out.</P>
<P>Unfortunately I don't have the option to use Kafka, so a SPL solution would be the best for me.</P>Mon, 02 Mar 2020 12:52:19 GMTpestatp2020-03-02T12:52:19ZCalculate total bps from NetFlow data
https://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415587#M13446
<P>I am trying to calculate bandwidth from NetFlow data.</P>
<P>Each flow data has the timestamp of flow generated time, the timestamp of the first packet (%FIRST_SWITCHED), the timestamp of the last packet (%LAST_SWITCHED), and total received bytes (%IN_BYTES) and sent bytes (%OUT_BYTES).<BR />
By using these data, I can understand the average bandwidth of each flow by the following formula;</P>
<PRE><CODE> in_bps = IN_BYTES * 8 / (LAST_SWITCHED - FIRST_SWITCHED)
out_bps = OUT_BYTES * 8 / (LAST_SWITCHED - FIRST_SWITCHED)
</CODE></PRE>
<P>However, I am not sure how I can create a timechart of total bandwidth of multiple flows.</P>
<P>Currently, _time is set by the timestamp when the flow data is received.<BR />
If I use it to derive total bandwidth like "index=netflow | timechart span=5min sum(in_bps) by PROTOCOL", it does not give me the right figure.<BR />
To derive the accurate result, I need to pick up the flow data that can meet the condition, current time >= FIRST_SWITCHED & current_time<=LAST_SWITCHED, and calculate the sum of in_bps or out_bps of each flow for that specific time.<BR />
But I have no idea how I can write such SPL to analyze multiple timestamps in a single log entry.</P>
<P>It would be great if someone can give me some idea to perform such task in Splunk.</P>
<P>Just in case, NetFlow data is like below json data;<BR />
{"22":1543571246000,"11":443,"12":"xxx.xxx.xxx.xxx","23":16550,"24":233,"14":0,"57590":91,"1":3209400,"2":2154,"4":6,"5":0,"6":27,"7":13726,"8":"yyy.yyy.yyy.yyy","57659":"FQDN of URL","10":0,"21":1543571253000}</P>
<P>I wrote field extractions, and each json data will be extracted to _time, LAST_SWITCHED, FIRST_SWITCHED, IN_BYTES, OUT_BYTES, PROTOCOL, and so on.</P>Tue, 29 Sep 2020 22:13:38 GMThttps://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415587#M13446masato_sekiguch2020-09-29T22:13:38ZRe: Calculate total bps from NetFlow data
https://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415588#M13447
<P>Maybe I'm missing something, but I don't see any glaring issues with your methodology (see more below) so what makes you think it's wrong? And is it wrong from the view of some other product's numbers, or is it wrong from the view of "given the data I have, Splunk is literally not adding things up right?"</P>
<P>Simplifying a bit, you have a timespan and a number of bytes. Doing the math you did should get you a bits per (time unit of time difference). I can't confirm which number in your JSON maps to which field, but my guess would be the 1543561246000 is one of the LAST_SWITCHED or FIRST_SWITCHED, and there's another timstamp like that later. Those are not seconds, they also include the ms. So that means the math you have shows bits per millisecond, not bits per second. Could that be it?</P>Tue, 29 Sep 2020 22:13:59 GMThttps://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415588#M13447Richfez2020-09-29T22:13:59ZRe: Calculate total bps from NetFlow data
https://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415589#M13448
<P>Please ignore msec or sec. It is not important here.</P>
<P>IN_BYTES is total received bytes during FIRST_SWITCHED and LAST_SWITCHED.<BR />
I can derive the average bps by the formula I wrote, but when I think about drawing bps graph, the graph should have this average bps value starts from FIRST_SWITCHED and end at LAST_SWITCHED.<BR />
If I use SPL I wrote, the bps value of the flow only appears at _time in the graph.<BR />
It does not take into consideration of the session timespan, FIRST_SWITCHED and LAST_SWITCHED.</P>
<P>From mathematics view point, bps of the flow can be certain function over time. I use fx(t) in here for the flow, x. IN_BPS for flow x will be the integral of fx(t) from FIRST_SWITCHED and LAST_SWITHCED.</P>
<PRE><CODE>IN_BPS x = integrate.quad (lambda t , fx(t), FIRST_SWITCHED, LAST_SWITCHED)
</CODE></PRE>
<P>To derive the total bandwidth at particular time, t1, we need to calculate below.</P>
<PRE><CODE>total_bps at t1 = f1(t1) + f2(t1) + ... + fx(t1)
</CODE></PRE>
<P>timechart span=5min sum(in_bps) does not do this calculation. <BR />
timechart just use _time value and derive sum of all bps value without considering FIRST_SWTICHED and LAST_SWITCHED.</P>
<P>In our case, 50% of flow is completed less than a second, and the longest flow have 120sec.<BR />
So just deriving 5min average by IN_BYTES and OUT_BYTES would be good enough although it does not consider LAST_SWITCHED and FIRST_SWITCHED. If I use LAST_SWITCHED for _time, the result may be more accurate.</P>
<PRE><CODE>index=netflow | bin _time span=5min | stats sum(IN_BYTES) as IN_BYTES sum(OUT_BYTES) as OUT_BYTES by _time,PROTOCOL | eval 5m_avg=(IN_BYTES+OUT_BYTES)/300/1024/1024 | timechart span=5min max(5m_avg) as 5m_avg by PROTOCOL
</CODE></PRE>
<P>Regarding flow data,<BR /><BR />
22: FIRST_SWITCHED<BR />
11: L4_DST_PORT<BR />
12: IPV4_DST_ADDR<BR />
23: OUT_BYTES<BR />
24: OUT_PKTS<BR />
14: OUTPUT_SNMP (interface number)<BR />
57590: L7_PROTO<BR />
1: IN_BYTES<BR />
2: IN_PKTS<BR />
4: PROTOCOL<BR />
5: SRC_TOS<BR />
6: TCP_FLAGS<BR />
7: L4_SRC_PORT<BR />
8: IPV4_SRC_ADDR<BR />
57659: HTTP_HOST<BR />
10: INPUT_SNMP (interface number)<BR />
21: LAST_SWITCHED</P>Tue, 29 Sep 2020 22:16:28 GMThttps://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415589#M13448masato_sekiguch2020-09-29T22:16:28ZRe: Calculate total bps from NetFlow data
https://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415590#M13449
<P>Ah, OK, so have it use the average of the bit rate over the whole duration, only per second (or whatever - spread it out).</P>
<P>This is more difficult. It can be done, I have an example of something similar but it's a wee bit nasty and very specific to a certain type of data, so it'll take a bit of work before it's even ready to put here.</P>
<P>First, though - your particular question you didn't quite ask - you can use LAST_SWITCHED as the time if you want. Just assign it like <CODE>index=netflow | eval _time = LAST_SWITCHED | bin _time ...</CODE> and continue as you have. And right, this works reasonably well as long as your minimum time span you will be plotting is significantly longer than the longest time span in your data, but there's still a lot of boundary effects.</P>
<P>So, to solve the more general problem - the idea is to compute the average rate, via one of a couple of methods break that up into second-by-second values, then finally do your final summations.</P>
<P>So using placeholder numbers, let's have one flow have a start of 5 and an end of 10 (including 10) and a total of 120, another is start 7 end 9 flow 90 you'd average...</P>
<P>First one to 20 per unit of time, spread that into <CODE>5, 20</CODE>, <CODE>6, 20</CODE>, <CODE>7, 20</CODE>, <CODE>8, 20</CODE>, <CODE>9, 20</CODE>, <CODE>10, 20</CODE>. <BR />
Second one to 30 per unit of time, spread into <CODE>7, 30</CODE>, <CODE>8,30</CODE>, <CODE>9,30</CODE>.</P>
<P>Then re-add that back up per second, <CODE>5, 20</CODE>, <CODE>6, 20</CODE>, <CODE>7, 50</CODE>, <CODE>8, 50</CODE>, <CODE>9, 50</CODE>, <CODE>10, 20</CODE>. And tada, that's really the numbers you need to work with.</P>
<P>As I said, I have some working code for a different problem that I think I can bend to doing this task so hopefully I can get a chance to sort it out in the next day or two and get a copy posted here. </P>
<P>I can fake up some close-enough data easily enough, and I should have time a little later this week to really dig through the example I have and convert it to this task. Someone else might jump in, but I think if you can wait a bit I'll have something worked up.</P>Tue, 29 Sep 2020 22:16:39 GMThttps://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415590#M13449Richfez2020-09-29T22:16:39ZRe: Calculate total bps from NetFlow data
https://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415591#M13450
<P>Thanks for your feedback.<BR />
The idea looks good although I am not sure how I can break up 1 json flow to multiple place holders. And I am also wondering if the suggested solution would be practical in the environment which have more than 1,000 flows per sec.<BR />
In my case, long-lived session will have 120 sec timespan. We need to have 120,000 place holders if we have 1,000 of such long-lived session. </P>
<P>I am currently evaluating kafka. NetFlow data is sent to kafka from the flow generator, and Splunk reads it via Kafka connect for Splunk. <BR />
It might be much easier to analyze flow data in Kafka layer to derive bps, pps, or other statistics, create new topic for statistics, and read that statistics from Splunk for visualization.<BR />
Although I am new to Kafka, I will also try that path and see which would be practical.</P>Tue, 04 Dec 2018 01:39:21 GMThttps://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415591#M13450masato_sekiguch2018-12-04T01:39:21ZRe: Calculate total bps from NetFlow data
https://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415592#M13451
<P>Did you ever come up with a solution to this? I am attempting to solve the same problem and can't quite figure it out.</P>
<P>Unfortunately I don't have the option to use Kafka, so a SPL solution would be the best for me.</P>Mon, 02 Mar 2020 12:52:19 GMThttps://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415592#M13451pestatp2020-03-02T12:52:19ZRe: Calculate total bps from NetFlow data
https://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415593#M13452
<PRE><CODE>| makeresults
| eval _raw="{\"22\":1543571246000,\"11\":443,\"12\":\"xxx.xxx.xxx.xxx\",\"23\":16550,\"24\":233,\"14\":0,\"57590\":91,\"1\":3209400,\"2\":2154,\"4\":6,\"5\":0,\"6\":27,\"7\":13726,\"8\":\"yyy.yyy.yyy.yyy\",\"57659\":\"FQDN of URL\",\"10\":0,\"21\":1543571253000}"
| spath
| rename 1 as IN_BYTES, 22 as FIRST_SWITCHED, 23 as OUT_BYTES, 21 as LAST_SWITCHED, 4 as PROTOCOL
| table LAST_SWITCHED, FIRST_SWITCHED, IN_BYTES, OUT_BYTES, PROTOCOL
| foreach *_SWITCHED
[ eval <<FIELD>>_p =strftime('<<FIELD>>'/1000 , "%F %T") ]
| eval in_bps = IN_BYTES * 8 / ((LAST_SWITCHED - FIRST_SWITCHED) /1000)
| eval out_bps = OUT_BYTES * 8 / ((LAST_SWITCHED - FIRST_SWITCHED) / 1000)
</CODE></PRE>
<P>So far we have calculated.</P>Mon, 02 Mar 2020 13:30:13 GMThttps://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415593#M13452to4kawa2020-03-02T13:30:13ZRe: Calculate total bps from NetFlow data
https://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415594#M13453
<P>This still wouldn't provide a total bandwidth from multiple events, correct? </P>
<P>I took the original question to be asking how to get the total bandwidth for a particular time from multiple events with total bytes and a time range.</P>Mon, 02 Mar 2020 14:02:10 GMThttps://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415594#M13453pestatp2020-03-02T14:02:10ZRe: Calculate total bps from NetFlow data
https://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415595#M13454
<P>sample log is only one. so I can't make query. will you provide 10~20 samples?</P>Mon, 02 Mar 2020 21:21:41 GMThttps://community.splunk.com/t5/Security/Calculate-total-bps-from-NetFlow-data/m-p/415595#M13454to4kawa2020-03-02T21:21:41Z