| 1 | #!/usr/bin/perl -w |
| 2 | |
| 3 | # Where is the osm 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 | 'osmdehd' => 5 |
| 16 | ); |
| 17 | |
| 18 | %mapmap = ( 'osm' => 1, |
| 19 | 'osmde' => 2, |
| 20 | 'osmhd' => 4, |
| 21 | 'osmdehd' => 5 |
| 22 | ); |
| 23 | |
| 24 | # ----------------------------------------------------------------------------- |
| 25 | |
| 26 | use DBI; |
| 27 | use POSIX qw(strftime mktime); |
| 28 | |
| 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)) { |
| 35 | return 1; |
| 36 | } else { |
| 37 | return 0; |
| 38 | } |
| 39 | } |
| 40 | |
| 41 | # Initializes database if it doesn't exist yet |
| 42 | sub doinitdb() { |
| 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) |
| 53 | ) |
| 54 | EOCR |
| 55 | unless ($res) { |
| 56 | print("Could not create table tilerequests. $DBI::errstr\n"); exit(1); |
| 57 | } |
| 58 | } |
| 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) |
| 69 | ) |
| 70 | EOCR |
| 71 | unless ($res) { |
| 72 | print("Could not create table renderrequests. $DBI::errstr\n"); exit(1); |
| 73 | } |
| 74 | } |
| 75 | } |
| 76 | |
| 77 | sub showhelp() { |
| 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"); |
| 81 | } |
| 82 | |
| 83 | unless ($dbh = DBI->connect("dbi:Pg:dbname=$statsdb","","")) { |
| 84 | print(STDERR "Failed to open database. Please try again later.\n"); exit(1); |
| 85 | } |
| 86 | # Create database tables if they do not exist yet |
| 87 | doinitdb(); |
| 88 | my $FILENAME = '-'; |
| 89 | my $RUNMODE = 0; |
| 90 | for ($i = 0; $i < @ARGV; $i++) { |
| 91 | if ($ARGV[$i] eq '--help') { |
| 92 | showhelp(); exit(0); |
| 93 | } elsif ($ARGV[$i] eq '-h') { |
| 94 | showhelp(); exit(0); |
| 95 | } elsif ($ARGV[$i] eq '--tirex') { |
| 96 | $RUNMODE = 2; |
| 97 | } elsif ($ARGV[$i] eq '--apache') { |
| 98 | $RUNMODE = 1; |
| 99 | } else { |
| 100 | unless ($FILENAME eq '-') { |
| 101 | print("ERROR: At most one filename can be given on the command line.\n"); |
| 102 | showhelp(); exit(1); |
| 103 | } |
| 104 | $FILENAME = $ARGV[$i]; |
| 105 | } |
| 106 | } |
| 107 | unless (($RUNMODE >= 1) && ($RUNMODE <= 2)) { |
| 108 | print("ERROR: You must select a logfile mode (--apache or --tirex)\n"); |
| 109 | showhelp(); exit(1); |
| 110 | } |
| 111 | if ($FILENAME eq '-') { |
| 112 | $ALF = STDIN; |
| 113 | } else { |
| 114 | unless (open($ALF, '<', $FILENAME)) { |
| 115 | print(STDERR "ERROR: Could not open logfile $FILENAME\n"); |
| 116 | exit(1); |
| 117 | } |
| 118 | } |
| 119 | if ($RUNMODE == 1) { # Apache |
| 120 | my %ctr = (); |
| 121 | my $nlines = 0; |
| 122 | while ($ll = <$ALF>) { |
| 123 | $ll =~ s/[\r\n]//g; |
| 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" |
| 125 | #print("$ll\n"); |
| 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/^([^ ]+) ([^ ]+) ([^ ]+) \[([^\]]+)\] "([^"]+)" ([^ ]+) ([^ ]+)/) { |
| 131 | my $srcip = $1; |
| 132 | my $statuscode = $6; |
| 133 | my $bytessent = $7; |
| 134 | my $filename = $5; |
| 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. |
| 142 | next; |
| 143 | } |
| 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}++; |
| 153 | } else { |
| 154 | $ctr{$s}{$z}{$y}{$x} = 1; |
| 155 | } |
| 156 | $nlines++; |
| 157 | } |
| 158 | } |
| 159 | } |
| 160 | close($ALF); |
| 161 | my $ninserts = 0; |
| 162 | my $nupdates = 0; |
| 163 | foreach $s (keys(%ctr)) { |
| 164 | my %h1 = %{$ctr{$s}}; |
| 165 | foreach $z (keys(%h1)) { |
| 166 | my %h2 = %{$h1{$z}}; |
| 167 | foreach $y (keys(%h2)) { |
| 168 | my %h3 = %{$h2{$y}}; |
| 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"); |
| 182 | exit(1); |
| 183 | } else { |
| 184 | $nupdates++; |
| 185 | } |
| 186 | } else { |
| 187 | $ninserts++; |
| 188 | } |
| 189 | } |
| 190 | } |
| 191 | } |
| 192 | } |
| 193 | print("Done. $nlines relevant lines of logfile were handled with $ninserts DB inserts and $nupdates DB updates.\n"); |
| 194 | } |
| 195 | if ($RUNMODE == 2) { # tirex logfile |
| 196 | my $nreqs; |
| 197 | while ($ll = <$ALF>) { |
| 198 | my $reqtime; my $map; my $x; my $y; my $z; my $rendertime; |
| 199 | $ll =~ s/[\r\n]//g; |
| 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. |
| 203 | } |
| 204 | if ($ll =~ m/\srequest_time=(\d+)\s/) { |
| 205 | $reqtime = $1; |
| 206 | } else { |
| 207 | next; |
| 208 | } |
| 209 | if ($ll =~ m/\smap=([^ ]+)\s/) { |
| 210 | $map = $mapmap{$1}; |
| 211 | unless (defined($map)) { next; } |
| 212 | } else { |
| 213 | next; |
| 214 | } |
| 215 | if ($ll =~ m/\srender_time=(\d+)\s/) { |
| 216 | $rendertime = ($1 / 1000.0); |
| 217 | } else { |
| 218 | next; |
| 219 | } |
| 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"); |
| 228 | } |
| 229 | $nreqs++; |
| 230 | } |
| 231 | close($ALF); |
| 232 | print("Done. Inserted $nreqs entries into DB.\n"); |
| 233 | } |