access_log2tab

#!/usr/bin/env perl

#=======================================================================
# access_log_2tab
# File ID: 7501e37a-f740-11dd-8b2b-000475e441b9
#
# Convert access log files generated by the Apache HTTP Server to TAB 
# separated data suitable for import into a database.
#
# Character set: UTF-8
# ©opyleft 2006– Øyvind A. Holm <sunny@sunbase.org>
# License: GNU General Public License version 2 or later, see end of 
# file for legal stuff.
#=======================================================================

use strict;
use warnings;
use Getopt::Long;

local $| = 1;

our %Opt = (

    'help' => 0,
    'initdb' => 0,
    'quiet' => 0,
    'server' => "",
    'verbose' => 0,
    'version' => 0,

);

our $progname = $0;
$progname =~ s/^.*\/(.*?)$/$1/;
our $VERSION = '0.2.0';

Getopt::Long::Configure('bundling');
GetOptions(

    'help|h' => \$Opt{'help'},
    'initdb' => \$Opt{'initdb'},
    'quiet|q+' => \$Opt{'quiet'},
    'server|s=s' => \$Opt{'server'},
    'verbose|v+' => \$Opt{'verbose'},
    'version' => \$Opt{'version'},

) || die("$progname: Option error. Use -h for help.\n");

$Opt{'verbose'} -= $Opt{'quiet'};
$Opt{'help'} && usage(0);
if ($Opt{'version'}) {
    print_version();
    exit(0);
}

exit(main());

sub main {
    # {{{
    my $Retval = 0;

    if ($Opt{'initdb'}) {
        print(<<'END');
CREATE TABLE IF NOT EXISTS access_log (
    w text[],
    host text,
    ip inet,
    identd text,
    username text,
    date timestamptz,
    page text,
    status integer,
    bytes_sent bigint,
    referrer text,
    browser text,
    hostname text
);
CREATE TABLE IF NOT EXISTS domains (
    ip inet,
    hostname text,
    date timestamptz
);
CREATE OR REPLACE FUNCTION ip2host(inet) RETURNS text
AS $$
SELECT hostname FROM domains
    WHERE ip = $1;
$$ LANGUAGE SQL;
END
        exit(0);
    }

    length($Opt{'server'}) || die("Need to specify the --server (-s) option\n");

    while (<>) {
        if (/^
            (\d+\.\d+\.\d+\.\d+) # 1: IP adress
            \s
            (\S+)                # 2: identd info (Not used)
            \s
            (.+?)                # 3: User name if http auth
            \s
            \[(.*?)\]            # 4: Timestamp
            \s
            "(.*?)"              # 5: Method + Page + Protocol
            \s
            (\d+)                # 6: Status code
            \s
            (\S+)                # 7: Bytes sent
            \s
            "(.*?)"              # 8: Referrer
            \s
            "(.*?)"              # 9: Browser
            $
        /x) {
            print(
                join("\t",
                    '{}',
                    $Opt{'server'},
                    $1,
                    $2,
                    postgresql_copy_safe($3),
                    $4,
                    postgresql_copy_safe($5),
                    $6,
                    $7 eq '-' ? 0 : $7,
                    postgresql_copy_safe($8),
                    postgresql_copy_safe($9),
                    '',
                ) . "\n"
            );
        } else {
            chomp(my $Line = $_);
            warn("Line $.: Unknown line: '$Line'\n");
        }
    }

    return $Retval;
    # }}}
} # main()

sub postgresql_copy_safe {
    # {{{
    my $Str = shift;
    $Str =~ s/\\/\\\\/gs;
    $Str =~ s/\n/\\n/gs;
    $Str =~ s/\r/\\r/gs;
    $Str =~ s/\t/\\t/gs;
    return($Str);
    # }}}
}

sub print_version {
    # Print program version {{{
    print("$progname $VERSION\n");
    return;
    # }}}
} # print_version()

sub usage {
    # Send the help message to stdout {{{
    my $Retval = shift;

    if ($Opt{'verbose'}) {
        print("\n");
        print_version();
    }
    print(<<"END");

Convert access log files generated by the Apache HTTP Server to TAB 
separated data suitable for import into a database.

Usage: $progname -s servername [options] [file [files [...]]]

Options:

  --initdb
    Output SQL commands to create the initialise the database.
  -h, --help
    Show this help.
  -q, --quiet
    Be more quiet. Can be repeated to increase silence.
  -s x, --server x
    Server identification. Required.
  -v, --verbose
    Increase level of verbosity. Can be repeated.
  --version
    Print version information.

Postgres:

  createdb DBNAME
  $progname --initdb | psql DBNAME
  cat *ACCESS_LOG_FILES* | \\
    $progname -s SERVERNAME | \\
    psql DBNAME -c "copy access_log from stdin"

SQLite 3:

  $progname --initdb | sqlite3 DBNAME
  cat *ACCESS_LOG_FILES* | \\
    $progname -s SERVERNAME | \\
    sqlite3 DBNAME '.separator "\\t"' '.import /dev/stdin access_log'

END
    exit($Retval);
    # }}}
} # usage()

sub msg {
    # Print a status message to stderr based on verbosity level {{{
    my ($verbose_level, $Txt) = @_;

    if ($Opt{'verbose'} >= $verbose_level) {
        print(STDERR "$progname: $Txt\n");
    }
    return;
    # }}}
} # msg()

__END__

# This program is free software; you can redistribute it and/or modify 
# it under the terms of the GNU General Public License as published by 
# the Free Software Foundation; either version 2 of the License, or (at 
# your option) any later version.
#
# This program is distributed in the hope that it will be useful, but 
# WITHOUT ANY WARRANTY; without even the implied warranty of 
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
# See the GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License 
# along with this program.
# If not, see L<http://www.gnu.org/licenses/>.

# vim: set fenc=UTF-8 ft=perl fdm=marker ts=4 sw=4 sts=4 et fo+=w :