From patchwork Mon Apr 13 11:50:18 2020 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 2960 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 4916PZ2csnz3yBt for ; Mon, 13 Apr 2020 11:50:26 +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 4916PX6bkxz2kn; Mon, 13 Apr 2020 11:50:24 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4916PX3pnrz30F5; Mon, 13 Apr 2020 11:50:24 +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 4916PV62Phz2yTF for ; Mon, 13 Apr 2020 11:50:22 +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 4916PV47wHz2h9; Mon, 13 Apr 2020 11:50:22 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1586778622; 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=Q3wYcwvQed38ETrMl9pCwJIDn+k+NOsLv3fdoOmBh/w=; b=D+Yi0g1MoiS3reGMKsJRTVOVHLZDw7C+Pkze5LeI4e76whgb3U5MXowAdYN4dT6+Lz/Drg In16vIL8d5ll/PBA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1586778622; 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=Q3wYcwvQed38ETrMl9pCwJIDn+k+NOsLv3fdoOmBh/w=; b=UpspGGUxMZ/wkyvfY1Qo2TwBUe3YlFHeNyeItlXWyOz2elEekpmcgNDOGxlsdgmk/xDpiS Y6SEhi8wTg+nyFpAakwwHcxvkYs9B5a3lSP5igAQclF6x4nyKt5pdlnyifLIaOUSACu4fc 4bQODcDkCI4F4YDhJK3W4c4kZ634uy2zoutd1EOBGLU2jBRJSfyGW8z+QSDVhxZpZ0DGqo MNx4NXQaXchIWqn5Z+rEyhKrx36R8BfVtvwo7Z1SjGgMK66QUzhn+sFkMv4lLaRj5NV2mh cVf2CoiGdxW0aps/b3Tpiqpprsd8Z01+0aCCSVW0cZpoT3rsFeSwQISgWFzidw== From: Michael Tremer To: development@lists.ipfire.org Subject: [PATCH 2/2] openvpn: Store connection times in ASCII timestamps Date: Mon, 13 Apr 2020 11:50:18 +0000 Message-Id: <20200413115018.3115-2-michael.tremer@ipfire.org> In-Reply-To: <20200413115018.3115-1-michael.tremer@ipfire.org> References: <20200413115018.3115-1-michael.tremer@ipfire.org> MIME-Version: 1.0 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" This format seems to be a lot easier to handle in SQLite queries. Signed-off-by: Michael Tremer --- src/scripts/openvpn-metrics | 11 ++++++----- 1 file changed, 6 insertions(+), 5 deletions(-) diff --git a/src/scripts/openvpn-metrics b/src/scripts/openvpn-metrics index 30b3932c5..ac0cab909 100755 --- a/src/scripts/openvpn-metrics +++ b/src/scripts/openvpn-metrics @@ -114,8 +114,8 @@ class OpenVPNMetrics(object): db.executescript(""" CREATE TABLE IF NOT EXISTS sessions( common_name TEXT NOT NULL, - connected_at INTEGER NOT NULL, - duration INTEGER, + connected_at TEXT NOT NULL, + disconnected_at TEXT, bytes_received INTEGER, bytes_sent INTEGER ); @@ -144,7 +144,7 @@ class OpenVPNMetrics(object): c = self.db.cursor() c.execute("INSERT INTO sessions(common_name, connected_at) \ - VALUES(?, ?)", (common_name, time_unix)) + VALUES(?, DATETIME(?, 'unixepoch'))", (common_name, time_unix)) self.db.commit() def client_disconnect(self, args): @@ -159,8 +159,9 @@ class OpenVPNMetrics(object): % (common_name, duration, bytes_received, bytes_sent)) c = self.db.cursor() - c.execute("UPDATE sessions SET duration = ?, bytes_received = ?, \ - bytes_sent = ? WHERE common_name = ? AND duration IS NULL", + c.execute("UPDATE sessions SET disconnected_at = DATETIME(connected_at, '+' || ? || ' seconds'), \ + bytes_received = ?, bytes_sent = ? \ + WHERE common_name = ? AND disconnected_at IS NULL", (duration, bytes_received, bytes_sent, common_name)) self.db.commit()