Signed-off-by: Stefan Schantl --- html/cgi-bin/logs.cgi/ovpnclients.dat | 327 ++++++++++++++++++++++++++ langs/en/cgi-bin/en.pl | 2 + 2 files changed, 329 insertions(+) create mode 100755 html/cgi-bin/logs.cgi/ovpnclients.dat diff --git a/html/cgi-bin/logs.cgi/ovpnclients.dat b/html/cgi-bin/logs.cgi/ovpnclients.dat new file mode 100755 index 000000000..703f4e507 --- /dev/null +++ b/html/cgi-bin/logs.cgi/ovpnclients.dat @@ -0,0 +1,327 @@ +#!/usr/bin/perl +############################################################################### +# # +# IPFire.org - A linux based firewall # +# Copyright (C) 2020 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/".$mainsettings{'THEME'}."/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"}); + +my $database_query = qq( + SELECT + common_name, SUM( + STRFTIME('%s', ( + CASE + WHEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') + THEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') + ELSE DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') + END + ), 'utc') - + STRFTIME('%s', ( + CASE + WHEN DATETIME(connected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') + THEN DATETIME(connected_at, 'localtime') + ELSE DATETIME('$from_datestring', 'localtime', 'start of day') + END + ), 'utc') + ) + FROM sessions + WHERE + disconnected_at IS NULL + OR + DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') + OR + DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') + GROUP BY common_name + ORDER BY common_name; +); + +if ($cgiparams{'CONNECTION_NAME'}) { + $database_query = qq( + SELECT * + FROM sessions + WHERE + common_name = '$cgiparams{"CONNECTION_NAME"}' AND ( + DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') + OR + DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')); + ); +} + +# Prepare SQL statement. +my $statement_handle = $database_handle->prepare($database_query); + +# Execute SQL statement and get retun value if any error happened. +my $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=""; + +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 = $row[3]; + my $connection_bytes_sent = $row[4]; + + # Colorize columns. + if ($lines % 2) { + $col="bgcolor='$color{'color22'}'"; + } else { + $col="bgcolor='$color{'color20'}'"; + } + +print < + + + + + + +END +; + + # Increase lines count. + $lines++; + + } + +print "
$connection_name$connection_open_time$connection_close_time$connection_bytes_recieved$connection_bytes_sent

\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"; +} diff --git a/langs/en/cgi-bin/en.pl b/langs/en/cgi-bin/en.pl index a68c8f411..9d3ca5c6d 100644 --- a/langs/en/cgi-bin/en.pl +++ b/langs/en/cgi-bin/en.pl @@ -1915,6 +1915,7 @@ 'ovpn' => 'OpenVPN', 'ovpn add conf' => 'Additional configuration', 'ovpn con stat' => 'OpenVPN Connection Statistics', +'ovpn connection name' => 'Connection name', 'ovpn config' => 'OVPN-Config', 'ovpn crypt options' => 'Cryptographic options', 'ovpn device' => 'OpenVPN device:', @@ -1947,6 +1948,7 @@ 'ovpn reneg sec' => 'Session key lifetime:', 'ovpn routes push' => 'Routes (one per line) e.g. 192.168.10.0/255.255.255.0 192.168.20.0/24', 'ovpn routes push options' => 'Route push options', +'ovpn rw connection log' => 'OpenVPN RW connections log', 'ovpn server status' => 'Current OpenVPN server status:', 'ovpn subnet' => 'OpenVPN subnet:', 'ovpn subnet is invalid' => 'OpenVPN subnet is invalid.', -- 2.26.0