access_log2tab

   1 #!/usr/bin/env perl
   2
   3 #=======================================================================
   4 # access_log_2tab
   5 # File ID: 7501e37a-f740-11dd-8b2b-000475e441b9
   6 #
   7 # Convert access log files generated by the Apache HTTP Server to TAB
   8 # separated data suitable for import into a database.
   9 #
  10 # Character set: UTF-8
  11 # ©opyleft 2006– Øyvind A. Holm <sunny@sunbase.org>
  12 # License: GNU General Public License version 2 or later, see end of
  13 # file for legal stuff.
  14 #=======================================================================
  15
  16 use strict;
  17 use warnings;
  18 use Getopt::Long;
  19
  20 local $| = 1;
  21
  22 our %Opt = (
  23
  24     'help' => 0,
  25     'initdb' => 0,
  26     'quiet' => 0,
  27     'server' => "",
  28     'verbose' => 0,
  29     'version' => 0,
  30
  31 );
  32
  33 our $progname = $0;
  34 $progname =~ s/^.*\/(.*?)$/$1/;
  35 our $VERSION = '0.2.0';
  36
  37 Getopt::Long::Configure('bundling');
  38 GetOptions(
  39
  40     'help|h' => \$Opt{'help'},
  41     'initdb' => \$Opt{'initdb'},
  42     'quiet|q+' => \$Opt{'quiet'},
  43     'server|s=s' => \$Opt{'server'},
  44     'verbose|v+' => \$Opt{'verbose'},
  45     'version' => \$Opt{'version'},
  46
  47 ) || die("$progname: Option error. Use -h for help.\n");
  48
  49 $Opt{'verbose'} -= $Opt{'quiet'};
  50 $Opt{'help'} && usage(0);
  51 if ($Opt{'version'}) {
  52     print_version();
  53     exit(0);
  54 }
  55
  56 exit(main());
  57
  58 sub main {
  59     # {{{
  60     my $Retval = 0;
  61
  62     if ($Opt{'initdb'}) {
  63         print(<<'END');
  64 CREATE TABLE IF NOT EXISTS access_log (
  65     w text[],
  66     host text,
  67     ip inet,
  68     identd text,
  69     username text,
  70     date timestamptz,
  71     page text,
  72     status integer,
  73     bytes_sent bigint,
  74     referrer text,
  75     browser text,
  76     hostname text
  77 );
  78 CREATE TABLE IF NOT EXISTS domains (
  79     ip inet,
  80     hostname text,
  81     date timestamptz
  82 );
  83 CREATE OR REPLACE FUNCTION ip2host(inet) RETURNS text
  84 AS $$
  85 SELECT hostname FROM domains
  86     WHERE ip = $1;
  87 $$ LANGUAGE SQL;
  88 END
  89         exit(0);
  90     }
  91
  92     length($Opt{'server'}) || die("Need to specify the --server (-s) option\n");
  93
  94     while (<>) {
  95         if (/^
  96             (\d+\.\d+\.\d+\.\d+) # 1: IP adress
  97             \s
  98             (\S+)                # 2: identd info (Not used)
  99             \s
 100             (.+?)                # 3: User name if http auth
 101             \s
 102             \[(.*?)\]            # 4: Timestamp
 103             \s
 104             "(.*?)"              # 5: Method + Page + Protocol
 105             \s
 106             (\d+)                # 6: Status code
 107             \s
 108             (\S+)                # 7: Bytes sent
 109             \s
 110             "(.*?)"              # 8: Referrer
 111             \s
 112             "(.*?)"              # 9: Browser
 113             $
 114         /x) {
 115             print(
 116                 join("\t",
 117                     '{}',
 118                     $Opt{'server'},
 119                     $1,
 120                     $2,
 121                     postgresql_copy_safe($3),
 122                     $4,
 123                     postgresql_copy_safe($5),
 124                     $6,
 125                     $7 eq '-' ? 0 : $7,
 126                     postgresql_copy_safe($8),
 127                     postgresql_copy_safe($9),
 128                     '',
 129                 ) . "\n"
 130             );
 131         } else {
 132             chomp(my $Line = $_);
 133             warn("Line $.: Unknown line: '$Line'\n");
 134         }
 135     }
 136
 137     return $Retval;
 138     # }}}
 139 } # main()
 140
 141 sub postgresql_copy_safe {
 142     # {{{
 143     my $Str = shift;
 144     $Str =~ s/\\/\\\\/gs;
 145     $Str =~ s/\n/\\n/gs;
 146     $Str =~ s/\r/\\r/gs;
 147     $Str =~ s/\t/\\t/gs;
 148     return($Str);
 149     # }}}
 150 }
 151
 152 sub print_version {
 153     # Print program version {{{
 154     print("$progname $VERSION\n");
 155     return;
 156     # }}}
 157 } # print_version()
 158
 159 sub usage {
 160     # Send the help message to stdout {{{
 161     my $Retval = shift;
 162
 163     if ($Opt{'verbose'}) {
 164         print("\n");
 165         print_version();
 166     }
 167     print(<<"END");
 168
 169 Convert access log files generated by the Apache HTTP Server to TAB
 170 separated data suitable for import into a database.
 171
 172 Usage: $progname -s servername [options] [file [files [...]]]
 173
 174 Options:
 175
 176   --initdb
 177     Output SQL commands to create the initialise the database.
 178   -h, --help
 179     Show this help.
 180   -q, --quiet
 181     Be more quiet. Can be repeated to increase silence.
 182   -s x, --server x
 183     Server identification. Required.
 184   -v, --verbose
 185     Increase level of verbosity. Can be repeated.
 186   --version
 187     Print version information.
 188
 189 Postgres:
 190
 191   createdb DBNAME
 192   $progname --initdb | psql DBNAME
 193   cat *ACCESS_LOG_FILES* | \\
 194     $progname -s SERVERNAME | \\
 195     psql DBNAME -c "copy access_log from stdin"
 196
 197 SQLite 3:
 198
 199   $progname --initdb | sqlite3 DBNAME
 200   cat *ACCESS_LOG_FILES* | \\
 201     $progname -s SERVERNAME | \\
 202     sqlite3 DBNAME '.separator "\\t"' '.import /dev/stdin access_log'
 203
 204 END
 205     exit($Retval);
 206     # }}}
 207 } # usage()
 208
 209 sub msg {
 210     # Print a status message to stderr based on verbosity level {{{
 211     my ($verbose_level, $Txt) = @_;
 212
 213     if ($Opt{'verbose'} >= $verbose_level) {
 214         print(STDERR "$progname: $Txt\n");
 215     }
 216     return;
 217     # }}}
 218 } # msg()
 219
 220 __END__
 221
 222 # This program is free software; you can redistribute it and/or modify
 223 # it under the terms of the GNU General Public License as published by
 224 # the Free Software Foundation; either version 2 of the License, or (at
 225 # your option) any later version.
 226 #
 227 # This program is distributed in the hope that it will be useful, but
 228 # WITHOUT ANY WARRANTY; without even the implied warranty of
 229 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 230 # See the GNU General Public License for more details.
 231 #
 232 # You should have received a copy of the GNU General Public License
 233 # along with this program.
 234 # If not, see L<http://www.gnu.org/licenses/>.
 235
 236 # vim: set fenc=UTF-8 ft=perl fdm=marker ts=4 sw=4 sts=4 et fo+=w :