twitter |   ||  email | PGP --> harshal @ harshdevx.comharshdevx:~#
harshdevX:~#r00t  |  analyt1cs   |  st1lls  |  ab0ut m3
updating...
generating stats with your firewall | part - 2
so far so good. next we need to work on processing the data. in earlier page we created the log file. now we need to process this log file and insert the records in our database.
#1 sqlite3 /your-working-folder/dbanalytics.db "UPDATE kernellog SET flag=0 WHERE flag=1;"
#2 cat /your-working-folder/scanip.log | gawk '{ print $1" "$2" "$3" "$7" "$10" "$11" "$12" "$15" "$17" "$18" "$19" "$20 }' > /tmp/scanip.tmp
#3 while read LINE
#4 do
#5 MON=`echo $LINE | gawk '{ print $1 }'`
#6 DAY=`echo $LINE | gawk '{ print $2 }'`
#7 if [ `echo -n "$DAY" | wc -c` = "1" ]; then
#8 DAY=`echo -n "$DAY" | sed -e 's/^/0/'`
#9 fi
#10 TIME=`echo $LINE | gawk '{ print $3 }'`
#11 INTERFACE=`echo $LINE | gawk '{ print $4 }' | cut -f 2 -d "="`
#12 SRC=`echo $LINE | gawk '{ print $5 }' | cut -f 2 -d "="`
#13 DST=`echo $LINE | gawk '{ print $6 }' | cut -f 2 -d "="`
#14 LEN=`echo $LINE | gawk '{ print $7 }' | cut -f 2 -d "="`
#15 TTL=`echo $LINE | gawk '{ print $8 }' | cut -f 2 -d "="`
#16
#17 PROTO=`echo $LINE | gawk '{ print $9 }' | cut -f 2 -d "="`
#18 if [ "$PROTO" == "DF" ] ; then
#19 PROTO=`echo $LINE | gawk '{ print $10 }' | cut -f 2 -d "="`
#20 SPORT=`echo $LINE | gawk '{ print $11 }' | cut -f 2 -d "="`
#21 DPORT=`echo $LINE | gawk '{ print $12 }' | cut -f 2 -d "="`
#22 elif [ "$PROTO" == "TCP" ] ; then
#23 SPORT=`echo $LINE | gawk '{ print $10}' | cut -f 2 -d "="`
#24 DPORT=`echo $LINE | gawk '{ print $11}' | cut -f 2 -d "="`
#25 elif [ "$PROTO" == "UDP" ] ; then
#26 SPORT=`echo $LINE | gawk '{ print $10}' | cut -f 2 -d "="`
#27 DPORT=`echo $LINE | gawk '{ print $11}' | cut -f 2 -d "="`
#28 fi
#29 YEAR=`date +%Y`
#30
#31 if [ "$MON" == "Jan" ] ; then
#32 MONTH="01"
#33 elif [ "$MON" == "Feb" ] ; then
#34 MONTH="02"
#35 elif [ "$MON" == "Mar" ] ; then
#36 MONTH="03"
#37 elif [ "$MON" == "Apr" ] ; then
#38 MONTH="04"
#39 elif [ "$MON" == "May" ] ; then
#40 MONTH="05"
#41 elif [ "$MON" == "Jun" ] ; then
#42 MONTH="06"
#43 elif [ "$MON" == "Jul" ] ; then
#44 MONTH="07"
#45 elif [ "$MON" == "Aug" ] ; then
#46 MONTH="08"
#47 elif [ "$MON" == "Sep" ] ; then
#48 MONTH="09"
#49 elif [ "$MON" == "Oct" ] ; then
#50 MONTH="10"
#51 elif [ "$MON" == "Nov" ] ; then
#52 MONTH="11"
#53 elif [ "$MON" == "Dec" ] ; then
#54 MONTH="12"
#55 fi
#56
#57 DATE=$YEAR"-"$MONTH"-"$DAY
#58 sqlite3 /your-working-folder/dbanalytics.db "INSERT INTO kernellog VALUES('$DATE','$TIME','$INTERFACE','$SRC','$DST',$LEN,$TTL,'$PROTO',$SPORT,$DPORT,1);"
#59 done < /tmp/scanip.tmp
#60 while read -a row
#61 do
#62 RECORD=`echo ${row[0]}`
#63 if [ "$RECORD" != "src" ] ; then
#64 COUNTRY=`geoiplookup $RECORD | sed -e 's/GeoIP Country Edition: //'`
#65 # echo "INSERT INTO geoiplookup VALUES ('$RECORD','$COUNTRY');"
#66 sqlite3 /your-working-folder/dbanalytics.db "INSERT INTO geoiplookup VALUES ('$RECORD','$COUNTRY');"
#67 fi
#68 done < <(sqlite3 /your-working-folder/dbanalytics.db "select distinct (kernellog.src) from kernellog where kernellog.src not in (select geoiplookup.src from geoiplookup);")
#69 sqlite3 /your-working-folder/dbanalytics.db "update kernellog set dstno ='1' where dstno='your-firewall-ip-here';"
download shell script and save as .sh
Part -3

concept and design: harshdevX