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 | } |