[osmrrze.git] / scripts / collectlogsintodb.pl

#!/usr/bin/perl -w

# Where is the ftp stats database?
$statsdb = 'osmstats';

# How many tiles are a metatile? These get merged / counted as one tile.
# If you really want to get stats for individual tiles and not per metatile,
# just set this to 1.
$metatilesize = 8;

%stylemap = ( 'tiles'   => 1,
              'osmde'   => 2,
              'lowzoom' => 3,
              'osmhd'   => 4
            );

%mapmap = ( 'osm'   => 1,
            'osmde' => 2,
            'osmhd' => 4
          );

# -----------------------------------------------------------------------------

use DBI;
use POSIX qw(strftime mktime);

# Parameter 0: table name
# Returns: 1 if the table exists, or 0 if not.
sub dbtableexists($) {
  my $cnt = $dbh->selectrow_array("SELECT COUNT(*) FROM pg_tables" .
                                  " WHERE tablename=" . $dbh->quote($_[0]));
  if ((defined($cnt)) && ($cnt > 0)) {
    return 1;
  } else {
    return 0;
  }
}

# Initializes database if it doesn't exist yet
sub doinitdb() {
  unless (dbtableexists('tilerequests')) {
    my $res = $dbh->do( <<EOCR );
      CREATE TABLE tilerequests (
        styleid INTEGER NOT NULL DEFAULT NULL,
        date DATE NOT NULL DEFAULT NULL,
        x INTEGER NOT NULL DEFAULT NULL,
        y INTEGER NOT NULL DEFAULT NULL,
        z INTEGER NOT NULL DEFAULT NULL,
        requests BIGINT NOT NULL DEFAULT 0,
        PRIMARY KEY (styleid, date, x, y, z)
      )
EOCR
    unless ($res) {
      print("Could not create table tilerequests. $DBI::errstr\n"); exit(1);
    }
  }
  unless (dbtableexists('renderrequests')) {
    my $res = $dbh->do( <<EOCR );
      CREATE TABLE renderrequests (
        mapid INTEGER NOT NULL DEFAULT NULL,
        ts BIGINT NOT NULL DEFAULT NULL,
        x INTEGER NOT NULL DEFAULT NULL,
        y INTEGER NOT NULL DEFAULT NULL,
        z INTEGER NOT NULL DEFAULT NULL,
        rendertime REAL NOT NULL DEFAULT NULL,
        PRIMARY KEY (mapid, ts, x, y, z)
      )
EOCR
    unless ($res) {
      print("Could not create table renderrequests. $DBI::errstr\n"); exit(1);
    }
  }
}

sub showhelp() {
  print("Syntax: $0 --tirex|--apache [logfile]\n");
  print("Parses the logfile given as tirex or apache log into the respective database table.\n");
  print("If no logfile is given, uses STDIN.\n");
}

unless ($dbh = DBI->connect("dbi:Pg:dbname=$statsdb","","")) {
  print(STDERR "Failed to open database. Please try again later.\n"); exit(1);
}
# Create database tables if they do not exist yet
doinitdb();
my $FILENAME = '-';
my $RUNMODE = 0;
for ($i = 0; $i < @ARGV; $i++) {
  if ($ARGV[$i] eq '--help') {
    showhelp(); exit(0);
  } elsif ($ARGV[$i] eq '-h') {
    showhelp(); exit(0);
  } elsif ($ARGV[$i] eq '--tirex') {
    $RUNMODE = 2;
  } elsif ($ARGV[$i] eq '--apache') {
    $RUNMODE = 1;
  } else {
    unless ($FILENAME eq '-') {
      print("ERROR: At most one filename can be given on the command line.\n");
      showhelp(); exit(1);
    }
    $FILENAME = $ARGV[$i];
  }
}
unless (($RUNMODE >= 1) && ($RUNMODE <= 2)) {
  print("ERROR: You must select a logfile mode (--apache or --tirex)\n");
  showhelp(); exit(1);
}
if ($FILENAME eq '-') {
  $ALF = STDIN;
} else {
  unless (open($ALF, '<', $FILENAME)) {
    print(STDERR "ERROR: Could not open logfile $FILENAME\n");
    exit(1);
  }
}
if ($RUNMODE == 1) { # Apache
  my %ctr = ();
  my $nlines = 0;
  while ($ll = <$ALF>) {
    $ll =~ s/[\r\n]//g;
    # 127.0.0.0 - - [23/Sep/2013:17:25:23 +0200] "GET /server-status?auto HTTP/1.1" 200 2431 "-" "libwww-perl/6.03"
    #print("$ll\n");
    # If there is a " in the URL or referrer or user agent, it is escaped by
    # apache with '\"' - that makes it pretty hard to parse with a regexp.
    # Therefore, work around the problem by replacing the \" with something else.
    $ll =~ s/\\"/\%22/g;  # HTML code for the "
    if ($ll =~ m/^([^ ]+) ([^ ]+) ([^ ]+) \[([^\]]+)\] "([^"]+)" ([^ ]+) ([^ ]+)/) {
      my $srcip = $1;
      my $statuscode = $6;
      my $bytessent = $7;
      my $filename = $5;
      if ($filename !~ m/^GET /) { next; }
      $filename =~ s/^GET //g;
      $filename =~ s!(http|https)://[^/]*!!g;
      $filename =~ s! HTTP/\d\.\d$!!g;
      $filename =~ s!\?[^ ]*$!!g;
      if (($statuscode !~ m/^2../) && ($statuscode !~ m/^304/)) {
        # only count successful requests. 304 is 'not modified', so a successful request.
        next;
      }
      if ($filename =~ m!([^/]+)/(\d+)/(\d+)/(\d+)\.png!) {
        my $sn = $1; my $z = $2; my $x = $3; my $y = $4;
        my $s = $stylemap{$sn};
        unless (defined($s)) { next; }
        $x = $x & ~($metatilesize - 1);
        $y = $y & ~($metatilesize - 1);
        #print("tilestyle: $sn (# $s) $x $y $z\n");
        if (defined($ctr{$s}{$z}{$y}{$x})) {
          $ctr{$s}{$z}{$y}{$x}++;
        } else {
          $ctr{$s}{$z}{$y}{$x} = 1;
        }
        $nlines++;
      }
    }
  }
  close($ALF);
  my $ninserts = 0;
  my $nupdates = 0;
  foreach $s (keys(%ctr)) {
    my %h1 = %{$ctr{$s}};
    foreach $z (keys(%h1)) {
      my %h2 = %{$h1{$z}};
      foreach $y (keys(%h2)) {
        my %h3 = %{$h2{$y}};
        foreach $x (keys(%h3)) {
          #print("$s $z $y $x $h3{$x}\n");
          my $date = strftime("%Y-%m-%d", localtime(time() - 86400));
          local $dbh->{'PrintError'};  # We're fully aware that the execute can
          local $dbh->{'PrintWarn'};   # fail, no need to spam about it.
          unless ($dbh->do('INSERT INTO tilerequests(styleid, date, z, y, x, requests)' .
                           ' VALUES(' . $s . ',' . $dbh->quote($date) . ',' .
                                $z . ',' . $y . ',' . $x . ',' . $h3{$x} . ')')) {
            # Try again with update
            unless ($dbh->do('UPDATE tilerequests SET requests=requests+' . $h3{$x} .
                             ' WHERE styleid=' . $s . ' AND date=' . $dbh->quote($date) .
                             '  AND z=' . $z . ' AND y=' . $y . ' AND x=' . $x)) {
              print(STDERR "Both INSERT and UPDATE to DB failed: $DBI::errstr\n");
              exit(1);
            } else {
              $nupdates++;
            }
          } else {
            $ninserts++;
          }
        }
      }
    }
  }
  print("Done. $nlines relevant lines of logfile were handled with $ninserts DB inserts and $nupdates DB updates.\n");
}
if ($RUNMODE == 2) {  # tirex logfile
  my $nreqs;
  while ($ll = <$ALF>) {
    my $reqtime; my $map; my $x; my $y; my $z; my $rendertime;
    $ll =~ s/[\r\n]//g;
    # 2015-09-07T13:37:58 id=1441625875_43436056 map=osmde x=34816 y=22928 z=16 prio=20 request_time=1441625875 expire= sources=MMMMMMMM render_time=3277 success=1
    unless ($ll =~ m/\ssuccess=1/) {
      next; # We do not care about failed requests.
    }
    if ($ll =~ m/\srequest_time=(\d+)\s/) {
      $reqtime = $1;
    } else {
      next;
    }
    if ($ll =~ m/\smap=([^ ]+)\s/) {
      $map = $mapmap{$1};
      unless (defined($map)) { next; }
    } else {
      next;
    }
    if ($ll =~ m/\srender_time=(\d+)\s/) {
      $rendertime = ($1 / 1000.0);
    } else {
      next;
    }
    if ($ll =~ m/\sx=(\d+)\s/) { $x = $1; } else { next; }
    if ($ll =~ m/\sy=(\d+)\s/) { $y = $1; } else { next; }
    if ($ll =~ m/\sz=(\d+)\s/) { $z = $1; } else { next; }
    #print("$map  $z $y $x  $rendertime\n");
    unless ($dbh->do('INSERT INTO renderrequests(mapid, ts, z, y, x, rendertime)' .
                     ' VALUES(' . $map . ',' . $reqtime . ',' . $z . ',' .
                              $y . ',' . $x . ',' . $rendertime . ')')) {
      print(STDERR "Failed to insert renderrequest into DB: $DBI::errstr\n");
    }
    $nreqs++;
  }
  close($ALF);
  print("Done. Inserted $nreqs entries into DB.\n");
}
Commit	Line	Data
f9d757fc MM	1	#!/usr/bin/perl -w
	2
	3	# Where is the ftp stats database?
	4	$statsdb = 'osmstats';
	5
	6	# How many tiles are a metatile? These get merged / counted as one tile.
	7	# If you really want to get stats for individual tiles and not per metatile,
	8	# just set this to 1.
	9	$metatilesize = 8;
	10
	11	%stylemap = ( 'tiles' => 1,
	12	'osmde' => 2,
	13	'lowzoom' => 3,
	14	'osmhd' => 4
	15	);
	16
	17	%mapmap = ( 'osm' => 1,
	18	'osmde' => 2,
	19	'osmhd' => 4
	20	);
	21
	22	# -----------------------------------------------------------------------------
	23
	24	use DBI;
	25	use POSIX qw(strftime mktime);
	26
	27	# Parameter 0: table name
	28	# Returns: 1 if the table exists, or 0 if not.
	29	sub dbtableexists($) {
	30	my $cnt = $dbh->selectrow_array("SELECT COUNT(*) FROM pg_tables" .
	31	" WHERE tablename=" . $dbh->quote($_[0]));
	32	if ((defined($cnt)) && ($cnt > 0)) {
	33	return 1;
	34	} else {
	35	return 0;
	36	}
	37	}
	38
	39	# Initializes database if it doesn't exist yet
	40	sub doinitdb() {
	41	unless (dbtableexists('tilerequests')) {
	42	my $res = $dbh->do( <<EOCR );
	43	CREATE TABLE tilerequests (
	44	styleid INTEGER NOT NULL DEFAULT NULL,
	45	date DATE NOT NULL DEFAULT NULL,
	46	x INTEGER NOT NULL DEFAULT NULL,
	47	y INTEGER NOT NULL DEFAULT NULL,
	48	z INTEGER NOT NULL DEFAULT NULL,
	49	requests BIGINT NOT NULL DEFAULT 0,
	50	PRIMARY KEY (styleid, date, x, y, z)
	51	)
	52	EOCR
	53	unless ($res) {
	54	print("Could not create table tilerequests. $DBI::errstr\n"); exit(1);
	55	}
	56	}
	57	unless (dbtableexists('renderrequests')) {
	58	my $res = $dbh->do( <<EOCR );
	59	CREATE TABLE renderrequests (
	60	mapid INTEGER NOT NULL DEFAULT NULL,
	61	ts BIGINT NOT NULL DEFAULT NULL,
	62	x INTEGER NOT NULL DEFAULT NULL,
	63	y INTEGER NOT NULL DEFAULT NULL,
	64	z INTEGER NOT NULL DEFAULT NULL,
65	rendertime REAL NOT NULL DEFAULT NULL,
66	PRIMARY KEY (mapid, ts, x, y, z)
67	)
68	EOCR
69	unless ($res) {
70	print("Could not create table renderrequests. $DBI::errstr\n"); exit(1);
71	}
72	}
73	}
74
75	sub showhelp() {
76	print("Syntax: $0 --tirex\|--apache [logfile]\n");
77	print("Parses the logfile given as tirex or apache log into the respective database table.\n");
78	print("If no logfile is given, uses STDIN.\n");
79	}
80
81	unless ($dbh = DBI->connect("dbi:Pg:dbname=$statsdb","","")) {
82	print(STDERR "Failed to open database. Please try again later.\n"); exit(1);
83	}
84	# Create database tables if they do not exist yet
85	doinitdb();
86	my $FILENAME = '-';
87	my $RUNMODE = 0;
88	for ($i = 0; $i < @ARGV; $i++) {
89	if ($ARGV[$i] eq '--help') {
90	showhelp(); exit(0);
91	} elsif ($ARGV[$i] eq '-h') {
92	showhelp(); exit(0);
93	} elsif ($ARGV[$i] eq '--tirex') {
94	$RUNMODE = 2;
95	} elsif ($ARGV[$i] eq '--apache') {
96	$RUNMODE = 1;
97	} else {
98	unless ($FILENAME eq '-') {
99	print("ERROR: At most one filename can be given on the command line.\n");
100	showhelp(); exit(1);
101	}
102	$FILENAME = $ARGV[$i];
103	}
104	}
105	unless (($RUNMODE >= 1) && ($RUNMODE <= 2)) {
106	print("ERROR: You must select a logfile mode (--apache or --tirex)\n");
107	showhelp(); exit(1);
108	}
109	if ($FILENAME eq '-') {
110	$ALF = STDIN;
111	} else {
112	unless (open($ALF, '<', $FILENAME)) {
113	print(STDERR "ERROR: Could not open logfile $FILENAME\n");
114	exit(1);
115	}
116	}
117	if ($RUNMODE == 1) { # Apache
118	my %ctr = ();
119	my $nlines = 0;
120	while ($ll = <$ALF>) {
121	$ll =~ s/[\r\n]//g;
122	# 127.0.0.0 - - [23/Sep/2013:17:25:23 +0200] "GET /server-status?auto HTTP/1.1" 200 2431 "-" "libwww-perl/6.03"
123	#print("$ll\n");
124	# If there is a " in the URL or referrer or user agent, it is escaped by
125	# apache with '\"' - that makes it pretty hard to parse with a regexp.
126	# Therefore, work around the problem by replacing the \" with something else.
127	$ll =~ s/\\"/\%22/g; # HTML code for the "
128	if ($ll =~ m/^([^ ]+) ([^ ]+) ([^ ]+) \[([^\]]+)\] "([^"]+)" ([^ ]+) ([^ ]+)/) {
129	my $srcip = $1;
130	my $statuscode = $6;
131	my $bytessent = $7;
132	my $filename = $5;
133	if ($filename !~ m/^GET /) { next; }
134	$filename =~ s/^GET //g;
135	$filename =~ s!(http\|https)://[^/]*!!g;
136	$filename =~ s! HTTP/\d\.\d$!!g;
137	$filename =~ s!\?[^ ]*$!!g;
138	if (($statuscode !~ m/^2../) && ($statuscode !~ m/^304/)) {
139	# only count successful requests. 304 is 'not modified', so a successful request.
140	next;
141	}
142	if ($filename =~ m!([^/]+)/(\d+)/(\d+)/(\d+)\.png!) {
143	my $sn = $1; my $z = $2; my $x = $3; my $y = $4;
144	my $s = $stylemap{$sn};
145	unless (defined($s)) { next; }
146	$x = $x & ~($metatilesize - 1);
147	$y = $y & ~($metatilesize - 1);
148	#print("tilestyle: $sn (# $s) $x $y $z\n");
149	if (defined($ctr{$s}{$z}{$y}{$x})) {
150	$ctr{$s}{$z}{$y}{$x}++;
151	} else {
152	$ctr{$s}{$z}{$y}{$x} = 1;
153	}
154	$nlines++;
155	}
156	}
157	}
158	close($ALF);
159	my $ninserts = 0;
160	my $nupdates = 0;
161	foreach $s (keys(%ctr)) {
162	my %h1 = %{$ctr{$s}};
163	foreach $z (keys(%h1)) {
164	my %h2 = %{$h1{$z}};
165	foreach $y (keys(%h2)) {
166	my %h3 = %{$h2{$y}};
167	foreach $x (keys(%h3)) {
168	#print("$s $z $y $x $h3{$x}\n");
169	my $date = strftime("%Y-%m-%d", localtime(time() - 86400));
170	local $dbh->{'PrintError'}; # We're fully aware that the execute can
171	local $dbh->{'PrintWarn'}; # fail, no need to spam about it.
172	unless ($dbh->do('INSERT INTO tilerequests(styleid, date, z, y, x, requests)' .
173	' VALUES(' . $s . ',' . $dbh->quote($date) . ',' .
174	$z . ',' . $y . ',' . $x . ',' . $h3{$x} . ')')) {
175	# Try again with update
176	unless ($dbh->do('UPDATE tilerequests SET requests=requests+' . $h3{$x} .
177	' WHERE styleid=' . $s . ' AND date=' . $dbh->quote($date) .
178	' AND z=' . $z . ' AND y=' . $y . ' AND x=' . $x)) {
179	print(STDERR "Both INSERT and UPDATE to DB failed: $DBI::errstr\n");
180	exit(1);
181	} else {
182	$nupdates++;
183	}
184	} else {
185	$ninserts++;
186	}
187	}
188	}
189	}
190	}
191	print("Done. $nlines relevant lines of logfile were handled with $ninserts DB inserts and $nupdates DB updates.\n");
192	}
193	if ($RUNMODE == 2) { # tirex logfile
194	my $nreqs;
195	while ($ll = <$ALF>) {
196	my $reqtime; my $map; my $x; my $y; my $z; my $rendertime;
197	$ll =~ s/[\r\n]//g;
198	# 2015-09-07T13:37:58 id=1441625875_43436056 map=osmde x=34816 y=22928 z=16 prio=20 request_time=1441625875 expire= sources=MMMMMMMM render_time=3277 success=1
199	unless ($ll =~ m/\ssuccess=1/) {
200	next; # We do not care about failed requests.
201	}
202	if ($ll =~ m/\srequest_time=(\d+)\s/) {
203	$reqtime = $1;
204	} else {
205	next;
206	}
207	if ($ll =~ m/\smap=([^ ]+)\s/) {
208	$map = $mapmap{$1};
209	unless (defined($map)) { next; }
210	} else {
211	next;
212	}
213	if ($ll =~ m/\srender_time=(\d+)\s/) {
214	$rendertime = ($1 / 1000.0);
215	} else {
216	next;
217	}
218	if ($ll =~ m/\sx=(\d+)\s/) { $x = $1; } else { next; }
219	if ($ll =~ m/\sy=(\d+)\s/) { $y = $1; } else { next; }
220	if ($ll =~ m/\sz=(\d+)\s/) { $z = $1; } else { next; }
221	#print("$map $z $y $x $rendertime\n");
222	unless ($dbh->do('INSERT INTO renderrequests(mapid, ts, z, y, x, rendertime)' .
223	' VALUES(' . $map . ',' . $reqtime . ',' . $z . ',' .
224	$y . ',' . $x . ',' . $rendertime . ')')) {
225	print(STDERR "Failed to insert renderrequest into DB: $DBI::errstr\n");
226	}
227	$nreqs++;
228	}
229	close($ALF);
230	print("Done. Inserted $nreqs entries into DB.\n");
231	}