#!/usr/bin/perl ############################################################################### # # # IPFire.org - A linux based firewall # # Copyright (C) 2020 - 2025 IPFire Team # # # # This program is free software: you can redistribute it and/or modify # # it under the terms of the GNU General Public License as published by # # the Free Software Foundation, either version 3 of the License, or # # (at your option) any later version. # # # # This program is distributed in the hope that it will be useful, # # but WITHOUT ANY WARRANTY; without even the implied warranty of # # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # # GNU General Public License for more details. # # # # You should have received a copy of the GNU General Public License # # along with this program. If not, see . # # # ############################################################################### use strict; use POSIX(); use DBI; # enable only the following on debugging purpose #use warnings; #use CGI::Carp 'fatalsToBrowser'; require '/var/ipfire/general-functions.pl'; require "${General::swroot}/lang.pl"; require "${General::swroot}/header.pl"; my %color = (); my %mainsettings = (); &General::readhash("${General::swroot}/main/settings", \%mainsettings); &General::readhash("/srv/web/ipfire/html/themes/ipfire/include/colors.txt", \%color); # Path and file of the OVPN connections database. my $database = "/var/ipfire/ovpn/clients.db"; my %cgiparams=(); my %logsettings=(); my %ovpnsettings=(); my $errormessage=''; # Hash wich contains the month numbers and the translated names for easy access. my %monthhash = ( "1" => "$Lang::tr{'january'}", "2" => "$Lang::tr{'february'}", "3" => "$Lang::tr{'march'}", "4" => "$Lang::tr{'april'}", "5" => "$Lang::tr{'may'}", "6" => "$Lang::tr{'june'}", "7" => "$Lang::tr{'july'}", "8" => "$Lang::tr{'august'}", "9" => "$Lang::tr{'september'}", "10" => "$Lang::tr{'october'}", "11" => "$Lang::tr{'november'}", "12" => "$Lang::tr{'december'}" ); # Get current time. my ($sec,$min,$hour,$mday,$month,$year,$wday,$yday,$isdst) = localtime(time); # Adjust month, because Jan starts as month "0". $month = $month+1; # Adjust year number. $year = $year+1900; # Assign default vaules. $cgiparams{'FROM_DAY'} = $mday; $cgiparams{'FROM_MONTH'} = $month; $cgiparams{'FROM_YEAR'} = $year; $cgiparams{'TO_DAY'} = $mday; $cgiparams{'TO_MONTH'} = $month; $cgiparams{'TO_YEAR'} = $year; &Header::getcgihash(\%cgiparams); # Read-in OpenVPN settings and connections. &General::readhasharray("${General::swroot}/ovpn/ovpnconfig", \%ovpnsettings); # Init DB Module and connect to the database. my $database_handle = DBI->connect("DBI:SQLite:dbname=$database", "", "", { RaiseError => 1 }); # Generate datestrings for SQL queries. my $from_datestring = sprintf '%04d-%02d-%02d', ($cgiparams{"FROM_YEAR"}, $cgiparams{"FROM_MONTH"}, $cgiparams{"FROM_DAY"}); my $to_datestring = sprintf '%04d-%02d-%02d', ($cgiparams{"TO_YEAR"}, $cgiparams{"TO_MONTH"}, $cgiparams{"TO_DAY"}); # Check if the to datestring is later than the from datestring. unless ($to_datestring ge $from_datestring) { $errormessage = "$Lang::tr{'error the to date has to be later than the from date'}"; } # Initialise database my $cursor = $database_handle->prepare(" CREATE TABLE IF NOT EXISTS sessions( common_name TEXT NOT NULL, connected_at TEXT NOT NULL, disconnected_at TEXT, bytes_received INTEGER, bytes_sent INTEGER ); -- Create index for speeding up searches CREATE INDEX IF NOT EXISTS sessions_common_name ON sessions(common_name); "); $cursor->execute(); my $database_query = qq( SELECT common_name, SUM( STRFTIME('%s', ( CASE WHEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds') THEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') ELSE DATETIME('$to_datestring', 'start of day', '+86399 seconds') END ), 'utc') - STRFTIME('%s', ( CASE WHEN DATETIME(connected_at, 'localtime') > DATETIME('$from_datestring', 'start of day') THEN DATETIME(connected_at, 'localtime') ELSE DATETIME('$from_datestring', 'start of day') END ), 'utc') ) AS duration FROM sessions WHERE ( disconnected_at IS NULL OR DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day') ) AND DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds') GROUP BY common_name ORDER BY common_name, duration DESC; ); if (($cgiparams{'CONNECTION_NAME'}) && ($cgiparams{'CONNECTION_NAME'} =~ /^[a-zA-Z0-9]+$/)) { $database_query = qq( SELECT common_name, DATETIME(connected_at, 'localtime'), DATETIME(disconnected_at, 'localtime'), bytes_received, bytes_sent, STRFTIME('%s', DATETIME(disconnected_at)) - STRFTIME('%s', DATETIME(connected_at)) AS duration FROM sessions WHERE common_name = '$cgiparams{"CONNECTION_NAME"}' AND ( DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day') AND DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds') ) ORDER BY connected_at; ); } my $statement_handle; my $database_return_value; # Only process SQL actions if there is no error message. unless ($errormessage) { # Prepare SQL statement. $statement_handle = $database_handle->prepare($database_query); # Execute SQL statement and get retun value if any error happened. $database_return_value = $statement_handle->execute(); } # If an error has been returned, assign it to the errorstring value for displaying. if($database_return_value < 0) { $errormessage = "$DBI::errstr"; } &Header::showhttpheaders(); &Header::openpage($Lang::tr{'ovpn rw connection log'}, 1, ''); &Header::openbigbox('100%', 'left', '', $errormessage); if ($errormessage) { &Header::openbox('100%', 'left', $Lang::tr{'error messages'}); print "$errormessage \n"; &Header::closebox(); } &Header::openbox('100%', 'left', "$Lang::tr{'settings'}:"); print "
\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "
$Lang::tr{'from'}:
$Lang::tr{'day'}: \;\n"; &generate_select("FROM_DAY", "days"); print "$Lang::tr{'month'}: \;\n"; &generate_select("FROM_MONTH", "months"); print "$Lang::tr{'year'}: \;\n"; &generate_select("FROM_YEAR", "years"); print "

$Lang::tr{'to'}:
$Lang::tr{'day'}: \;\n"; &generate_select("TO_DAY", "days"); print "$Lang::tr{'month'}: \;\n"; &generate_select("TO_MONTH", "months"); print "$Lang::tr{'year'}: \;\n"; &generate_select("TO_YEAR", "years"); print "

$Lang::tr{'ovpn connection name'}:\n"; print "\n"; print "
\n"; print "
\n"; &Header::closebox(); &Header::openbox('100%', 'left', $Lang::tr{'log'}); my $lines = 0; print ""; my $col = "bgcolor='$color{'color20'}'"; print "\n"; print "\n"; if ($cgiparams{'CONNECTION_NAME'}) { print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; } else { print "\n"; # Only try to fetch the DB items if there is no error message. unless ($errormessage) { while(my @row = $statement_handle->fetchrow_array()) { # Assign some nice to read variable names for the DB fields. my $connection_name = $row[0]; my $connection_open_time = $row[1]; my $connection_close_time = $row[2]; my $connection_bytes_recieved = &General::formatBytes($row[3]); my $connection_bytes_sent = &General::formatBytes($row[4]); my $duration = &General::format_time($row[5]); # Colorize columns. if ($lines % 2) { $col="bgcolor='$color{'color20'}'"; } else { $col="bgcolor='$color{'color22'}'"; } print "\n"; print "\n"; if ($cgiparams{'CONNECTION_NAME'}) { print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; } else { # Convert total connection time into human-readable format. my $total_time = &General::format_time($row[1]); print "\n"; } print "\n"; # Increase lines count. $lines++; } } # If nothing has been fetched, the amount of lines is still zero. # In this case display a hint about no data. unless ($lines) { print "\n"; } print "
$Lang::tr{'ovpn connection name'}$Lang::tr{'connected'}$Lang::tr{'disconnected'}$Lang::tr{'duration'}$Lang::tr{'received'}$Lang::tr{'sent'}$Lang::tr{'total connection time'}\n"; } print "
$connection_name$connection_open_time$connection_close_time$duration$connection_bytes_recieved$connection_bytes_sent$total_time
$Lang::tr{'no entries'}

\n"; &Header::closebox(); # Close database connection. $database_handle->disconnect(); &Header::closebigbox(); &Header::closepage(); # ## Function for easy select generation. # sub generate_select($$) { my ($name, $type) = @_; my $start = 1; my $stop; # Adjust start and stop by the given type. if ($type eq "days") { $stop = 31; } elsif ($type eq "months") { $stop = 12; } elsif ($type = "years") { $stop = $year; $start = $stop - 10; } # Print select HTML tag. print "\n\n"; }