]>
Commit | Line | Data |
---|---|---|
3e10b3de SS |
1 | #!/usr/bin/perl |
2 | ############################################################################### | |
3 | # # | |
4 | # IPFire.org - A linux based firewall # | |
5 | # Copyright (C) 2020 IPFire Team <info@ipfire.org> # | |
6 | # # | |
7 | # This program is free software: you can redistribute it and/or modify # | |
8 | # it under the terms of the GNU General Public License as published by # | |
9 | # the Free Software Foundation, either version 3 of the License, or # | |
10 | # (at your option) any later version. # | |
11 | # # | |
12 | # This program is distributed in the hope that it will be useful, # | |
13 | # but WITHOUT ANY WARRANTY; without even the implied warranty of # | |
14 | # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # | |
15 | # GNU General Public License for more details. # | |
16 | # # | |
17 | # You should have received a copy of the GNU General Public License # | |
18 | # along with this program. If not, see <http://www.gnu.org/licenses/>. # | |
19 | # # | |
20 | ############################################################################### | |
21 | ||
22 | use strict; | |
23 | use POSIX(); | |
24 | use DBI; | |
25 | ||
26 | # enable only the following on debugging purpose | |
27 | #use warnings; | |
28 | #use CGI::Carp 'fatalsToBrowser'; | |
29 | ||
30 | require '/var/ipfire/general-functions.pl'; | |
31 | require "${General::swroot}/lang.pl"; | |
32 | require "${General::swroot}/header.pl"; | |
33 | ||
34 | my %color = (); | |
35 | my %mainsettings = (); | |
36 | &General::readhash("${General::swroot}/main/settings", \%mainsettings); | |
8186b372 | 37 | &General::readhash("/srv/web/ipfire/html/themes/ipfire/include/colors.txt", \%color); |
3e10b3de SS |
38 | |
39 | # Path and file of the OVPN connections database. | |
40 | my $database = "/var/ipfire/ovpn/clients.db"; | |
41 | ||
42 | my %cgiparams=(); | |
43 | my %logsettings=(); | |
44 | my %ovpnsettings=(); | |
45 | ||
46 | my $errormessage=''; | |
47 | ||
48 | # Hash wich contains the month numbers and the translated names for easy access. | |
49 | my %monthhash = ( | |
50 | "1" => "$Lang::tr{'january'}", | |
51 | "2" => "$Lang::tr{'february'}", | |
52 | "3" => "$Lang::tr{'march'}", | |
53 | "4" => "$Lang::tr{'april'}", | |
54 | "5" => "$Lang::tr{'may'}", | |
55 | "6" => "$Lang::tr{'june'}", | |
56 | "7" => "$Lang::tr{'july'}", | |
57 | "8" => "$Lang::tr{'august'}", | |
58 | "9" => "$Lang::tr{'september'}", | |
59 | "10" => "$Lang::tr{'october'}", | |
60 | "11" => "$Lang::tr{'november'}", | |
61 | "12" => "$Lang::tr{'december'}" | |
62 | ); | |
63 | ||
64 | # Get current time. | |
65 | my ($sec,$min,$hour,$mday,$month,$year,$wday,$yday,$isdst) = localtime(time); | |
66 | ||
67 | # Adjust month, because Jan starts as month "0". | |
68 | $month = $month+1; | |
69 | ||
70 | # Adjust year number. | |
71 | $year = $year+1900; | |
72 | ||
73 | # Assign default vaules. | |
74 | $cgiparams{'FROM_DAY'} = $mday; | |
75 | $cgiparams{'FROM_MONTH'} = $month; | |
76 | $cgiparams{'FROM_YEAR'} = $year; | |
77 | $cgiparams{'TO_DAY'} = $mday; | |
78 | $cgiparams{'TO_MONTH'} = $month; | |
79 | $cgiparams{'TO_YEAR'} = $year; | |
80 | ||
81 | &Header::getcgihash(\%cgiparams); | |
82 | ||
83 | # Read-in OpenVPN settings and connections. | |
84 | &General::readhasharray("${General::swroot}/ovpn/ovpnconfig", \%ovpnsettings); | |
85 | ||
86 | # Init DB Module and connect to the database. | |
87 | my $database_handle = DBI->connect("DBI:SQLite:dbname=$database", "", "", { RaiseError => 1 }); | |
88 | ||
89 | # Generate datestrings for SQL queries. | |
90 | my $from_datestring = sprintf '%04d-%02d-%02d', ($cgiparams{"FROM_YEAR"}, $cgiparams{"FROM_MONTH"}, $cgiparams{"FROM_DAY"}); | |
91 | my $to_datestring = sprintf '%04d-%02d-%02d', ($cgiparams{"TO_YEAR"}, $cgiparams{"TO_MONTH"}, $cgiparams{"TO_DAY"}); | |
92 | ||
0f195a53 SS |
93 | # Check if the to datestring is later than the from datestring. |
94 | unless ($to_datestring ge $from_datestring) { | |
95 | $errormessage = "$Lang::tr{'error the to date has to be later than the from date'}"; | |
96 | } | |
97 | ||
75bb55e7 MT |
98 | # Initialise database |
99 | my $cursor = $database_handle->prepare(" | |
100 | CREATE TABLE IF NOT EXISTS sessions( | |
101 | common_name TEXT NOT NULL, | |
102 | connected_at TEXT NOT NULL, | |
103 | disconnected_at TEXT, | |
104 | bytes_received INTEGER, | |
105 | bytes_sent INTEGER | |
106 | ); | |
107 | ||
108 | -- Create index for speeding up searches | |
109 | CREATE INDEX IF NOT EXISTS sessions_common_name ON sessions(common_name); | |
110 | "); | |
111 | $cursor->execute(); | |
112 | ||
3e10b3de SS |
113 | my $database_query = qq( |
114 | SELECT | |
115 | common_name, SUM( | |
116 | STRFTIME('%s', ( | |
117 | CASE | |
6e409634 | 118 | WHEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds') |
3e10b3de | 119 | THEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') |
6e409634 | 120 | ELSE DATETIME('$to_datestring', 'start of day', '+86399 seconds') |
3e10b3de SS |
121 | END |
122 | ), 'utc') - | |
123 | STRFTIME('%s', ( | |
124 | CASE | |
6e409634 | 125 | WHEN DATETIME(connected_at, 'localtime') > DATETIME('$from_datestring', 'start of day') |
3e10b3de | 126 | THEN DATETIME(connected_at, 'localtime') |
6e409634 | 127 | ELSE DATETIME('$from_datestring', 'start of day') |
3e10b3de SS |
128 | END |
129 | ), 'utc') | |
70e1d587 | 130 | ) AS duration |
3e10b3de SS |
131 | FROM sessions |
132 | WHERE | |
70e1d587 MT |
133 | ( |
134 | disconnected_at IS NULL | |
135 | OR | |
6e409634 | 136 | DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day') |
70e1d587 MT |
137 | ) |
138 | AND | |
6e409634 | 139 | DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds') |
3e10b3de | 140 | GROUP BY common_name |
70e1d587 | 141 | ORDER BY common_name, duration DESC; |
3e10b3de SS |
142 | ); |
143 | ||
144 | if ($cgiparams{'CONNECTION_NAME'}) { | |
145 | $database_query = qq( | |
186c0ddd MT |
146 | SELECT common_name, DATETIME(connected_at, 'localtime'), DATETIME(disconnected_at, 'localtime'), bytes_received, bytes_sent, |
147 | STRFTIME('%s', DATETIME(disconnected_at)) - STRFTIME('%s', DATETIME(connected_at)) AS duration FROM sessions | |
3e10b3de | 148 | WHERE |
70e1d587 MT |
149 | common_name = '$cgiparams{"CONNECTION_NAME"}' |
150 | AND ( | |
6e409634 | 151 | DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day') |
70e1d587 | 152 | AND |
6e409634 | 153 | DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds') |
70e1d587 MT |
154 | ) |
155 | ORDER BY connected_at; | |
3e10b3de SS |
156 | ); |
157 | } | |
158 | ||
e1cc1e6c SS |
159 | my $statement_handle; |
160 | my $database_return_value; | |
3e10b3de | 161 | |
e1cc1e6c SS |
162 | # Only process SQL actions if there is no error message. |
163 | unless ($errormessage) { | |
164 | # Prepare SQL statement. | |
165 | $statement_handle = $database_handle->prepare($database_query); | |
166 | ||
167 | # Execute SQL statement and get retun value if any error happened. | |
168 | $database_return_value = $statement_handle->execute(); | |
169 | } | |
3e10b3de SS |
170 | |
171 | # If an error has been returned, assign it to the errorstring value for displaying. | |
172 | if($database_return_value < 0) { | |
173 | $errormessage = "$DBI::errstr"; | |
174 | } | |
175 | ||
176 | &Header::showhttpheaders(); | |
177 | ||
178 | &Header::openpage($Lang::tr{'ovpn rw connection log'}, 1, ''); | |
179 | ||
180 | &Header::openbigbox('100%', 'left', '', $errormessage); | |
181 | ||
182 | if ($errormessage) { | |
183 | &Header::openbox('100%', 'left', $Lang::tr{'error messages'}); | |
184 | print "<font class='base'>$errormessage </font>\n"; | |
185 | &Header::closebox(); | |
186 | } | |
187 | ||
188 | &Header::openbox('100%', 'left', "$Lang::tr{'settings'}:"); | |
189 | ||
190 | print "<form method='post' action=\"$ENV{'SCRIPT_NAME'}\">\n"; | |
191 | print "<table width='100%'>\n"; | |
192 | print "<tr>\n"; | |
193 | print "<td class='base' colspan='2'><b>$Lang::tr{'from'}:</b></td>\n"; | |
194 | print "</tr>\n"; | |
195 | ||
196 | print "<tr>\n"; | |
197 | print "<td class='base'>$Lang::tr{'day'}: \;\n"; | |
198 | &generate_select("FROM_DAY", "days"); | |
199 | print "</td>\n"; | |
200 | ||
201 | print "<td class='base'>$Lang::tr{'month'}: \;\n"; | |
202 | &generate_select("FROM_MONTH", "months"); | |
203 | print "</td>\n"; | |
204 | ||
205 | print "<td class='base'>$Lang::tr{'year'}: \;\n"; | |
206 | &generate_select("FROM_YEAR", "years"); | |
207 | print "</td>\n"; | |
208 | print "</tr>\n"; | |
209 | ||
210 | print "<tr><td><br></td></tr>\n"; | |
211 | ||
212 | print "<tr>\n"; | |
213 | print "<td class='base' colspan='2'><b>$Lang::tr{'to'}:</b></td>\n"; | |
214 | print "</tr>\n"; | |
215 | ||
216 | print "<tr>\n"; | |
217 | print "<td class='base'>$Lang::tr{'day'}: \;\n"; | |
218 | &generate_select("TO_DAY", "days"); | |
219 | print "</td>\n"; | |
220 | ||
221 | print "<td class='base'>$Lang::tr{'month'}: \;\n"; | |
222 | &generate_select("TO_MONTH", "months"); | |
223 | print "</td>\n"; | |
224 | ||
225 | print "<td class='base'>$Lang::tr{'year'}: \;\n"; | |
226 | &generate_select("TO_YEAR", "years"); | |
227 | print "</td>\n"; | |
228 | print "</tr>\n"; | |
229 | ||
230 | print "<tr><td><br></td></tr>\n"; | |
231 | ||
232 | print "<tr>\n"; | |
233 | print "<td class='base'>$Lang::tr{'ovpn connection name'}:</td>\n"; | |
234 | print "<td class='base' colspan='2'>\n"; | |
235 | ||
236 | print "<select name='CONNECTION_NAME' size='1'>\n"; | |
6317d55c | 237 | print "<option value=''>$Lang::tr{'all'}</option>\n"; |
3e10b3de SS |
238 | |
239 | # Loop through all configured OpenVPN connections and sort them by name. | |
240 | foreach my $key (sort { $ovpnsettings{$a}[2] cmp $ovpnsettings{$b}[2] } keys %ovpnsettings) { | |
241 | my $connection_name = $ovpnsettings{$key}[2]; | |
242 | my $selected; | |
243 | ||
244 | # Skip all non roadwarrior connections. | |
245 | next unless ($ovpnsettings{"$key"}[3] eq "host"); | |
246 | ||
247 | # Check and mark the selected one. | |
248 | if ($connection_name eq "$cgiparams{'CONNECTION_NAME'}") { | |
249 | $selected = "selected"; | |
250 | } | |
251 | ||
252 | print "<option value='$connection_name' $selected>$connection_name</option>\n"; | |
253 | } | |
254 | ||
255 | print "</select>\n"; | |
256 | print "</td>\n"; | |
257 | print "</tr>\n"; | |
258 | ||
259 | print "<tr>\n"; | |
260 | print "<td width='100%' align='right' colspan='3'><input type='submit' name='ACTION' value='$Lang::tr{'update'}'></td>\n"; | |
261 | print "</tr>\n"; | |
262 | print "</table>\n"; | |
263 | print "</form>\n"; | |
264 | ||
265 | &Header::closebox(); | |
266 | ||
267 | &Header::openbox('100%', 'left', $Lang::tr{'log'}); | |
268 | ||
269 | my $lines = 0; | |
270 | ||
271 | print "<table width='100%' class='tbl'>"; | |
272 | ||
d5b6023c SS |
273 | my $col = "bgcolor='$color{'color20'}'"; |
274 | ||
275 | print "<tr>\n"; | |
276 | print "<td width='40%' $col><b>$Lang::tr{'ovpn connection name'}</b></td>\n"; | |
277 | ||
278 | if ($cgiparams{'CONNECTION_NAME'}) { | |
186c0ddd MT |
279 | print "<td width='15%' $col><b>$Lang::tr{'connected'}</b></td>\n"; |
280 | print "<td width='15%' $col><b>$Lang::tr{'disconnected'}</b></td>\n"; | |
281 | print "<td width='10%' align='right' $col><b>$Lang::tr{'duration'}</b></td>\n"; | |
b10612e8 SS |
282 | print "<td width='10%' align='right' $col><b>$Lang::tr{'received'}</b></td>\n"; |
283 | print "<td width='10%' align='right' $col><b>$Lang::tr{'sent'}</b></td>\n"; | |
d5b6023c SS |
284 | } else { |
285 | print "<td $col><b>$Lang::tr{'total connection time'}</b>\n"; | |
286 | } | |
287 | ||
288 | print "</tr>\n"; | |
3e10b3de | 289 | |
e1cc1e6c SS |
290 | # Only try to fetch the DB items if there is no error message. |
291 | unless ($errormessage) { | |
292 | while(my @row = $statement_handle->fetchrow_array()) { | |
293 | # Assign some nice to read variable names for the DB fields. | |
294 | my $connection_name = $row[0]; | |
295 | my $connection_open_time = $row[1]; | |
296 | my $connection_close_time = $row[2]; | |
297 | my $connection_bytes_recieved = &General::formatBytes($row[3]); | |
298 | my $connection_bytes_sent = &General::formatBytes($row[4]); | |
186c0ddd | 299 | my $duration = &General::format_time($row[5]); |
e1cc1e6c SS |
300 | |
301 | # Colorize columns. | |
302 | if ($lines % 2) { | |
303 | $col="bgcolor='$color{'color20'}'"; | |
304 | } else { | |
305 | $col="bgcolor='$color{'color22'}'"; | |
306 | } | |
307 | ||
308 | print "<tr>\n"; | |
309 | print "<td width='40%' $col>$connection_name</td>\n"; | |
310 | ||
311 | if ($cgiparams{'CONNECTION_NAME'}) { | |
186c0ddd MT |
312 | print "<td width='15%' $col>$connection_open_time</td>\n"; |
313 | print "<td width='15%' $col>$connection_close_time</td>\n"; | |
314 | print "<td width='10%' align='right' $col>$duration</td>\n"; | |
b10612e8 SS |
315 | print "<td width='10%' align='right' $col>$connection_bytes_recieved</td>\n"; |
316 | print "<td width='10%' align='right' $col>$connection_bytes_sent</td>\n"; | |
e1cc1e6c SS |
317 | } else { |
318 | # Convert total connection time into human-readable format. | |
319 | my $total_time = &General::format_time($row[1]); | |
320 | ||
321 | print "<td $col>$total_time</td>\n"; | |
322 | } | |
323 | ||
324 | print "</tr>\n"; | |
325 | ||
326 | # Increase lines count. | |
327 | $lines++; | |
d5b6023c | 328 | } |
c7d55d7f | 329 | } |
3e10b3de | 330 | |
c7d55d7f | 331 | # If nothing has been fetched, the amount of lines is still zero. |
66c36198 | 332 | # In this case display a hint about no data. |
c7d55d7f | 333 | unless ($lines) { |
186c0ddd | 334 | print "<tr><td bgcolor='$color{'color22'}' colspan='6' align='center'>$Lang::tr{'no entries'}</td></tr>\n"; |
c7d55d7f | 335 | } |
3e10b3de SS |
336 | |
337 | print "</table><br>\n"; | |
338 | ||
339 | &Header::closebox(); | |
340 | ||
341 | # Close database connection. | |
342 | $database_handle->disconnect(); | |
343 | ||
344 | &Header::closebigbox(); | |
345 | ||
346 | &Header::closepage(); | |
347 | ||
348 | # | |
349 | ## Function for easy select generation. | |
350 | # | |
351 | sub generate_select($$) { | |
352 | my ($name, $type) = @_; | |
353 | ||
354 | my $start = 1; | |
355 | my $stop; | |
356 | ||
357 | # Adjust start and stop by the given type. | |
358 | if ($type eq "days") { | |
359 | $stop = 31; | |
360 | } elsif ($type eq "months") { | |
361 | $stop = 12; | |
362 | } elsif ($type = "years") { | |
363 | $stop = $year; | |
364 | $start = $stop - 10; | |
365 | } | |
366 | ||
367 | # Print select HTML tag. | |
368 | print "<select name='$name' size='1'>\n"; | |
369 | ||
370 | # Loop through the range. | |
371 | for ( my $i = $start; $i <= $stop; $i++) { | |
372 | print "\t<option "; | |
373 | ||
374 | # Check and select the current processed item. | |
375 | if ($i == $cgiparams{$name}) { | |
376 | print 'selected="selected" '; | |
377 | } | |
378 | ||
379 | # Check if months are processed and display the corresponding names. | |
380 | if ($type eq "months") { | |
381 | print "value='$i'>$monthhash{$i}</option>\n"; | |
382 | } else { | |
383 | print "value='$i'>$i</option>\n"; | |
384 | } | |
385 | } | |
386 | ||
387 | # Close select HTML tag. | |
388 | print "</select>\n\n"; | |
389 | } |