Opsætning af pmacct på debian

pmacct og mysql installeres via apt.
i mysql oprettes databasen pmacct og brugeren pmacct@localhost med koden pmacct:
  
  create database pmacct;
  grant all privileges on pmacct.* to pmacct@localhost identified by 'pmacct';

følgende config bruges: (/etc/pmacct/pmacctd.conf)

! pmacctd configuration
!
!
!
daemonize: true
pidfile: /var/run/pmacctd.pid
syslog: daemon
interface: eth1

aggregate[inbound]: dst_host
aggregate[outbound]: src_host
aggregate_filter[inbound]: dst net 217.195.176.0/24
aggregate_filter[outbound]: src net 217.195.176.0/24
plugins: mysql[inbound], mysql[outbound]
sql_table[inbound]: acct_in
sql_table[outbound]: acct_out
sql_table_version[inbound]: 1
sql_table_version[outbound]: 1

sql_host: localhost
sql_db: pmacct
sql_user: pmacct
sql_passwd: pmacct
sql_refresh_time: 60
sql_history: 1h
sql_history_roundoff: mh


Følgende sql script bruges til at oprette tabeller: 
(vim /usr/share/doc/pmacct/sql/pmacct-create-db_v1_in-out.mysql)

use pmacct;

drop table if exists acct_in;
create table acct_in (
        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)
);
drop table if exists acct_out;
create table acct_out (
        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)
);


Start pmacctd og lad den guffe traffik:

/etc/init.d/pmacct start

Optimer udlæsning af traffikken lidt:

CREATE VIEW vTraffic AS SELECT acct_in.ip_dst AS ip, acct_in.stamp_inserted AS Time, acct_out.bytes AS Sent, acct_in.bytes AS Recieved FROM acct_in, acct_out WHERE acct_in.ip_dst = acct_out.ip_src AND acct_in.stamp_inserted = acct_out.stamp_inserted ORDER BY Time, inet_aton(ip);

(Der burde være brugt outer join, da hosts som kun sender eller modtager i en given time ikke bliver målt. I praksis ser jeg bort fra dette, da vi nok ikke har den slags hosts overhovedet?)