OpenVPN: Fix query when selecting sessions only

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

Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
Signed-off-by: Arne Fitzenreiter <arne_f@ipfire.org>

diff --git a/html/cgi-bin/logs.cgi/ovpnclients.dat b/html/cgi-bin/logs.cgi/ovpnclients.dat
index 703f4e5078c2acb23273351a2a982d0a09eab999..46667a9d1f8cd6a000be9af88a7026ca267ca9d6 100755 (executable)
--- 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;
        );
 }