From mboxrd@z Thu Jan 1 00:00:00 1970 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> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0519992456344617256==" List-Id: --===============0519992456344617256== Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable 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): =20 c =3D 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() =20 def client_disconnect(self, args): @@ -159,8 +159,9 @@ class OpenVPNMetrics(object): % (common_name, duration, bytes_received, bytes_sent)) =20 c =3D self.db.cursor() - c.execute("UPDATE sessions SET duration =3D ?, bytes_received =3D ?, \ - bytes_sent =3D ? WHERE common_name =3D ? AND duration IS NULL", + c.execute("UPDATE sessions SET disconnected_at =3D DATETIME(connected_at, = '+' || ? || ' seconds'), \ + bytes_received =3D ?, bytes_sent =3D ? \ + WHERE common_name =3D ? AND disconnected_at IS NULL", (duration, bytes_received, bytes_sent, common_name)) self.db.commit() =20 --=20 2.20.1 --===============0519992456344617256==--