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