2 ###############################################################################
4 # IPFire.org - A linux based firewall #
5 # Copyright (C) 2020 IPFire Team <info@ipfire.org> #
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. #
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. #
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/>. #
20 ###############################################################################
26 # enable only the following on debugging purpose
28 #use CGI::Carp 'fatalsToBrowser';
30 require '/var/ipfire/general-functions.pl';
31 require "${General::swroot}/lang.pl";
32 require "${General::swroot}/header.pl";
35 my %mainsettings = ();
36 &General
::readhash
("${General::swroot}/main/settings", \
%mainsettings);
37 &General
::readhash
("/srv/web/ipfire/html/themes/ipfire/include/colors.txt", \
%color);
39 # Path and file of the OVPN connections database.
40 my $database = "/var/ipfire/ovpn/clients.db";
48 # Hash wich contains the month numbers and the translated names for easy access.
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'}"
65 my ($sec,$min,$hour,$mday,$month,$year,$wday,$yday,$isdst) = localtime(time);
67 # Adjust month, because Jan starts as month "0".
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;
81 &Header
::getcgihash
(\
%cgiparams);
83 # Read-in OpenVPN settings and connections.
84 &General
::readhasharray
("${General::swroot}/ovpn/ovpnconfig", \
%ovpnsettings);
86 # Init DB Module and connect to the database.
87 my $database_handle = DBI
->connect("DBI:SQLite:dbname=$database", "", "", { RaiseError
=> 1 });
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"});
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'}";
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,
108 -- Create index for speeding up searches
109 CREATE INDEX IF NOT EXISTS sessions_common_name ON sessions(common_name);
113 my $database_query = qq(
118 WHEN DATETIME
(COALESCE
(disconnected_at
, CURRENT_TIMESTAMP
), 'localtime') < DATETIME
('$to_datestring', 'start of day', '+86399 seconds')
119 THEN DATETIME
(COALESCE
(disconnected_at
, CURRENT_TIMESTAMP
), 'localtime')
120 ELSE DATETIME
('$to_datestring', 'start of day', '+86399 seconds')
125 WHEN DATETIME
(connected_at
, 'localtime') > DATETIME
('$from_datestring', 'start of day')
126 THEN DATETIME
(connected_at
, 'localtime')
127 ELSE DATETIME
('$from_datestring', 'start of day')
134 disconnected_at IS NULL
136 DATETIME
(disconnected_at
, 'localtime') > DATETIME
('$from_datestring', 'start of day')
139 DATETIME
(connected_at
, 'localtime') < DATETIME
('$to_datestring', 'start of day', '+86399 seconds')
141 ORDER BY common_name
, duration DESC
;
144 if ($cgiparams{'CONNECTION_NAME'}) {
145 $database_query = qq(
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
149 common_name
= '$cgiparams{"CONNECTION_NAME"}'
151 DATETIME
(disconnected_at
, 'localtime') > DATETIME
('$from_datestring', 'start of day')
153 DATETIME
(connected_at
, 'localtime') < DATETIME
('$to_datestring', 'start of day', '+86399 seconds')
155 ORDER BY connected_at
;
159 my $statement_handle;
160 my $database_return_value;
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);
167 # Execute SQL statement and get retun value if any error happened.
168 $database_return_value = $statement_handle->execute();
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";
176 &Header
::showhttpheaders
();
178 &Header
::openpage
($Lang::tr
{'ovpn rw connection log'}, 1, '');
180 &Header
::openbigbox
('100%', 'left', '', $errormessage);
183 &Header
::openbox
('100%', 'left', $Lang::tr
{'error messages'});
184 print "<font class='base'>$errormessage </font>\n";
188 &Header
::openbox
('100%', 'left', "$Lang::tr{'settings'}:");
190 print "<form method='post' action=\"$ENV{'SCRIPT_NAME'}\">\n";
191 print "<table width='100%'>\n";
193 print "<td class='base' colspan='2'><b>$Lang::tr{'from'}:</b></td>\n";
197 print "<td class='base'>$Lang::tr{'day'}: \;\n";
198 &generate_select
("FROM_DAY", "days");
201 print "<td class='base'>$Lang::tr{'month'}: \;\n";
202 &generate_select
("FROM_MONTH", "months");
205 print "<td class='base'>$Lang::tr{'year'}: \;\n";
206 &generate_select
("FROM_YEAR", "years");
210 print "<tr><td><br></td></tr>\n";
213 print "<td class='base' colspan='2'><b>$Lang::tr{'to'}:</b></td>\n";
217 print "<td class='base'>$Lang::tr{'day'}: \;\n";
218 &generate_select
("TO_DAY", "days");
221 print "<td class='base'>$Lang::tr{'month'}: \;\n";
222 &generate_select
("TO_MONTH", "months");
225 print "<td class='base'>$Lang::tr{'year'}: \;\n";
226 &generate_select
("TO_YEAR", "years");
230 print "<tr><td><br></td></tr>\n";
233 print "<td class='base'>$Lang::tr{'ovpn connection name'}:</td>\n";
234 print "<td class='base' colspan='2'>\n";
236 print "<select name='CONNECTION_NAME' size='1'>\n";
237 print "<option value=''>$Lang::tr{'all'}</option>\n";
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];
244 # Skip all non roadwarrior connections.
245 next unless ($ovpnsettings{"$key"}[3] eq "host");
247 # Check and mark the selected one.
248 if ($connection_name eq "$cgiparams{'CONNECTION_NAME'}") {
249 $selected = "selected";
252 print "<option value='$connection_name' $selected>$connection_name</option>\n";
260 print "<td width='100%' align='right' colspan='3'><input type='submit' name='ACTION' value='$Lang::tr{'update'}'></td>\n";
267 &Header
::openbox
('100%', 'left', $Lang::tr
{'log'});
271 print "<table width='100%' class='tbl'>";
273 my $col = "bgcolor='$color{'color20'}'";
276 print "<td width='40%' $col><b>$Lang::tr{'ovpn connection name'}</b></td>\n";
278 if ($cgiparams{'CONNECTION_NAME'}) {
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";
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";
285 print "<td $col><b>$Lang::tr{'total connection time'}</b>\n";
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]);
299 my $duration = &General
::format_time
($row[5]);
303 $col="bgcolor='$color{'color20'}'";
305 $col="bgcolor='$color{'color22'}'";
309 print "<td width='40%' $col>$connection_name</td>\n";
311 if ($cgiparams{'CONNECTION_NAME'}) {
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";
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";
318 # Convert total connection time into human-readable format.
319 my $total_time = &General
::format_time
($row[1]);
321 print "<td $col>$total_time</td>\n";
326 # Increase lines count.
331 # If nothing has been fetched, the amount of lines is still zero.
332 # In this case display a hint about no data.
334 print "<tr><td bgcolor='$color{'color22'}' colspan='6' align='center'>$Lang::tr{'no entries'}</td></tr>\n";
337 print "</table><br>\n";
341 # Close database connection.
342 $database_handle->disconnect();
344 &Header
::closebigbox
();
346 &Header
::closepage
();
349 ## Function for easy select generation.
351 sub generate_select
($$) {
352 my ($name, $type) = @_;
357 # Adjust start and stop by the given type.
358 if ($type eq "days") {
360 } elsif ($type eq "months") {
362 } elsif ($type = "years") {
367 # Print select HTML tag.
368 print "<select name='$name' size='1'>\n";
370 # Loop through the range.
371 for ( my $i = $start; $i <= $stop; $i++) {
374 # Check and select the current processed item.
375 if ($i == $cgiparams{$name}) {
376 print 'selected="selected" ';
379 # Check if months are processed and display the corresponding names.
380 if ($type eq "months") {
381 print "value='$i'>$monthhash{$i}</option>\n";
383 print "value='$i'>$i</option>\n";
387 # Close select HTML tag.
388 print "</select>\n\n";