twitter |   ||  email | PGP --> harshal @ harshdevx.comharshdevx:~#
harshdevX:~#r00t  |  analyt1cs   |  st1lls  |  ab0ut m3
generating stats with your firewall | part - 3
there are two more shell scripts that i created to keep updating the database. i created them because my database was sqlite. i will remove kernellog data after 1 month but i still want to retain the monthly counts. therefore i created scancounts table. what this will do is at the beginning of the month it will remove all the entries of the previous month and insert the final total of that month in scancount table. see the script
#1 MONTH=`date +%Y-%m`
#2 echo "insert into scancount (date,count) select strftime('%Y-%m',date) as date, count(date) as count from kernellog where like '$MONTH';" | sqlite3 dbanalytics.db
download shell script and save as .sh
the second one runs every hour. this is an update script to scancount table. i named it
#1 MONTH=`date +%Y-%m`
#2 echo "update scancount set count =(select count(date) from kernellog where like '$MONTH-%') where date='$MONTH';" | sqlite3 dbanalytics.db
download shell script and save as .sh
thats the end of it. this is purely based on shell scripts, kernel logs and sqlite. there are no other programs installed. my website has analyt1cs section which i created using php pages. you can do the same you can start mining the data in sql scripts and create meaningful reports. this is just a humble attempt to create meaningful reports without installing a ton of software. ofcourse the reports are not that great but surely meaningful. i have made code public, so that intelligent folks can take a look and correct me where i am wrong.

concept and design: harshdevX