Hi All

After a couple of attempts, I have finally found a simple traffic counting solution. Basically, I count the traffic between the internet and each internal lan ip, and save it to a database running on another machine big_smile

I add some rules into iptables - two for each internal ip - one for each direction. I added these rules into /etc/firewall.user :

iptables -t mangle -A FORWARD -s 192.168.0.2
iptables -t mangle -A FORWARD -d 192.168.0.2

iptables -t mangle -A FORWARD -s 192.168.0.3
iptables -t mangle -A FORWARD -d 192.168.0.3

iptables -t mangle -A FORWARD -d 192.168.0.4
iptables -t mangle -A FORWARD -s 192.168.0.4

Then, I use a script to extract the iptables counter information, and insert it into a database (I use postgres). This is run by a cron job every 5 minutes : 

#!/bin/sh

# get the data, and zero the counters
iptables -nvx -t mangle -L -Z FORWARD | grep "all" > /tmp/datadump



cat /tmp/datadump | while read line;
do
        # work out the direction
        seventhfield=$(echo "${line}" | awk '{print $7}')
        if [ $seventhfield != "0.0.0.0/0" ]; then
                #the direction is outbound from the ip in the seventh field
                direction='out'
                ip=$seventhfield
        fi

        eighthfield=$(echo "${line}" | awk '{print $8}')
        if [ $eighthfield != "0.0.0.0/0" ]; then
                #the direction is inbound to the ip in the eighth field
                direction='in'
                ip=$eighthfield
        fi

        #work out the bytes
        bytes=$(echo "${line}" | awk '{print $2}')

        statement="insert into dump (bytes, direction, ip) values ($bytes,'$direction','$ip')"
        #echo $statement
        psql -h 192.168.0.3 -U postgres -c "$statement" traffic
done

and the cronjob in /etc/crontabs/root

*/5 * * * * /bin/processtraffic > /tmp/ptlog

That's all for the wrt side.

Now, on my other machine which is running the database, I used this table definition : 

create sequence dump_sequence;

create table dump(
  id int not null default nextval('dump_sequence') primary key,
  time timestamp default current_timestamp,
  bytes int,
  ip varchar(15),
  direction varchar(3));

I have a simple cgi script that pulls the data and displays it in a browser. psql has a neato option to spit out the result set in html big_smile The cgi script has input fields so that one can enter a date range as well.

#!/bin/bash
echo Content-type: text/html
echo
echo "<html><head></head><body>"

DATEFROM=`echo "$QUERY_STRING" | sed -n 's/^.*datefrom=\([^&]*\).*$/\1/p' | sed "s/%20/ /g"`
DATETO=`echo "$QUERY_STRING" | sed -n 's/^.*dateto=\([^&]*\).*$/\1/p' | sed "s/%20/ /g"`

echo "<form method=get>"
echo "DATEFROM : <input type=text name=datefrom> (dd-mm-yyyy)<br>"
echo "DATETO : <input type=text name=dateto> (dd-mm-yyyy) <br>"
echo " <input type=submit>"
echo "</form>"
# to_timestamp('14-12-2007 23:59','DD-MM-YYYY HH:MM')


if [ $DATEFROM ] && [ $DATETO ]
then
        echo "datefrom: $DATEFROM <br>"
        echo "dateto: $DATETO <br>"
        echo "<p>Usage from $DATEFROM until $DATETO:</p>"
        /usr/bin/psql -h 192.168.0.3 -U postgres -c "select sum(bytes) as bytes, sum(bytes)/1048576 as megabytes,direction,ip from dump where time >= to_timestamp('$DATEFROM 00:00','DD-MM-YYYY HH:MM') and time <= to_timestamp('$DATETO 23:59','DD-MM-YYYY HH:MM') group by direction,ip order by ip desc" -H traffic

else
        echo "<p>Usage since I started to measure it :</p>"
        /usr/bin/psql -h 192.168.0.3 -U postgres -c "select sum(bytes) as bytes, sum(bytes)/1048576 as megabytes,direction,ip from dump group by direction,ip order by ip desc" -H traffic
fi

echo "hosts:<br>"

echo "192.168.0.2 : "
host 192.168.0.2 | awk '{print $5}'
echo "<br>"

echo "192.168.0.3 : "
host 192.168.0.3 | awk '{print $5}'
echo "<br>"

echo "192.168.0.4 : "
host 192.168.0.4 | awk '{print $5}'
echo "<br>"

echo "</body></html>"

This little scheme does have a couple of problems how ever.

1: The ip's are hardcoded.
I use dhcp to give out the ips by mac address, so I know which computer is which, and its unlikely that another computer will join often, but still it offends my inner programmer.... I have to store them in the firewall.user file, and in the HTML if I want the host name to show! I could perform a host lookup on the ip, and insert the hostname into the database along with the ip and traiific information maybe....
I could maybe manage the iptables rules by somehow adding when a new dhcp lease is handed out, or expires, but I am unsure how to do that. It would increase maintainability though big_smile

2: firewall.user does not appear to get run on reboot.
The last time I tried this, when I rebooted the router the rules where not present in iptables!

3: Kernel memory usage per rule, and performance.
I simply don't know how many rules is too many - is it 10, is it 100? Will 10 rules add latency? I haven't noticed any....

4: The cgi script is vulnerable to sql injection.
One could enter 

; drop table dump ;

into either of the input fields on the web page. I am a little too lazy to fix this, but I think a simple regex stripping out any ";" characters would do it.

5: The 5 minute interval could be too short.
Hopefully, even at full throttle, the 5 minute cron job is long enough to capture the counter data before the counters reset to zero.

So yeah. Any ideas on how to make any of this a bit more graceful? Probably the ip management and capture script needs the most work! But, for a small home network in my flat, I think its fine smile