From mboxrd@z Thu Jan 1 00:00:00 1970 From: Stefan Schantl To: development@lists.ipfire.org Subject: [PATCH 01/19] openvpn: Add WUI page for client usage statistics Date: Mon, 13 Apr 2020 09:45:32 +0200 Message-ID: <20200413074550.2735-1-stefan.schantl@ipfire.org> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============7962515163710310872==" List-Id: --===============7962515163710310872== Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable 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/ov= pnclients.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 =3D (); +my %mainsettings =3D (); +&General::readhash("${General::swroot}/main/settings", \%mainsettings); +&General::readhash("/srv/web/ipfire/html/themes/".$mainsettings{'THEME'}."/i= nclude/colors.txt", \%color); + +# Path and file of the OVPN connections database. +my $database =3D "/var/ipfire/ovpn/clients.db"; + +my %cgiparams=3D(); +my %logsettings=3D(); +my %ovpnsettings=3D(); + +my $errormessage=3D''; + +# Hash wich contains the month numbers and the translated names for easy acc= ess. +my %monthhash =3D ( + "1" =3D> "$Lang::tr{'january'}", + "2" =3D> "$Lang::tr{'february'}", + "3" =3D> "$Lang::tr{'march'}", + "4" =3D> "$Lang::tr{'april'}", + "5" =3D> "$Lang::tr{'may'}", + "6" =3D> "$Lang::tr{'june'}", + "7" =3D> "$Lang::tr{'july'}", + "8" =3D> "$Lang::tr{'august'}", + "9" =3D> "$Lang::tr{'september'}", + "10" =3D> "$Lang::tr{'october'}", + "11" =3D> "$Lang::tr{'november'}", + "12" =3D> "$Lang::tr{'december'}" +); + +# Get current time. +my ($sec,$min,$hour,$mday,$month,$year,$wday,$yday,$isdst) =3D localtime(tim= e); + +# Adjust month, because Jan starts as month "0". +$month =3D $month+1; + +# Adjust year number. +$year =3D $year+1900; + +# Assign default vaules. +$cgiparams{'FROM_DAY'} =3D $mday; +$cgiparams{'FROM_MONTH'} =3D $month; +$cgiparams{'FROM_YEAR'} =3D $year; +$cgiparams{'TO_DAY'} =3D $mday; +$cgiparams{'TO_MONTH'} =3D $month; +$cgiparams{'TO_YEAR'} =3D $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 =3D DBI->connect("DBI:SQLite:dbname=3D$database", "", ""= , { RaiseError =3D> 1 }); + +# Generate datestrings for SQL queries. +my $from_datestring =3D sprintf '%04d-%02d-%02d', ($cgiparams{"FROM_YEAR"}, = $cgiparams{"FROM_MONTH"}, $cgiparams{"FROM_DAY"}); +my $to_datestring =3D sprintf '%04d-%02d-%02d', ($cgiparams{"TO_YEAR"}, $cgi= params{"TO_MONTH"}, $cgiparams{"TO_DAY"}); + +my $database_query =3D qq( + SELECT + common_name, SUM( + STRFTIME('%s', ( + CASE + WHEN DATETIME(COALESCE(disconnected_at, CURR= ENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'localtime', 'start= of day', '+86399 seconds') + THEN DATETIME(COALESCE(disconnected_= at, CURRENT_TIMESTAMP), 'localtime') + ELSE DATETIME('$to_datestring', 'loc= altime', 'start of day', '+86399 seconds') + END + ), 'utc') - + STRFTIME('%s', ( + CASE + WHEN DATETIME(connected_at, 'localtime') > D= ATETIME('$from_datestring', 'localtime', 'start of day') + THEN DATETIME(connected_at, 'localti= me') + ELSE DATETIME('$from_datestring', 'l= ocaltime', 'start of day') + END + ), 'utc') + ) + FROM sessions + WHERE + disconnected_at IS NULL + OR + DATETIME(disconnected_at, 'localtime') > DATETIME('$from_dat= estring', 'localtime', 'start of day') + OR + DATETIME(connected_at, 'localtime') < DATETIME('$to_datestri= ng', 'localtime', 'start of day', '+86399 seconds') + GROUP BY common_name + ORDER BY common_name; +); + +if ($cgiparams{'CONNECTION_NAME'}) { + $database_query =3D qq( + SELECT * + FROM sessions + WHERE + common_name =3D '$cgiparams{"CONNECTION_NAME"}' AND ( + DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'lo= caltime', 'start of day') + OR + DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localti= me', 'start of day', '+86399 seconds')); + ); +} + +# Prepare SQL statement. +my $statement_handle =3D $database_handle->prepare($database_query); + +# Execute SQL statement and get retun value if any error happened. +my $database_return_value =3D $statement_handle->execute(); + +# If an error has been returned, assign it to the errorstring value for disp= laying. +if($database_return_value < 0) { + $errormessage =3D "$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 =3D 0; + +print ""; + +my $col=3D""; + +while(my @row =3D $statement_handle->fetchrow_array()) { + # Assign some nice to read variable names for the DB fields. + my $connection_name =3D $row[0]; + my $connection_open_time =3D $row[1]; + my $connection_close_time =3D $row[2]; + my $connection_bytes_recieved =3D $row[3]; + my $connection_bytes_sent =3D $row[4]; + + # Colorize columns.=20 + if ($lines % 2) { + $col=3D"bgcolor=3D'$color{'color22'}'"; + } else { + $col=3D"bgcolor=3D'$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) =3D @_; + + my $start =3D 1; + my $stop; + + # Adjust start and stop by the given type. + if ($type eq "days") { + $stop =3D 31; + } elsif ($type eq "months") { + $stop =3D 12; + } elsif ($type =3D "years") { + $stop =3D $year; + $start =3D $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' =3D> 'OpenVPN', 'ovpn add conf' =3D> 'Additional configuration', 'ovpn con stat' =3D> 'OpenVPN Connection Statistics', +'ovpn connection name' =3D> 'Connection name', 'ovpn config' =3D> 'OVPN-Config', 'ovpn crypt options' =3D> 'Cryptographic options', 'ovpn device' =3D> 'OpenVPN device:', @@ -1947,6 +1948,7 @@ 'ovpn reneg sec' =3D> 'Session key lifetime:', 'ovpn routes push' =3D> 'Routes (one per line) e.g. 192.168.10.0/255.255.255= .0 192.168.20.0/24', 'ovpn routes push options' =3D> 'Route push options', +'ovpn rw connection log' =3D> 'OpenVPN RW connections log', 'ovpn server status' =3D> 'Current OpenVPN server status:', 'ovpn subnet' =3D> 'OpenVPN subnet:', 'ovpn subnet is invalid' =3D> 'OpenVPN subnet is invalid.', --=20 2.26.0 --===============7962515163710310872==--