Access log to DB

Send apache access log to a mysql database

#!/bin/bash
# 2015-03-04 Nereu – versao Tomcat
#set -x
if [ ! -d /usr/local/tmp/his/$1 ] then
echo ‘Ops!not found data/produto!’
echo “Uso: $0 YYYYMMDD_Produto”
echo “Exemplo: $0 20150508_product”
exit 1
fi
date # timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, cat < 10 ) { print “drop table ” $0 “;\n”; } }’ | \ mysql -u root -h bkpmdsrv -pyrtqramna1 –force tomcat mysql -u root -h bkpmdsrv -pyrtqramna1 –force tomcat -B -N -e “show tables like ‘%oglobo'” | \ sort -r | awk ‘{ if ( NR > 10 ) { print “drop table ” $0 “;\n”; } }’ | \ mysql -u root -h bkpmdsrv -pyrtqramna1 –force tomcat date Second Script load_accesslog_tomcat script

#!/usr/bin/perl
#
# Load a Apache/Weblogic CLF(Common Log Format) in a MySQL table
#
# 2015-03-04 Nereu - ajustado para o Tomcat
#
use DBI;
$start=time();
$debug=0;
$dbh = DBI->connect('DBI:mysql:database=tomcat;host=bkpport=3306','root','na1',{ PrintError => 0 } ) || die "Could not connect to database: $DBI::errstr";
$n=$ne=0;
while (  ) {
  $n++;
  chop;
  if ( /\d+ms$/ ) {
    ( $ip, $junk, $user, $datetime, $req, $status, $size, $ref, $agent, $ela ) = /^(\S+) (\S+) (\S+) \[(.+)\] \"(.+)\" (\S+) (\S+) \"(.*)\" \"(.*)\" (\S+)/o;
  } else {
    ( $ip, $junk, $user, $datetime, $req, $status, $size, $ref, $agent ) = /^(\S+) (\S+) (\S+) \[(.+)\] \"(.+)\" (\S+) (\S+) \"(.*)\" \"(.*)\"/o;
    $ela="";
  }
  @mup = split / /,$req;
  $method=shift @mup;
  $protocol=pop @mup;
  if ( $protocol !~ /^HTTP.*/i && $protocol ne "" ) { push(@mup,$protocol); $protocol=""; }
  $url=join "", @mup;
  $url =~ s/'/''/g;
  $url =~ s/\\/\\\\/g;
  $ref =~ s/'/''/g;
  $ref =~ s/\\/\\\\/g;
  $agent =~ s/'/''/g;
  $agent =~ s/\\/\\\\/g;
  $ela =~ s/ms//;
  $sql=sprintf("insert into accesslog_%s values ('%s',%s,'%s',%s,%s,'%s','%s','%s',%s,%s,%s,%s,%s,%s)",
      $ARGV[0],
      $ARGV[1],
      $ARGV[2],
      $ip,
      ( $identity eq "-" ? "NULL" : "'${identity}'" ),
      ( $user eq "-" ? "NULL" : "'${user}'" ),
      &DateToTimestamp($datetime),
      $method,
      $url,
      ( $protocol eq "" ? "NULL" : "'${protocol}'" ),
      $status,
      ( $size eq "-" ? "NULL" : "${size}" ),
      ( $ref eq "-" ? "NULL" : "'${ref}'" ),
      ( $agent eq "-" ? "NULL" : "'${agent}'"),
      ( $ela eq "" ? "NULL" : "$ela" )
   );
   if ( ! $dbh->do($sql) ) {
     if ( $debug ) {
       print "REC: $_\n";
       print "SQL: $sql\n";
       print "Error: " . $dbh->errstr() . "\n";
     }
     $ne++;
   }
   #print stderr chr(13) . "$n..." if ( ($n % 10000) == 0 );
}
$dbh->disconnect();
#print stderr chr(13) . "\n";
$elap=time()-$start;
print "####  table    : accesslog_$ARGV[0]\n";
print "####  host     : $ARGV[1]\n";
print "####  port     : $ARGV[2]\n";
print "####  records  : $n\n";
print "####  errors   : $ne\n";
print "####  time(s)  : $elap\n";
print "\n";
###
sub DateToTimestamp {
  no warnings 'once';  ### tirar warning da falta de uso variavel $S_ZONE
  ### [03/Mar/2015:07:51:26 -0300]
  $Dt = $_[0];
  %Month_to_M = ( 'Jan', '01', 'Feb', '02', 'Mar', '03', 'Apr', '04', 'May', '05', 'Jun', '06',
                  'Jul', '07', 'Ago', '08', 'Sep', '09', 'Oct', '10', 'Nov', '11', 'Dec', '12'
  );
  ( $DMY,$H,$M,$S_ZONE ) = split /:/,$Dt;
  ( $D,$Month,$Y ) = split /\//,$DMY;
  ( $S,$Zone ) = split / /,$S_ZONE;
  return sprintf("%4s-%2s-%2s %2s:%2s:%2s",$Y,$Month_to_M{$Month},$D,$H,$M,$S);
}