Praćenje potrošnje prometa po IP adresi

Code:
DEVICE rl0
LOG 192.168.0.0/255.255.255.0
FILENAME "/var/log/ipfm/ipfm.%Y.%m.%d.%H.%M"
DUMP EVERY 30 minutes
CLEAR ALWAYS
SORT TOTAL 
NOPROMISC
NORESOLVE

Code:

#!/usr/local/bin/bash

getMAC () {
    # na bazi IP vracam MAC 
    tmp=`arp -na|fgrep "$1)"`
    echo $tmp | awk '{ print $4 }'
}

cd /home/some_user/backup/
/usr/local/bin/nmap -sP 192.168.0.0/24 1>/dev/null

for INPUT_FILE in `ls /var/log/ipfm/`
do
    if [ -e "out.tmp" ]
    then
        rm out.tmp
    fi
    
    if [ -e "nohead.tmp" ]
    then
        rm nohead.tmp
    fi
    
    echo $INPUT_FILE
    YEAR=`echo $INPUT_FILE | cut -d "." -f2`
    MONTH=`echo $INPUT_FILE | cut -d "." -f3`
    DAY=`echo $INPUT_FILE | cut -d "." -f4`
    HOUR=`echo $INPUT_FILE | cut -d "." -f5`
    MIN=`echo $INPUT_FILE | cut -d "." -f6`

    LIST=`cat "/var/log/ipfm/$INPUT_FILE" | fgrep 192.168.0 | awk '{ print $1}'` 
    cat "/var/log/ipfm/$INPUT_FILE" | fgrep 192.168.0 > nohead.tmp

    echo "use ipfm;" >> out.tmp

    for IP in $LIST
    do
        echo -n "INSERT INTO ip_counters (ip,mac,name,year,month,day,hour,min,download,upload,total) VALUES " >> out.tmp
        echo -n "('$IP', " >> out.tmp
        MAC=`getMAC $IP`
        if [ -z $MAC ]
        then
            MAC="noMAC"
        fi
                if [ $IP = "192.168.0.2" ]
                then
                        MAC="00:00:B4:91:E2:DA"
                fi
                if [ $IP = "192.168.0.255" ]
                then
                        MAC="FF:FF:FF:FF:FF:FF"
                fi
                if [ $IP = "192.168.0.0" ]
                then
                        MAC="00:00:00:00:00:00"
                fi
        echo -n "'$MAC', " >>  out.tmp 
        NAME=`cat macs.txt | grep -i "$MAC" | cut -d " " -f2`
        if [ -z $NAME ]
        then
            NAME="NO_NAME"
        fi
        echo -n "'$NAME', " >> out.tmp
        echo -n "$YEAR, " >> out.tmp
        echo -n "$MONTH, " >> out.tmp
        echo -n "$DAY, " >> out.tmp     
        echo -n "$HOUR, " >> out.tmp
        echo -n "$MIN, " >> out.tmp
        IN_BYTES=`cat nohead.tmp | fgrep $IP\  | awk '{ print $2}'`
        echo -n "$IN_BYTES, " >> out.tmp
        OUT_BYTES=`cat nohead.tmp | fgrep $IP\  | awk '{ print $3}'`
        echo -n "$OUT_BYTES, " >> out.tmp
        TOTAL_BYTES=`cat nohead.tmp | fgrep $IP\  | awk '{ print $4}'`
        echo "$TOTAL_BYTES);" >> out.tmp
    done
    
    mv out.tmp data.$YEAR.$MONTH.$DAY.$HOUR.$MIN.sql
    rm nohead.tmp
    
    /usr/local/bin/mysql -u db_username --password=password -h db_server.example.com < data.$YEAR.$MONTH.$DAY.$HOUR.$MIN.sql > data.$YEAR.$MONTH.$DAY.$HOUR.$MIN.result

rm "/var/log/ipfm/$INPUT_FILE"
rm data.$YEAR.$MONTH.$DAY.$HOUR.$MIN.sql
rm data.$YEAR.$MONTH.$DAY.$HOUR.$MIN.result

done

Code:

#!/usr/local/bin/bash
##################################################################
#
#
##################################################################


MAILTO='[email protected]'

DAY=`date "+%d"`
MONTH=`date "+%m"`
YEAR=`date "+%Y"`

cd /home/some_user/backup

if [ -e "sqlquery.txt" ]
then
    rm sqlquery.txt
fi 

echo "use ipfm;" > sqlquery.txt
echo "select name, sum(download)/1048576 as Down_MB, sum(upload)/1048576 as Up_MB  from ip_counters where day=${DAY} and month=${MONTH} and year=${YEAR} group by mac order by Down_MB desc;" >> sqlquery.txt

/usr/local/bin/mysql -u db_username --password=password -h db_server.example.com < sqlquery.txt > report

rm sqlquery.txt

sendmail -t << EOM
To: ${MAILTO}
From: [email protected]
Subject: Daily report `date "+%d.%m.%Y."`

`cat report`

EOM