From mboxrd@z Thu Jan 1 00:00:00 1970 From: Stefan Schantl To: development@lists.ipfire.org Subject: [PATCH 02/19] OpenVPN: Fix query when selecting sessions only Date: Mon, 13 Apr 2020 09:45:33 +0200 Message-ID: <20200413074550.2735-2-stefan.schantl@ipfire.org> In-Reply-To: <20200413074550.2735-1-stefan.schantl@ipfire.org> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1582706908732683802==" List-Id: --===============1582706908732683802== Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable From: Michael Tremer Previously some sessions were selected which did not qualify for the search. Signed-off-by: Michael Tremer --- html/cgi-bin/logs.cgi/ovpnclients.dat | 28 +++++++++++++++------------ 1 file changed, 16 insertions(+), 12 deletions(-) diff --git a/html/cgi-bin/logs.cgi/ovpnclients.dat b/html/cgi-bin/logs.cgi/ov= pnclients.dat index 703f4e507..46667a9d1 100755 --- a/html/cgi-bin/logs.cgi/ovpnclients.dat +++ b/html/cgi-bin/logs.cgi/ovpnclients.dat @@ -107,27 +107,31 @@ my $database_query =3D qq( ELSE DATETIME('$from_datestring', 'l= ocaltime', 'start of day') END ), 'utc') - ) + ) AS duration FROM sessions WHERE - disconnected_at IS NULL - OR - DATETIME(disconnected_at, 'localtime') > DATETIME('$from_dat= estring', 'localtime', 'start of day') - OR + ( + disconnected_at IS NULL + OR + DATETIME(disconnected_at, 'localtime') > DATETIME('$from_da= testring', 'localtime', 'start of day') + ) + AND DATETIME(connected_at, 'localtime') < DATETIME('$to_datestri= ng', 'localtime', 'start of day', '+86399 seconds') GROUP BY common_name - ORDER BY common_name; + ORDER BY common_name, duration DESC; ); =20 if ($cgiparams{'CONNECTION_NAME'}) { $database_query =3D qq( - SELECT * - FROM sessions + 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')); + common_name =3D '$cgiparams{"CONNECTION_NAME"}' + AND ( + DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'l= ocaltime', 'start of day') + AND + DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localt= ime', 'start of day', '+86399 seconds') + ) + ORDER BY connected_at; ); } =20 --=20 2.26.0 --===============1582706908732683802==--