From patchwork Mon May 25 09:17:29 2020 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 3127 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 49Vs23548Wz3xQr for ; Mon, 25 May 2020 09:17:47 +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 49Vs212nWdz1SP; Mon, 25 May 2020 09:17:45 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 49Vs2068Thz2yNV; Mon, 25 May 2020 09:17:44 +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 49Vs1y3KT2z2xpX for ; Mon, 25 May 2020 09:17:42 +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 49Vs1x0FmGz1S5; Mon, 25 May 2020 09:17:40 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1590398261; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc; bh=5OGL8bWKcKYm0ejjBLY6b4VwfhEO3qGt9EMiyvIeXtE=; b=JK1H72R9B8Np3xCPTcIEjVPtv+pIsgpiALPh9E2zs09c/8VR9nxbKw4nzYYObWHPlbXccp GFhYKollJoZ1ScAg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1590398261; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc; bh=5OGL8bWKcKYm0ejjBLY6b4VwfhEO3qGt9EMiyvIeXtE=; b=g1EmcAr72wKDD2iO7QkFvVwUXtZrE1GJunGxSgLZ2Bzu8pg/wMGEK/6KRY4E23LWbfjdch gXhOEUyWre1qk8eG8G3eKJhPFHOzSP5M7k8gcUCD6kyBi8bkqpH6zAgh2WsYEnws3Waf9C lvZXwIUx3x05hSXzWyfFV74zVkMbKiHxHXg+Rw4R124GYeDFXFW8xPyqJ2ehr2kotBubJc HMwxtfkYBIr9g28vqnNRlbpQ7hZPlELXcT34sXXAJbEKo1TJOQq6IKlSHHYlEsqdZx6N/y UmrsRSKhPzSrsNIS21i4He3ORaorM4e2YZgw0RkbY/nHwUrcMRuSGVZoArX4Jw== From: Michael Tremer To: development@lists.ipfire.org Subject: [PATCH 1/2] openvpn: Create database schema if not exists Date: Mon, 25 May 2020 10:17:29 +0100 Message-Id: <20200525091730.4059-1-michael.tremer@ipfire.org> Authentication-Results: mail01.ipfire.org; auth=pass smtp.mailfrom=michael.tremer@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: , Cc: Michael Tremer Errors-To: development-bounces@lists.ipfire.org Sender: "Development" Signed-off-by: Michael Tremer --- html/cgi-bin/logs.cgi/ovpnclients.dat | 15 +++++++++++++++ 1 file changed, 15 insertions(+) diff --git a/html/cgi-bin/logs.cgi/ovpnclients.dat b/html/cgi-bin/logs.cgi/ovpnclients.dat index 2009990ec..a064893a1 100755 --- a/html/cgi-bin/logs.cgi/ovpnclients.dat +++ b/html/cgi-bin/logs.cgi/ovpnclients.dat @@ -95,6 +95,21 @@ unless ($to_datestring ge $from_datestring) { $errormessage = "$Lang::tr{'error the to date has to be later than the from date'}"; } +# Initialise database +my $cursor = $database_handle->prepare(" + CREATE TABLE IF NOT EXISTS sessions( + common_name TEXT NOT NULL, + connected_at TEXT NOT NULL, + disconnected_at TEXT, + bytes_received INTEGER, + bytes_sent INTEGER + ); + + -- Create index for speeding up searches + CREATE INDEX IF NOT EXISTS sessions_common_name ON sessions(common_name); +"); +$cursor->execute(); + my $database_query = qq( SELECT common_name, SUM( From patchwork Mon May 25 09:17:30 2020 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 3128 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 49Vs2357z9z43x9 for ; Mon, 25 May 2020 09:17:47 +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 49Vs220yQBz37W; Mon, 25 May 2020 09:17:46 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 49Vs212TVqz2yr5; Mon, 25 May 2020 09:17:45 +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 49Vs1y4ZqYz2y3W for ; Mon, 25 May 2020 09:17:42 +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 49Vs1y2sLgz29W; Mon, 25 May 2020 09:17:42 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1590398262; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:in-reply-to:in-reply-to:references:references; bh=PlnRvVZkFI12TEby9WRY2fcjrhGaoWOc/ptGzGOOTOE=; b=fYN1P2ivsIVVlmBvLoQKBG9XY+W6xR3p43EhiA4m8nFSPD1qPYlntArrU+RYFDMAlt9ssd QHQSLbhpT1aYxnBA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1590398262; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:in-reply-to:in-reply-to:references:references; bh=PlnRvVZkFI12TEby9WRY2fcjrhGaoWOc/ptGzGOOTOE=; b=LytX390qwxjdbHEPKaZCw3sksExZwS2VwQjAyp7j/Nl5iBL9OoGTGW96xqxNcqNZLuII1g pMC9NQC4u+ZqUKl08HcCoHcv0zbkT2bh029sSZ2oMo01OYaqZn8J3CPTFCvTzhDtqe+z+D WSdaCUVLjDjl2evkyzLFsFuLpK3COAqXWlxZp6jwX9IK/WATwRTpkfn5U9bkbDktqMcwe/ 4tSQh3rDwNdMbTgIUpDyWRrTHNjtuehZhVGcEneUUZvf+XmVitHkKgkGnsxBaXYprc43Ed q7J0HH23gRsRWl/TKBggfEgsw2yn13L5qFdEddCFqoGhCzSLmtC2dmHbFGX18Q== From: Michael Tremer To: development@lists.ipfire.org Subject: [PATCH 2/2] core145: Enable OpenVPN metrics collection Date: Mon, 25 May 2020 10:17:30 +0100 Message-Id: <20200525091730.4059-2-michael.tremer@ipfire.org> In-Reply-To: <20200525091730.4059-1-michael.tremer@ipfire.org> References: <20200525091730.4059-1-michael.tremer@ipfire.org> Authentication-Results: mail01.ipfire.org; auth=pass smtp.mailfrom=michael.tremer@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: , Cc: Michael Tremer Errors-To: development-bounces@lists.ipfire.org Sender: "Development" Signed-off-by: Michael Tremer --- config/rootfiles/core/145/update.sh | 7 +++++++ 1 file changed, 7 insertions(+) diff --git a/config/rootfiles/core/145/update.sh b/config/rootfiles/core/145/update.sh index 5f5394f78..9ff64f7f9 100644 --- a/config/rootfiles/core/145/update.sh +++ b/config/rootfiles/core/145/update.sh @@ -73,6 +73,13 @@ for package in perl-DBI perl-DBD-SQLite; do rm -f /opt/pakfire/db/rootfiles/$package done +# Enable OpenVPN metrics collection +cat <> /var/ipfire/ovpn/server.conf +# Log clients connecting/disconnecting +client-connect "/usr/sbin/openvpn-metrics client-connect" +client-disconnect "/usr/sbin/openvpn-metrics client-disconnect" +EOF + # Start services /etc/init.d/vnstat start /etc/init.d/unbound restart