Home > HowTo, Linux > Access log to DB

Access log to DB

This script loads tomcat or apache logs into a mysql database.

Here is the code:

#!/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);
}
Categories: HowTo, Linux Tags:
  1. No comments yet.
  1. No trackbacks yet.

VAMOVE *

*