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

[Stefan Schantl <stefan.schantl@ipfire.org>]

[-- Attachment #1: Type: text/plain, Size: 2275 bytes --]

From: Michael Tremer <michael.tremer(a)ipfire.org>

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

Signed-off-by: Michael Tremer <michael.tremer(a)ipfire.org>
---
 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;
 	);
 }
 
-- 
2.26.0