After successfully capture Netflow data, my boss remind me to split IN and OUT traffic data…(read previous story)

Lets find answer from official documents …read~ read~ read~

Oh…the first sample..aggregate can be multiple exists in following style.

! A sample config file.
daemonize:true
aggregate[inbound]: dst_host
aggregate[outbound]: src_host
aggregate_filter[inbound]: dst net 192.168.0.0/16
aggregate_filter[outbound]: src net 192.168.0.0/16
plugins: memory[inbound], memory[outbound]

So, I change my config file and database schema…

$cat /etc/nfacctd.conf
daemonize: true
pidfile: /var/run/nfacctd.pid
plugins: mysql[inbound],mysql[outbound]
sql_db: pmacct
sql_host: localhost
sql_user: root
sql_passwd: xxxxxxxx
aggregate[inbound]: dst_host
aggregate[outbound]: src_host
nfacctd_port: 5678
sql_refresh_time: 120
sql_history: 10m
sql_history_roundoff: mh
sql_table_version: 1
sql_table[inbound]: acct_v1_in
sql_table[outbound]: acct_v1_out
networks_file: /etc/nfacctd.networks

I add two handle in mysql, inbound & outbound respectively. and sql table also need to split in&outbound.

I’m so lazy, just copy the database table that previously created.

$ mysql pmacct
mysql> create table acct_v1_in as select * from acct_v1 limit 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table acct_v1_in add PRIMARY KEY (`mac_src`, `mac_dst`, `ip_src`, `ip_dst`, `src_port`, `dst_port`, `ip_proto`, `stamp_inserted`);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table acct_v1_out as select * from acct_v1 limit 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table acct_v1_out add PRIMARY KEY (`mac_src`, `mac_dst`, `ip_src`, `ip_dst`, `src_port`, `dst_port`, `ip_proto`, `stamp_inserted`);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> truncate table acct_v1_in;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table acct_v1_out;
Query OK, 0 rows affected (0.00 sec)

and then fire it up again!

$ kill -INT `cat /var/run/nfacctd.pid`
$ nfacctd -f /etc/nfacctd.conf

And now I can get total in and out traffic per IP.

$ mysql pmacct
mysql> select ip_dst,sum(bytes) from acct_v1_in group by ip_dst;
mysql> select ip_src,sum(bytes) from acct_v1_out group by ip_src;

or hourly traffic on specific IP.

mysql> select date_format(stamp_inserted,'%Y%m%d%H'),sum(bytes/1000) from acct_v1_in where ip_dst in ('111.222.2.6') group by date_format(stamp_inserted,'%Y%m%d%H');
mysql> select date_format(stamp_inserted,'%Y%m%d%H'),sum(bytes/1000) from acct_v1_out where ip_src in ('111.222.2.6') group by date_format(stamp_inserted,'%Y%m%d%H');

Next, let developer to make the billing system. Cheer~!