From patchwork Mon Apr 13 07:45:33 2020 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Stefan Schantl X-Patchwork-Id: 2941 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) 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 web04.haj.ipfire.org (Postfix) with ESMTPS id 4910zZ1gxSz3yBt 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) client-signature ECDSA (P-384)) (Client CN "mail02.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4910zX4F5kz2h9; Mon, 13 Apr 2020 07:46:00 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4910zW66N7z30FF; 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) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4910zT0s2lz2yRK 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 4910zS1YWBzRj; Mon, 13 Apr 2020 07:45:56 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1586763956; 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: in-reply-to:in-reply-to:references:references; bh=fL72oFCCcqoIQ+mkXoQ4jgAhvR2/J6w2pvMFmFYrp6I=; b=tcVLv8opw4jy1IxnvJ45B/1cGGevwS+UE5Sgmhbm2U1jbruYyZnzkX0wsDDWl40bB9YZbS arYCjFNmgn8/zaAg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1586763956; 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: in-reply-to:in-reply-to:references:references; bh=fL72oFCCcqoIQ+mkXoQ4jgAhvR2/J6w2pvMFmFYrp6I=; b=KdQ3NLBCQnrgk5qI2uvDPgjUO2RvRuBEA0Ev/ZFf3jxgfPhTRMnDdcwYmdFL6dG2eahOcb sn0BWnWovVWhOBETa/Vyyw2Nn6pAeobs+hwft1o3Bk2e0+0/aBvN1W4gFWHLU0HzgFa3P/ wxMyHubnTyG+ajPqrPO2stLePtq8gGxJKdi7fHS+xq2M5blkLNtBVAi7/fFCT98QZZVVqF p9kdln0lRJ97re1z+gNI8cMzFdz1bYFjiPvkV4hdbylSa1IPckdAPtZIrxJMfWtsW9rdPF jEmeA07X8wRB1EDs/y3+0tPeCRaF/QzIyIm3F7W/cQgIhatRZESgThVQXUuo6Q== 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> References: <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" 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/ovpnclients.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 = qq( ELSE DATETIME('$from_datestring', 'localtime', 'start of day') END ), 'utc') - ) + ) AS duration FROM sessions WHERE - disconnected_at IS NULL - OR - DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') - OR + ( + disconnected_at IS NULL + OR + DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') + ) + AND DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') GROUP BY common_name - ORDER BY common_name; + ORDER BY common_name, duration DESC; ); if ($cgiparams{'CONNECTION_NAME'}) { $database_query = qq( - SELECT * - FROM sessions + 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')); + common_name = '$cgiparams{"CONNECTION_NAME"}' + AND ( + DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') + AND + DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') + ) + ORDER BY connected_at; ); }