Developer Blog - Inventic.eu
  • Skipper - The ORM Designer
  • VsBuilds - Parallel building
  • Pulpo - Free Skipper CLI

Tag: #Bash

Cmder, Cygwin and other

15 Oct 2016

Posted by: ludek.vodicka

Windows #Windows #Bash #cygwin #command line

How to parse Apache Log and insert data to MySQL database

#!/bin/bash

echo Generating new data from log
echo "#generated sql file" > data.sql

echo "truncate table log_data;" >> data.sql
echo "START TRANSACTION;" >> data.sql

FIND_PATTERN="\
\(\S*\)\s*\
\(\S*\)\s*\
\(\S*\s*\S*\)\s*\
[\x5B]\([^\x5D]*\)[\x5D]\s*\
\"\([^\"]*\)\"\s*\
\(\S*\)\s*\
\(\S*\)\s*\
\"\([^\"]*\)\"\s*\
\"\([^\"]*\)\"\s*\
";

REPLACE_PATTERN="\
INSERT INTO log_data \
(domain,ip,other,date,url,status,size,referral,clientstring) \
VALUES \
('\1','\2','\3','\4','\5','\6','\7','\8','\9');";

APACHE_FILE="other_vhosts_access.log";

if [ -f $APACHE_FILE.size ];
then
ALREADY_PROCEED=`cat $APACHE_FILE.size`
echo "Already proceed size ($APACHE_FILE): "$ALREADY_PROCEED;
else
ALREADY_PROCEED=0;
echo "New log file $APACHE_FILE";
fi

FILTER_PATTERN="\.css|\.ico|\.png|\.jpg|\.txt|\.js|\.gif|\.swf"

#echo $FIND_PATTERN"/"$REPLACE_PATTERN;

cat $APACHE_FILE |
tail -c +$ALREADY_PROCEED | #preskocime jiz zpracovane radky
#head -5000 | #debug
#head -1128863 | #tail -2 |
#tail -n +1177103 | head -3 |
#sed -r "s/(\(.*\))//g" | #debug - odebere z clientstring (...)
#sed "s/^[^+]*//g" | #debug - odebere pocatek stringu az do GET
#sed -r "s/Mozilla|Gecko|Firefox//g" | #debug odebere tyhle klicovy slova
sed -r "s/^(subdomain|www)\.//" | #odebereme prefix subdomain. a www.
sed -r "s/^([^:]*)(:[0-9]*)/\1/" | #odebereme port
sed -r -n "/^(orm-|inventic).*/p" | #exportujem jen nektere domeny
sed "s/\([\x5c][\x22]\)//g" | #odebrani \" retezcu
sed "s/\([']\)//g" | #odebrani apostrof ktery delaj pak bordel v$
sed -r "s/ \"GET ([\x2F].*) HTTP[\x2F][0-9.]*/ \"\1/" | #dame pryc GET a HTTP1.1
#sed -r -n "/$FILTER_PATTERN/p" #| debug- vypise filtrovane .css/.png/...
sed -r "/$FILTER_PATTERN/d" | #vyfiltrujeme pryc .css, .png atd
#sed "/^$FIND_PATTERN$/d" | #debug - toto lze pouzit na vypis radku kte$
sed -n "/^$FIND_PATTERN$/p" | #vypiseme vsechny radky ktere jsou validni
sed "s/$FIND_PATTERN/$REPLACE_PATTERN/" >> data.sql

echo "COMMIT;" >> data.sql

#ulozime velikost zpracovaneho logu

stat -c&quot;%s&quot; $APACHE_FILE &gt; $APACHE_FILE.size<br />
#vlozime data do Mysql<br />
echo Putting data to MySQL<br />
mysql \<br />
  --user=root \<br />
  --password=1234 \<br />
  --host=192.168.0.10 \<br />
  --database=server_log &lt; data.sql</p>

17 Jan 2011

Posted by: ludek.vodicka

Linux #Linux #Apache #Bash