Traffic Accountin

v0.2

Jelikoz statistiky Sosacu byli po padu iGW - Oriona zruseny/vypnuty/znova nenahozeny: http://charon.hkfree.org/~pavkriz/hkfree/stat.php

tak jsem se rozhodl ze bych si neco podobneho mohl pustit na APckach abych mel prehled kdo jak moc sosa. Sic by nekdo mohl namitnout, ze muzu pouzit RRD Tools, ale ty zatezujou system opravdu neskutecnym zpusobem, coz je neprijatelne a tak se to pokusim udelat jinak s tim ze to sice nebude graficky zobrazovano pres hotsanic (coz me netrapi) ale bude se to tahat z MySQL databaze.

---

Prvni cast scriptu, kterou jsem za asistence Coudka dal do kupy, je tato:

## Pokud vam bezi take na AP shaper, tak to musiste tahat odsud

iptables -nL -t mangle -vx | grep eth1 | grep all | awk '{ if ($3 !="IMQ") {print $2 " " $8 " " $9}}' | cat > /root/data-frafic/users-data.tmp

## Pak se to tridi na download a upload

cat /root/data-frafic/users-data.tmp | awk '{ if ($2 !="0.0.0.0/0") {print $1 " " $2}}' | cat > /root/data-frafic/users-data.upload cat /root/data-frafic/users-data.tmp | awk '{ if ($3 !="0.0.0.0/0") {print $1 " " $3}}' | cat > /root/data-frafic/users-data.download

## Prevod do formatu MySQL

cat /root/data-frafic/users-data.upload | awk '{print "INSERT INTO `upload` ( `bytes` , `IP` ) VALUES ( '"'"'"$1"'"'"' , '"'"'"$2"'"'"' )" ";" }' | cat > /root/data-frafic/mysql-upload.sql cat /root/data-frafic/users-data.download | awk '{print "INSERT INTO `download` ( `bytes` , `IP` ) VALUES ( '"'"'"$1"'"'"' , '"'"'"$2"'"'"' )" ";" }' | cat > /root/data-frafic/mysql-download.sql

## Upload do MySQL databaze

mysql -u uzivatel_db --password=heslo_do_db -D databaze < /root/data-frafic/mysql-upload.sql mysql -u uzivatel_db --password=heslo_do_db -D databaze < /root/data-frafic/mysql-download.sql

---

Pokud by mel nekdo napad jak cokoliv vylepsit, zjednodusit ci zprehlednit necht klidne da vedet treba na newsech

---

Co je potreba dodelat

1. Pricitani hodnot ke stavajicim aby tabulka v MySQL nemela behem dne milion radku
2. Ukladani hodnot do nejakyho externiho souboru aby v pripade rebootu masiny, ci jen vymazani a znovu nahrani iptables pocitadlo nepocitalo odzacatku

v1.0b

 #!/bin/bash
 dir_data_txt="/opt/ipaccounting"
 dir_data_rrd="/opt/ipaccounting/rrd"
 rrd_active="on"
 txt_active="on"
 # Nacteni dat z IPTABLES
 counter_file=`cat $dir_data_txt/data.txt `
 data=`iptables -t mangle -L unishaper_up -v -x -n -Z | grep all | awk '{ if ($1 !="0") {print }}' |  awk '{ if ($2 !="0") {print}}' | sed -e 's/ /-/g' | grep MARK`
 for i in $data; do
   ip=`echo $i | sed -e 's/-/ /g' | awk '{print $7}' `
   out=`echo $i | sed -e 's/-/ /g' | awk '{print $2}' `
   out_old=`echo -ne "$counter_file"  | grep "$ip-" | sed -e 's/-/ /g' | awk '{print $3}' `
   out_new=$(($out_old + $out))
   new_file_out="$new_file_out$ip-$out_new\n"
   out_aver=$(($out / 120))
   new_rrd_out="$new_rrd_out$ip-$out_aver\n"
   echo -ne "$ip\tOUT\tstare: $out_old \tnove: $out \tsoucet: $out_new\trate: $out_aver\n"
 done
 data=`iptables -t mangle -L unishaper_down -v -x -n -Z | grep all | awk '{ if ($1 !="0") {print }}' |  awk '{ if ($2 !="0") {print}}' | sed -e 's/ /-/g' | grep MARK`
 for i in $data; do
   ip=`echo $i | sed -e 's/-/ /g' | awk '{print $8}'`
   in=`echo $i | sed -e 's/-/ /g' | awk '{print $2}'`
   in_old=`echo -ne "$counter_file"  | grep "$ip-" | sed -e 's/-/ /g' | awk '{print $2}'`
   in_new=$(($in_old + $in))
   new_file_in="$new_file_in$ip-$in_new\n"
   in_aver=$(($in / 120))
   new_rrd_in="$new_rrd_in$ip-$in_aver\n"
   echo -ne "$ip\tIN\tstare: $in_old \tnove: $in \tsoucet: $in_new\trate: $in_aver\n"
 done
 # Zapsani hodnot do txt souboru (pocitadlo prenesenych dat)
 if [ $txt_active == "on" ]; then
   for i in `echo -ne $new_file_out`; do
       ip=`echo $i | sed -e 's/-/ /g' | awk '{print $1}'`
       out=`echo $i | sed -e 's/-/ /g' | awk '{print $2}'`
       in=`echo -ne "$new_file_in" | grep "$ip-" | sed -e 's/-/ /g' | awk '{print $2}'`
       counter_data="$counter_data$ip-$out-$in\n"
   done
   echo -ne $counter_data > $dir_data_txt/data.txt
 fi
 # Zapsani hodnot do rrd databaze (grafy prenosu)
 if [ $rrd_active == "on" ]; then
   for i in `echo -ne $new_rrd_out`; do
       ip=`echo $i | sed -e 's/-/ /g' | awk '{print $1}'`
       out=`echo $i | sed -e 's/-/ /g' | awk '{print $2}'`
       in=`echo -ne "$new_rrd_in" | grep "$ip-" | sed -e 's/-/ /g' | awk '{print $2}'`
           if [ ! -e "$dir_data_rrd/host-$ip.rrd" ]; then
               rrdtool create "$dir_data_rrd/host-$ip.rrd" --step 120 DS:in:GAUGE:600:0:U DS:out:GAUGE:600:0:URRA:AVERAGE:0.5:1:3600 RRA:AVERAGE:0.5:6:3600 RRA:AVERAGE:0.5:42:3600;
           fi
           rrdtool update "$dir_data_rrd/host-$ip.rrd" -t in:out N:$in:$out
   done
 fi