diff mbox series

[02/19] OpenVPN: Fix query when selecting sessions only

Message ID 20200413074550.2735-2-stefan.schantl@ipfire.org
State Accepted
Commit 70e1d5874580cd07a8e38050b80d190ce0805a09
Headers
Series [01/19] openvpn: Add WUI page for client usage statistics |

Commit Message

Stefan Schantl April 13, 2020, 7:45 a.m. UTC 
  From: Michael Tremer <michael.tremer@ipfire.org>

Previously some sessions were selected which did not qualify
for the search.

Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
---
 html/cgi-bin/logs.cgi/ovpnclients.dat | 28 +++++++++++++++------------
 1 file changed, 16 insertions(+), 12 deletions(-)
diff mbox series

Patch

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;
 	);
 }