html/cgi-bin/logs.cgi/ovpnclients.dat

   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);
  37 &General::readhash("/srv/web/ipfire/html/themes/ipfire/include/colors.txt", \%color);
  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
  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
  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
 113 my $database_query = qq(
 114         SELECT
 115         common_name, SUM(
 116                 STRFTIME('%s', (
 117                         CASE
 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')
 121                         END
 122                 ), 'utc') -
 123                 STRFTIME('%s', (
 124                         CASE
 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')
 128                         END
 129                 ), 'utc')
 130         ) AS duration
 131         FROM sessions
 132         WHERE
 133                 (
 134                         disconnected_at IS NULL
 135                         OR
 136                         DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day')
 137                 )
 138                 AND
 139                 DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds')
 140         GROUP BY common_name
 141         ORDER BY common_name, duration DESC;
 142 );
 143
 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
 148                 WHERE
 149                         common_name = '$cgiparams{"CONNECTION_NAME"}'
 150                         AND (
 151                                 DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day')
 152                                 AND
 153                                 DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds')
 154                         )
 155                 ORDER BY connected_at;
 156         );
 157 }
 158
 159 my $statement_handle;
 160 my $database_return_value;
 161
 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 }
 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&nbsp;</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'}:&nbsp\;\n";
 198                         &generate_select("FROM_DAY", "days");
 199                 print "</td>\n";
 200
 201                 print "<td class='base'>$Lang::tr{'month'}:&nbsp\;\n";
 202                         &generate_select("FROM_MONTH", "months");
 203                 print "</td>\n";
 204
 205                 print "<td class='base'>$Lang::tr{'year'}:&nbsp\;\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'}:&nbsp\;\n";
 218                         &generate_select("TO_DAY", "days");
 219                 print "</td>\n";
 220
 221                 print "<td class='base'>$Lang::tr{'month'}:&nbsp\;\n";
 222                         &generate_select("TO_MONTH", "months");
 223                 print "</td>\n";
 224
 225                 print "<td class='base'>$Lang::tr{'year'}:&nbsp\;\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";
 237                                 print "<option value=''>$Lang::tr{'all'}</option>\n";
 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
 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'}) {
 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";
 284         } else {
 285                 print "<td $col><b>$Lang::tr{'total connection time'}</b>\n";
 286         }
 287
 288         print "</tr>\n";
 289
 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]);
 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'}) {
 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";
 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++;
 328         }
 329 }
 330
 331 # If nothing has been fetched, the amount of lines is still zero.
 332 # In this case display a hint about no data.
 333 unless ($lines) {
 334         print "<tr><td bgcolor='$color{'color22'}' colspan='6' align='center'>$Lang::tr{'no entries'}</td></tr>\n";
 335 }
 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 }