From patchwork Mon Apr 13 07:45:32 2020 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Stefan Schantl X-Patchwork-Id: 2942 Return-Path: Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4910zZ6Jkpz3yC0 for ; Mon, 13 Apr 2020 07:46:02 +0000 (UTC) Received: from mail02.haj.ipfire.org (mail02.haj.ipfire.org [172.28.1.201]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384 client-signature ECDSA (P-384) client-digest SHA384) (Client CN "mail02.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4910zW66JYz2bx; Mon, 13 Apr 2020 07:45:59 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4910zW2pzxz2xkV; Mon, 13 Apr 2020 07:45:59 +0000 (UTC) Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384 client-signature ECDSA (P-384) client-digest SHA384) (Client CN "mail01.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4910zT0CjCz2xkV for ; Mon, 13 Apr 2020 07:45:57 +0000 (UTC) Received: from [127.0.0.1] (localhost [127.0.0.1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384) (Client did not present a certificate) by mail01.ipfire.org (Postfix) with ESMTPSA id 4910zQ51YVzgh; Mon, 13 Apr 2020 07:45:54 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1586763955; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding; bh=rIC3zh3ILC7tsG/FHQW38nJEZQRBCy++oahcZJ/KEkY=; b=Hj3Cm3nS1XBrh/SI/TKQeXFKpSrTzIZiKWTZPxl38BeSIjLFzu1flxk19H7T9ARRhAjmEq 0fQ3PNZS5pmv7gBg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1586763955; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding; bh=rIC3zh3ILC7tsG/FHQW38nJEZQRBCy++oahcZJ/KEkY=; b=DRyD3YYwn4Bhi78aIOqzfkXm3ZTGP408UcV72r1UR1pqtCgD1sZ53wGJXly9sMahJApdly x9lK0oWg6wrOeAPBSNe7IgiDG2sxJNpm0DWdwXYMOesT9MWGQzDAEqeLjfkFFuYmUUAMpM 6nKY9u9YwuIAIFdN55VIR/mHi+fL0G+b4Rflx+l0qvnLCtry7+CPUbx3iMo+fLwv3wXLX3 kTnVMUTbqs5ej3DjDFEH2mZfOUXSlSKKkOUyWZwJcAWikbVuuGa3RfmUd63NzZdZyij3MT oANz1ok+EMOKa+mpODpOwcbf3suufFn3Dy/iX0T9AP6ZbKEOid59o+rH7AJuig== 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 Authentication-Results: mail01.ipfire.org; auth=pass smtp.mailfrom=stefan.schantl@ipfire.org X-BeenThere: development@lists.ipfire.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: IPFire development talk List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: development-bounces@lists.ipfire.org Sender: "Development" 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.',