I have several subnet need ip accounting for billing network traffic consumption. Since we have Netflow setup in our network, it would be easy to reuse those information collected.

I’ve found pmacct (Official website: http://www.pmacct.net/) can read Netflow data and store into MySQL, Nice!!!!

What is it?
pmacct is a small set of passive network monitoring tools to measure, account, classify,aggregate and export IPv4 and IPv6 traffic; its main features are:

• Suitable to ISP, IXP, CDN, IP carrier, data-centre and hot-spots enviroments
• Runs on Linux, BSDs, Solaris and embedded systems
• Support for both IPv4 and IPv6
• Collects data through libpcap, Netlink/ULOG, NetFlow v1/v5/v7/v8/v9, sFlow v2/v4/v5 and IPFIX
• Saves data to a number of backends including memory tables, MySQL, PostgreSQL, SQLite, BerkeleyDB and flat files
• Exports data to remote collectors through IPFIX, NetFlow v5/v9 and sFlow v5
• Replicates incoming IPFIX, NetFlow and sFlow packets to remote collectors
• Flexible architecture to tag, filter, redirect, aggregate and split captured data
• Embeds a BGP daemon for efficient visibility into the inter-domain routing plane. Read more here.
• Embeds an IS-IS/IGP daemon for visibility of internal routes. (from 0.14.0)
• Supports BGP/MPLS VPNs rfc4364 (from 0.14.0rc3)
• Traffic streams classification. Read more here
• Support for packet and flow sampling and renormalization
• Inspection of tunnelled traffic (ie. GTP)
• Pluggable architecture for easy integration of new capturing environments and data backends
• Careful SQL support: data pre-processing, triggers, dynamic table naming
• It’s free, open-source, developed and supported with passion and open mind

Whether using memory or SQL tables as backend storage, pmacct can easily feed data into external tools including RRDtool, GNUPlot, Net-SNMP, MRTG and Cacti among the others. Little scripting abilities are required and a number of sample scripts, contributions, web frontends and some tutorials are already available.

Installation
Installation is simple, as usual, ./configure and make install, but following packages must installed before compilation. (My server is Ubuntu Server 12.10).

 gcc
 libpcap0.8-dev
 libmysqlclient-dev

Download source from here.

There are some option need to specify, in my case I need compile multi-threading, MySQL plugin and enable 64bit counters.

$ tar zxf pmacct-0.14.1.tar.gz
$ cd pmacct-0.14.1
$ ./configure --enable-64bit --enable-mysql --enable-threads
$ make
$ make install

Find more configuration options that available to fit your case.

$ ./configure --help
 Usage: configure [options] [host]
 Options: [defaults in brackets after descriptions]
 Configuration:
 --cache-file=FILE cache test results in FILE
 --help print this message
 --no-create do not create output files
 --quiet, --silent do not print `checking...' messages
 --version print the version of autoconf that created configure
 Directory and file names:
 --prefix=PREFIX install architecture-independent files in PREFIX
 [/usr/local]
 --exec-prefix=EPREFIX install architecture-dependent files in EPREFIX
 [same as prefix]
 --bindir=DIR user executables in DIR [EPREFIX/bin]
 --sbindir=DIR system admin executables in DIR [EPREFIX/sbin]
 --libexecdir=DIR program executables in DIR [EPREFIX/libexec]
 --datadir=DIR read-only architecture-independent data in DIR
 [PREFIX/share]
 --sysconfdir=DIR read-only single-machine data in DIR [PREFIX/etc]
 --sharedstatedir=DIR modifiable architecture-independent data in DIR
 [PREFIX/com]
 --localstatedir=DIR modifiable single-machine data in DIR [PREFIX/var]
 --libdir=DIR object code libraries in DIR [EPREFIX/lib]
 --includedir=DIR C header files in DIR [PREFIX/include]
 --oldincludedir=DIR C header files for non-gcc in DIR [/usr/include]
 --infodir=DIR info documentation in DIR [PREFIX/info]
 --mandir=DIR man documentation in DIR [PREFIX/man]
 --srcdir=DIR find the sources in DIR [configure dir or ..]
 --program-prefix=PREFIX prepend PREFIX to installed program names
 --program-suffix=SUFFIX append SUFFIX to installed program names
 --program-transform-name=PROGRAM
 run sed PROGRAM on installed program names
 Host type:
 --build=BUILD configure for building on BUILD [BUILD=HOST]
 --host=HOST configure for HOST [guessed]
 --target=TARGET configure for TARGET [TARGET=HOST]
 Features and packages:
 --disable-FEATURE do not include FEATURE (same as --enable-FEATURE=no)
 --enable-FEATURE[=ARG] include FEATURE [ARG=yes]
 --with-PACKAGE[=ARG] use PACKAGE [ARG=yes]
 --without-PACKAGE do not use PACKAGE (same as --with-PACKAGE=no)
 --x-includes=DIR X include files are in DIR
 --x-libraries=DIR X library files are in DIR
 --enable and --with options recognized:
 --enable-debug enable debugging compiler options
 --enable-relax relax compiler optimization
 --disable-l2 disable Layer-2 features and support
 --enable-ipv6 Enable IPv6 code
 --enable-v4-mapped allow IPv6 sockets to handle IPv4 connections
 --with-pcap-includes=DIR Search the specified directories for header files
 --with-pcap-libs=DIR Search the specified directories for libraries
 --enable-mysql Enable MySQL support
 --with-mysql-libs=DIR Search for MySQL libs in the specified directory
 --with-mysql-includes=DIR Search for MySQL includes in the specified directory
 --enable-pgsql Enable PostgreSQL support
 --with-pgsql-libs=DIR Search for PostgreSQL libs in the specified directory
 --with-pgsql-includes=DIR Search for PostgreSQL includes in the specified directory
 --enable-sqlite3 Enable SQLite3 support
 --with-sqlite3-libs=DIR Search for SQLite3 libs in the specified directory
 --with-sqlite3-includes=DIR Search for SQLite3 includes in the specified directory
 --disable-so Disable shared objects
 --enable-64bit Enable 64bit counters
 --enable-threads Enable multi-threading in pmacctd
 --enable-ulog Enable ULOG support

You’ll got several binary files installed after make successful, Following are the official descriptions.

pmacctd: libpcap-based accounting daemon; it captures packets from an interface it is bound to. Statistics can be printed to stdout, stored in memory tables or a PostgreSQL/MySQL/SQLite/Berkeley DB(*) database, exported via NetFlow, IPFIX or sFlow protocols.

nfacctd: NetFlow accounting daemon; it listens for NetFlow packets v1/v5/v7/v8/v9 and IPFIX on one or more interfaces (IPv4 and IPv6); statistics can be printed to stdout, stored in memory tables or a PostgreSQL/MySQL/SQLite/BerkeleyDB(*) database,
replicated to other collectors.
sfacctd:  sFlow accounting daemon; it listens for sFlow packets v2, v4 and v5 on one or more interfaces (both IPv4 and IPv6); statistics can be printed to stdout, stored in memory tables or a PostgreSQL/MySQL/SQLite/BerkeleyDB(*) database, replicated
to other collectors.
uacctd:  Linux Netlink ULOG accounting daemon; it captures packets by leveraging a ULOG multicast group – and works only on Linux; Statistics can be printed to stdout, stored in memory tables or a PostgreSQL/MySQL/SQLite/BerkeleyDB(*) database, exported via NetFlow, IPFIX or sFlow protocols.

pmacct: commandline pmacct client; used to retrieve data from a memory plugin; it can execute both partial and full data retrieval. Output is either formatted or ‘counters-only’,
suitable for data injection in tools like MRTG, RRDtool, Gnuplot or SNMP server among the others.

pmmyplay: pmacct MySQL logfile player; it plays logfiles previously
generated by a MySQL plugin.

pmpgplay: pmacct PgSQL logfile player; it plays logfiles previously
generated by a PostgreSQL plugin.

 

Configuration

My first attempt, I use the schema in sql/pmacct-create-db_v1.mysql

drop database if exists pmacct;
create database pmacct;
use pmacct;
drop table if exists acct;
create table acct (
mac_src CHAR(17) NOT NULL,
mac_dst CHAR(17) NOT NULL,
ip_src CHAR(15) NOT NULL,
ip_dst CHAR(15) NOT NULL,
src_port INT(2) UNSIGNED NOT NULL,
dst_port INT(2) UNSIGNED NOT NULL,
ip_proto CHAR(6) NOT NULL,
packets INT UNSIGNED NOT NULL,
bytes BIGINT UNSIGNED NOT NULL,
stamp_inserted DATETIME NOT NULL,
stamp_updated DATETIME,
PRIMARY KEY (mac_src, mac_dst, ip_src, ip_dst, src_port, dst_port, ip_proto, stamp_inserted)
);

and edit config file /etc/nfacctd.conf, I use nfacctd because this is specific daemon to receive Netflow data.

$ cat /etc/nfacctd.conf
daemonize: true
pidfile: /var/run/nfacctd.pid
plugins: mysql
sql_db: pmacct
sql_host: localhost
sql_user: root
sql_passwd: xxxxxxxxxxxx
aggregate: sum_host
nfacctd_port: 5678
sql_refresh_time: 120
sql_history: 10m
sql_history_roundoff: mh
sql_table_version: 1

and then fire it up!

$ nfacctd -f /etc/nfacctd.conf

Wait for a while and found……………..What a mess~!!! I don’t need all IPs, just need the subnet I that need to account.

After check all the parameters in official documents, I’ve found a solution, by additional config file /etc/nfacctd.networks (actually, any filenames are ok, just make the file be much easy to identify)

$ cat /etc/nfacctd.networks
111.222.1.0/24
111.222.2.0/24
111.222.3.0/24

and append below line to config file /etc/nfacctd.conf.

networks_file: /etc/nfacctd.networks

We don’t have initscript to stop the daemon, so simply kill it and start again.

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

$ mysql pmacct
mysql> select ip_src,bytes from acct_v1;
+----------------+-------------+
| ip_src | bytes |
+----------------+-------------+
| 111.222.1.2 | 344119 |
| 111.222.1.2 | 422112 |
| 111.222.1.3 | 415405 |
| 111.222.1.3 | 363630 |
| 111.222.1.5 | 329945 |
| 111.222.1.5 | 364246 |
| 111.222.1.6 | 340761 |
| 111.222.1.6 | 370787 |
| 111.222.2.3 | 356685 |
| 111.222.2.3 | 346626 |
| 111.222.2.6 | 398860 |
| 111.222.2.6 | 369996 |
| 111.222.2.9 | 393748 |
....blahblahblah
+----------------+-------------+
1000 rows in set (0.01 sec)

mysql>

Aha! I got the correct data, ip and sum of in+out traffic. But my boss tell me that he want to separate in and out traffic…..