3 # Where is the osm stats database?
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,
11 %stylemap = ( 'tiles' => 1,
18 %mapmap = ( 'osm' => 1,
24 # -----------------------------------------------------------------------------
27 use POSIX qw(strftime mktime);
29 # Parameter 0: table name
30 # Returns: 1 if the table exists, or 0 if not.
31 sub dbtableexists($) {
32 my $cnt = $dbh->selectrow_array("SELECT COUNT(*) FROM pg_tables" .
33 " WHERE tablename=" . $dbh->quote($_[0]));
34 if ((defined($cnt)) && ($cnt > 0)) {
41 # Initializes database if it doesn't exist yet
43 unless (dbtableexists('tilerequests')) {
44 my $res = $dbh->do( <<EOCR );
45 CREATE TABLE tilerequests (
46 styleid INTEGER NOT NULL DEFAULT NULL,
47 date DATE NOT NULL DEFAULT NULL,
48 x INTEGER NOT NULL DEFAULT NULL,
49 y INTEGER NOT NULL DEFAULT NULL,
50 z INTEGER NOT NULL DEFAULT NULL,
51 requests BIGINT NOT NULL DEFAULT 0,
52 PRIMARY KEY (styleid, date, x, y, z)
56 print("Could not create table tilerequests. $DBI::errstr\n"); exit(1);
59 unless (dbtableexists('renderrequests')) {
60 my $res = $dbh->do( <<EOCR );
61 CREATE TABLE renderrequests (
62 mapid INTEGER NOT NULL DEFAULT NULL,
63 ts BIGINT NOT NULL DEFAULT NULL,
64 x INTEGER NOT NULL DEFAULT NULL,
65 y INTEGER NOT NULL DEFAULT NULL,
66 z INTEGER NOT NULL DEFAULT NULL,
67 rendertime REAL NOT NULL DEFAULT NULL,
68 PRIMARY KEY (mapid, ts, x, y, z)
72 print("Could not create table renderrequests. $DBI::errstr\n"); exit(1);
78 print("Syntax: $0 --tirex|--apache [logfile]\n");
79 print("Parses the logfile given as tirex or apache log into the respective database table.\n");
80 print("If no logfile is given, uses STDIN.\n");
83 unless ($dbh = DBI->connect("dbi:Pg:dbname=$statsdb","","")) {
84 print(STDERR "Failed to open database. Please try again later.\n"); exit(1);
86 # Create database tables if they do not exist yet
90 for ($i = 0; $i < @ARGV; $i++) {
91 if ($ARGV[$i] eq '--help') {
93 } elsif ($ARGV[$i] eq '-h') {
95 } elsif ($ARGV[$i] eq '--tirex') {
97 } elsif ($ARGV[$i] eq '--apache') {
100 unless ($FILENAME eq '-') {
101 print("ERROR: At most one filename can be given on the command line.\n");
104 $FILENAME = $ARGV[$i];
107 unless (($RUNMODE >= 1) && ($RUNMODE <= 2)) {
108 print("ERROR: You must select a logfile mode (--apache or --tirex)\n");
111 if ($FILENAME eq '-') {
114 unless (open($ALF, '<', $FILENAME)) {
115 print(STDERR "ERROR: Could not open logfile $FILENAME\n");
119 if ($RUNMODE == 1) { # Apache
122 while ($ll = <$ALF>) {
124 # 127.0.0.0 - - [23/Sep/2013:17:25:23 +0200] "GET /server-status?auto HTTP/1.1" 200 2431 "-" "libwww-perl/6.03"
126 # If there is a " in the URL or referrer or user agent, it is escaped by
127 # apache with '\"' - that makes it pretty hard to parse with a regexp.
128 # Therefore, work around the problem by replacing the \" with something else.
129 $ll =~ s/\\"/\%22/g; # HTML code for the "
130 if ($ll =~ m/^([^ ]+) ([^ ]+) ([^ ]+) \[([^\]]+)\] "([^"]+)" ([^ ]+) ([^ ]+)/) {
135 if ($filename !~ m/^GET /) { next; }
136 $filename =~ s/^GET //g;
137 $filename =~ s!(http|https)://[^/]*!!g;
138 $filename =~ s! HTTP/\d\.\d$!!g;
139 $filename =~ s!\?[^ ]*$!!g;
140 if (($statuscode !~ m/^2../) && ($statuscode !~ m/^304/)) {
141 # only count successful requests. 304 is 'not modified', so a successful request.
144 if ($filename =~ m!([^/]+)/(\d+)/(\d+)/(\d+)\.png!) {
145 my $sn = $1; my $z = $2; my $x = $3; my $y = $4;
146 my $s = $stylemap{$sn};
147 unless (defined($s)) { next; }
148 $x = $x & ~($metatilesize - 1);
149 $y = $y & ~($metatilesize - 1);
150 #print("tilestyle: $sn (# $s) $x $y $z\n");
151 if (defined($ctr{$s}{$z}{$y}{$x})) {
152 $ctr{$s}{$z}{$y}{$x}++;
154 $ctr{$s}{$z}{$y}{$x} = 1;
163 foreach $s (keys(%ctr)) {
164 my %h1 = %{$ctr{$s}};
165 foreach $z (keys(%h1)) {
167 foreach $y (keys(%h2)) {
169 foreach $x (keys(%h3)) {
170 #print("$s $z $y $x $h3{$x}\n");
171 my $date = strftime("%Y-%m-%d", localtime(time() - 86400));
172 local $dbh->{'PrintError'}; # We're fully aware that the execute can
173 local $dbh->{'PrintWarn'}; # fail, no need to spam about it.
174 unless ($dbh->do('INSERT INTO tilerequests(styleid, date, z, y, x, requests)' .
175 ' VALUES(' . $s . ',' . $dbh->quote($date) . ',' .
176 $z . ',' . $y . ',' . $x . ',' . $h3{$x} . ')')) {
177 # Try again with update
178 unless ($dbh->do('UPDATE tilerequests SET requests=requests+' . $h3{$x} .
179 ' WHERE styleid=' . $s . ' AND date=' . $dbh->quote($date) .
180 ' AND z=' . $z . ' AND y=' . $y . ' AND x=' . $x)) {
181 print(STDERR "Both INSERT and UPDATE to DB failed: $DBI::errstr\n");
193 print("Done. $nlines relevant lines of logfile were handled with $ninserts DB inserts and $nupdates DB updates.\n");
195 if ($RUNMODE == 2) { # tirex logfile
197 while ($ll = <$ALF>) {
198 my $reqtime; my $map; my $x; my $y; my $z; my $rendertime;
200 # 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
201 unless ($ll =~ m/\ssuccess=1/) {
202 next; # We do not care about failed requests.
204 if ($ll =~ m/\srequest_time=(\d+)\s/) {
209 if ($ll =~ m/\smap=([^ ]+)\s/) {
211 unless (defined($map)) { next; }
215 if ($ll =~ m/\srender_time=(\d+)\s/) {
216 $rendertime = ($1 / 1000.0);
220 if ($ll =~ m/\sx=(\d+)\s/) { $x = $1; } else { next; }
221 if ($ll =~ m/\sy=(\d+)\s/) { $y = $1; } else { next; }
222 if ($ll =~ m/\sz=(\d+)\s/) { $z = $1; } else { next; }
223 #print("$map $z $y $x $rendertime\n");
224 unless ($dbh->do('INSERT INTO renderrequests(mapid, ts, z, y, x, rendertime)' .
225 ' VALUES(' . $map . ',' . $reqtime . ',' . $z . ',' .
226 $y . ',' . $x . ',' . $rendertime . ')')) {
227 print(STDERR "Failed to insert renderrequest into DB: $DBI::errstr\n");
232 print("Done. Inserted $nreqs entries into DB.\n");