gistfile1.txt

#
# Background: this was done on a CentOS 6.5 ESXi VM running pmacct 1.5.0rc2 and PostgreSQL 9.3 from PGDG repo with 
# the following config line for pmacct:
#
# ./configure  --enable-pgsql --enable-geoip --enable-ipv6 --with-pgsql-libs=/usr/pgsql-9.3/lib --with-pgsql-includes=/usr/pgsql-9.3/include
#
#
# I also customized the table they used in postgres to add vertical partitioning, but that's another gist :)
#

#may need other deps, this was not a completely clean install of centos
yum -y install bzip2-devel wget

wget -qO - https://bitbucket.org/ripencc/bgpdump/get/tip.tar.gz | tar zxv
 
cd ripencc-bgpdump*
sh bootstrap.sh && make && make install

mkdir /tmp/work ; cd /tmp/work

# Replace with a different date, unless you always want the routing table from Feburary 2014 :)
# You can name these whatever you want, but the .bz2 extension is important

wget http://archive.routeviews.org/bgpdata/2014.02/RIBS/rib.20140214.1400.bz2
wget -O rib6.bz2 http://archive.routeviews.org/route-views6/bgpdata/2014.02/RIBS/rib.20140214.1400.bz2

# Yeah, yeah, laugh, I'm no awk guru :P
bgpdump -m rib.20140214.1400.bz2 | awk -F "|" '{print $6 " " $7}' | awk '{print $NF "," $1}' | sort | uniq > /tmp/bgpdump
bgpdump -m rib6.bz2 | awk -F "|" '{print $6 " " $7}' | awk '{print $NF "," $1}' | sort | uniq >> /tmp/bgpdump

mv /tmp/bgpdump /usr/share/as_origins.lst


# Add these lines to your conf file for pmacctd/nfacctd/sfacctd

!
pmacctd_as: true
nfacctd_as_new: true
sfacctd_as_new: true
networks_file: /usr/share/as_origins.lst
snaplen: 700
!


# Extra credit: add the long ASN names to postgresql
#
# There are a lot of programmatic ways to 'remove the boilerplate of HTML'
#
# I just went to http://bgp.potaroo.net/cidr/autnums.html in firefox and copied and pasted
# it into /tmp/asnameraw.tsv in PuTTY and it "just worked". It did take 5 minutes to finish pasting though.
#
# I grep off the asdot networks for now. In the future I'll use http://search.cpan.org/~dfreedman/Net-ASN-1.06/lib/Net/ASN.pm
# or something similar to convert
#

cat /tmp/asnameraw.tsv | sed s/^AS// | grep -E '^[0-9]{1,6}' | grep -vE '^[0-9]\.' > /tmp/asnames.tsv

# I setup my postgres as UTF8. YMMV

iconv -f utf-8 -t utf-8 -c /tmp/asnames.tsv > /tmp/asnames_u8.tsv

# Must be pgsql superuser for copy
su - postgres
psql -d pmacct
pmacct=> create table asn_info(as_number int unique primary key, as_name text);
pmacct=# copy asn_info from '/tmp/asnames_u8.tsv';
COPY 58809

# Then you can run something like:

pmacct=# select sum(bytes) as xfer,as_dst,max(as_name) from acct_v7 left join asn_info on (as_dst=as_number) group by as_dst order by xfer desc;
   xfer   | as_dst |                          max
----------+--------+-------------------------------------------------------
 14929216 |  33668 | CMCS - Comcast Cable Communications, Inc.
  1434873 |      0 |     -Reserved AS-
   355628 |  14618 | AMAZON-AES - Amazon.com, Inc.
   104112 |   7922 |  COMCAST-7922 - Comcast Cable Communications, Inc.
   101298 |  36692 | OPENDNS - OpenDNS, LLC
    63823 |  16509 | AMAZON-02 - Amazon.com, Inc.