Citat:
--ja--: Vezu ip-osoba već imam (treba još neke dijelove iskodirati ali više-manje je gotovo). Neću podatke ubacivati na NAT mašini u bazu, već će se u nekim vremenskim intervalima raditi upit na php aplikaciju na udaljenom računalu i tako slati podatke (vjerojatno najjednostavnije putem GET requesta), a php aplikacija će raditi izračun prometa (php aplikacija ima način da sazna koji ip kome pripada u danom trenutku).
Zanimao bi me eventualno skript koji vadi podatke iz ipfm-a, da ne izmišljam toplu vodu ako već ima odrađeno...
Hvala!
Mapiranje IP-osoba nije mozda uvek tacno, jer user-i mogu da menjaju rucno IP adresu, ali kao pocetno resenje moze da posluzi.
Ne moras ubaciti podatke u bazu na NAT mashini, nego upisujes u bazu na drugom (DB) serveru.
Uspeo sam da pronadjem jedan script za OpenBSD, ali princip je isti i za linux:
Ove recimo config od ipfm-a:
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
Ovo je code koji parsira ipfm file-ove i formatira sql da bi se ubacio u bazu:
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
Ovo je kod za scheduled mail report:
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