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~!
hi
really helpfull,tnx for sharing us