IP accounting using pmacct and NetFlow – 1

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 is simple, as usual, ./configure and make install, but following packages must installed before compilation. (My server is Ubuntu Server 12.10).


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]
 --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
 --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
 --sysconfdir=DIR read-only single-machine data in DIR [PREFIX/etc]
 --sharedstatedir=DIR modifiable architecture-independent data in DIR
 --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
 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.



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,
ip_proto CHAR(6) 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

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 |
| | 344119 |
| | 422112 |
| | 415405 |
| | 363630 |
| | 329945 |
| | 364246 |
| | 340761 |
| | 370787 |
| | 356685 |
| | 346626 |
| | 398860 |
| | 369996 |
| | 393748 |
1000 rows in set (0.01 sec)


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…..

7 thoughts on “IP accounting using pmacct and NetFlow – 1

  1. Hi,
    I followed the steps above and found that ip_dst column shows as and ip_proto shows “ip”. Can you let me know what will be the configuration for fetch the above data.

    • Can you show me your configuration? the key part is the /etc/nfacctd.networks contain all your subnet in order to filter out all unnecessary broadcast.

      Following are the working configuration of mine,
      pidfile: /var/run/nfacctd.intl.pid
      plugins: mysql[intl_in],mysql[intl_out]
      sql_db: pmacct
      sql_host: localhost
      sql_user: root
      sql_passwd: {secret}
      aggregate[intl_in]: dst_host, in_iface, out_iface
      aggregate[intl_out]: src_host, in_iface, out_iface
      nfacctd_ip: {my ip to receive data}
      nfacctd_port: {my server port}
      sql_refresh_time: 300
      sql_history: 5m
      sql_history_roundoff: mh
      sql_table_version: 5
      sql_table[intl_in]: mx80_INTL_in
      sql_table[intl_out]: mx80_INTL_out
      networks_file_filter: true

  2. Hey thank you for such a straightforward guide. I’ve got this running nicely in a proxmox container with MariaDB in under 30 minutes.

  3. Hello everyone
    I am working on my end of study project, I would like to collect BMP (Protocol Monnitoring BGP) data
    Here is the content of my pmbmp.conf file

    daemonize: true
    pidfile: /var/pmbmpd.pid
    aggregate: src_host, as_path, peer_src_ip, peer_dst_ip, peer_src_as, peer_dst_as, local_pref, sum_net, sum_as
    pcap_filter: net
    interface: lo
    plugins: mysql
    sql_host: localhost
    sql_passwd: <pmacct-create-db_bgp_v1.mysql
    sql_history: 1h
    sql_history_roundoff: mhd
    sql_table_version: 1

    I would like to know:
    1- how to recover BMP data
    2- how to feed the database (mysql) with BMP data

    thank you in advance for your help
    I am in a hurry so that I can move forward thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *