Thread https://community.ipfire.org/t/openvpn-roadwarrior-connections-log/7049/16 The SQL code in ovpnclients.dat does not seem to report correctly. I enter From=25 To=25 Month=January year=2022 The report should look between 25/01/2022 00:00:00 and 25/01/2022 23:59:59 but it does not provide a report. uppercase DATETIME() seems to be custom function, not the usual datetime() function in Perl. /* https://git.ipfire.org/?p=ipfire-2.x.git;a=blob;f=html/cgi-bin/logs.cgi/ovpnclients.dat */ /* lines 146-155 */ SELECT common_name, DATETIME(connected_at , 'localtime'), DATETIME(disconnected_at, 'localtime'), bytes_received, bytes_sent, STRFTIME('%s', DATETIME(disconnected_at)) - STRFTIME('%s', DATETIME(connected_at)) AS duration FROM sessions WHERE 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; Per my example: (I select From=25 To=25, select connection name josh) disconnected at=25/1/2022 08:45 AZ time > from string=25/1/2022 00:00 AZ time connected at=25/1/2022 08:30 AZ time < to string=25/1/2022 23:59 AZ time
DATETIME() is a function provided by SQLite. What do you get and what do you expect instead?
I enter From=25 To=25 Month=January year=2022 to get a report. The code looks between 25/01/2022 00:00:00 and 25/01/2022 23:59:59 but it does not provide a report.
Could you please send a plaintext dump of your database to check if there is actually any data in it that would match?
Created attachment 973 [details] image.png I don't know how to do that. Should I attach /var/log/rrd/collectd/localhost/openvpn-josh It is 301K If I type From=25, To=26 I do get data [image: image.png] On Thu, Jan 27, 2022 at 6:27 AM IPFire Bugzilla <bugzilla@ipfire.org> wrote: > Michael Tremer <michael.tremer@ipfire.org> changed bug 12768 > <https://bugzilla.ipfire.org/show_bug.cgi?id=12768> > What Removed Added > Assignee nobody@ipfire.org michael.tremer@ipfire.org > Component openvpn --- > > *Comment # 3 <https://bugzilla.ipfire.org/show_bug.cgi?id=12768#c3> on bug > 12768 <https://bugzilla.ipfire.org/show_bug.cgi?id=12768> from Michael > Tremer <michael.tremer@ipfire.org> * > > Could you please send a plaintext dump of your database to check if there is > actually any data in it that would match? > > ------------------------------ > You are receiving this mail because: > > - You reported the bug. > >
I found /var/ipfire/ovpn/clients.db [root@ipfire ovpn]# sqlite3 clients.db SQLite version 3.36.0 2021-06-18 18:36:39 Enter ".help" for usage hints. sqlite> .output paul sqlite> .dump sqlite> .exit [root@ipfire ovpn]# cat paul PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE sessions( common_name TEXT NOT NULL, connected_at TEXT NOT NULL, disconnected_at TEXT, bytes_received INTEGER, bytes_sent INTEGER ); INSERT INTO sessions VALUES('chandler.from-az.net','2020-07-28 02:11:31','2020-07-28 02:12:43',26027,18216); INSERT INTO sessions VALUES('chandler.from-az.net','2020-07-28 02:12:43','2020-07-28 02:16:50',47591,38272); INSERT INTO sessions VALUES('chandler.from-az.net','2020-10-17 21:47:29','2020-10-17 21:51:06',673555,4087484); INSERT INTO sessions VALUES('chandler.from-az.net','2020-10-17 21:51:06','2020-10-17 21:53:41',138483,159013); INSERT INTO sessions VALUES('chandler.from-az.net','2021-03-09 17:48:19','2021-03-09 17:55:15',72034,68943); INSERT INTO sessions VALUES('chandler.from-az.net','2021-03-09 17:55:14','2021-03-09 18:01:19',76942,65731); INSERT INTO sessions VALUES('chandler.from-az.net','2021-03-09 18:01:18','2021-03-09 18:04:17',37986,29731); INSERT INTO sessions VALUES('chandler.from-az.net','2021-03-09 18:11:36','2021-03-09 18:16:56',754366,3632707); INSERT INTO sessions VALUES('chandler.from-az.net','2021-03-09 18:16:55','2021-03-09 18:19:29',10610,6727); INSERT INTO sessions VALUES('chandler.from-az.net','2021-03-20 21:06:53','2021-03-20 21:12:09',311443,2130551); INSERT INTO sessions VALUES('chandler.from-az.net','2021-03-20 21:12:08','2021-03-20 21:32:39',2302183,11488435); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-03 15:24:15','2021-11-03 15:28:38',45554,44435); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-03 15:29:11','2021-11-03 15:31:14',4350,5563); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-04 11:37:19','2021-11-04 11:37:49',11515,6823); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-04 11:37:49','2021-11-04 11:40:06',15167,15131); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-04 17:23:11','2021-11-04 17:25:13',4265,6906); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-04 17:26:05','2021-11-04 17:30:47',32387,22895); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-04 17:41:08','2021-11-04 17:44:05',40247,23799); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-05 01:18:51','2021-11-05 01:21:36',324639,23043); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-05 01:26:04','2021-11-05 01:31:05',164703,11315); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-05 01:58:15','2021-11-05 21:56:13',22845475,160200407); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-05 21:56:13','2021-11-08 17:15:38',30550005,182850711); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-08 17:16:41','2021-11-13 15:37:14',64035011,277167051); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-13 17:51:03','2021-11-13 17:54:14',37015,35227); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-13 20:26:50','2021-11-14 12:26:36',7948075,19844983); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-14 12:26:35','2021-11-16 15:35:12',211737240,346018627); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-16 15:57:36','2021-11-18 15:10:33',24103669,109425183); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-25 21:29:33','2021-11-25 21:32:06',18855,14031); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-26 15:50:48','2021-11-26 15:57:14',894355,10304647); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-29 12:36:09','2021-11-29 12:39:13',39931,36287); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-29 12:53:07','2021-11-29 12:56:31',257135,3740239); INSERT INTO sessions VALUES('chandler.from-az.net','2021-11-29 13:21:38','2021-11-29 13:24:41',17799,13759); INSERT INTO sessions VALUES('chandler.from-az.net','2021-12-15 23:23:41','2021-12-15 23:29:13',28619,21363); INSERT INTO sessions VALUES('chandler.from-az.net','2021-12-15 23:29:13','2021-12-15 23:31:56',19075,14759); INSERT INTO sessions VALUES('chandler.from-az.net','2022-01-17 14:24:59','2022-01-17 14:27:32',15543,11191); INSERT INTO sessions VALUES('josh','2022-01-17 14:25:59','2022-01-17 14:30:52',25132,11100); INSERT INTO sessions VALUES('josh','2022-01-17 14:30:51','2022-01-17 14:33:11',10868,8928); INSERT INTO sessions VALUES('chandler.from-az.net','2022-01-17 18:02:27','2022-01-17 18:14:09',4124579,41911947); INSERT INTO sessions VALUES('josh','2022-01-17 21:39:59','2022-01-17 21:47:18',1968488,8718316); INSERT INTO sessions VALUES('chandler.from-az.net','2022-01-18 19:53:20','2022-01-18 19:53:26',7397,4810); INSERT INTO sessions VALUES('chandler.from-az.net','2022-01-18 19:53:18','2022-01-18 19:55:26',4291,5602); INSERT INTO sessions VALUES('josh','2022-01-25 15:36:56','2022-01-25 15:46:55',817076,8067812); INSERT INTO sessions VALUES('josh','2022-01-25 23:31:04','2022-01-26 00:16:18',7929864,37502892); CREATE INDEX sessions_common_name ON sessions(common_name); COMMIT; [root@ipfire ovpn]# The last two INSERTS show josh entries for 25/01/2022
Michael - I see the same thing as Paul. My DB looks fine. I see it is in UTC. sqlite> select * from sessions where connected_at > DATETIME('now', '-7 day'); common_name connected_at disconnected_at bytes_received bytes_sent ----------- ------------------- ------------------- -------------- ---------- JiPhoneSE 2022-01-27 20:18:49 2022-01-27 20:20:37 671359 17084256 sqlite> But I think the Perl code is grabbing yesterday when the To date and the From date are the same date. Here is the From Date code from Perl with today's date (the 27th) added. sqlite> select DATETIME('2022-01-27', 'localtime', 'start of day', '+86399 seconds'); DATETIME('2022-01-27', 'localtime', 'start of day', '+86399 seconds') --------------------------------------------------------------------- 2022-01-26 23:59:59 sqlite>
It is not a standard thing. On my production machine and my vm testbed I am always getting the data shown on the correct date. So it is not something that is happening as standard with all systems. There must be something different between the systems or the data. I looked at my sqlite.db data and it didn't look any different to Paul's @Paul do you see the problem with both josh and chandler.from-az.net connections, or only with the josh connection?
Adolf ... the issue is for both connection names. I believe the $from_date_string and $to_datestring are already in local time so there is no need to re-local them so remove 'localtime from the right sides. My own test - I copied clients.db to my local system. - I asked for a report for the 25th, start of day MY TIME (no conversion) ``` sqlite> select * from sessions where common_name="josh" and ...> DATETIME(disconnected_at, 'localtime') > DATETIME('2022-01-25', 'start of day') AND ...> DATETIME(connected_at , 'localtime') < DATETIME('2022-01-25', 'start of day', '+86399 seconds') ...> ; josh|2022-01-25 15:36:56|2022-01-25 15:46:55|817076|8067812 josh|2022-01-25 23:31:04|2022-01-26 00:16:18|7929864|37502892 ``` this is correct.
Lines 90,91 assign the $from_datestring and $to_datestring in the user's local time. But connected_at and disconnected_at are UTC times from the db. The query in lines 114-142, whenever there is $from_datestring var or $to_datestring var, there is no need to relocalize them by adding 'localtime'.
(In reply to paul from comment #9) > Lines 90,91 assign the $from_datestring and $to_datestring in the user's > local time. But connected_at and disconnected_at are UTC times from the db. That is why the stored timestamps are being converted into local time first before they are compared to the timestamp that the user entered which is already in local time.
Created attachment 975 [details] attachment-3391146-0.html From the UI, I select From: 28 and To: 28. the $from_datestring is the user's local time (arizona), 2022-01-28 00:00:00. the $to_datestring is the user's local time (arizona), 2022-01-28 00:00:00. If you add 'datetime', it is thrown back 7 hours to 2022-01-27 17:00:00. the comparison should be: disconnected_at, 'localtime' > $from_datestring, 'start of day' without 'localtime' connected_at, 'localtime' < $to_datestring , 'start of day', '+86399 seconds' On Fri, Jan 28, 2022 at 2:06 AM IPFire Bugzilla <bugzilla@ipfire.org> wrote: > *Comment # 10 <https://bugzilla.ipfire.org/show_bug.cgi?id=12768#c10> on > bug 12768 <https://bugzilla.ipfire.org/show_bug.cgi?id=12768> from Michael > Tremer <michael.tremer@ipfire.org> * > > (In reply to paul from comment #9 <https://bugzilla.ipfire.org/show_bug.cgi?id=12768#c9>)> Lines 90,91 assign the $from_datestring and $to_datestring in the user's > > local time. But connected_at and disconnected_at are UTC times from the db. > > That is why the stored timestamps are being converted into local time first > before they are compared to the timestamp that the user entered which is > already in local time. > > ------------------------------ > You are receiving this mail because: > > - You reported the bug. > >
Paul - You've got the right idea but the wrong fix for the code. > If you add 'datetime', it is thrown back 7 hours to 2022-01-27 17:00:00. This should be: If you add 'localtime', it is thrown back 7 hours to 2022-01-27 17:00:00. Since '$to_datestring' and '$from_datestring' are both in local time, they don't need to be referred to as 'localtime' (again) in the DATETIME function. Single example: DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day')
(In reply to Adolf Belka from comment #7) > It is not a standard thing. On my production machine and my vm testbed I am > always getting the data shown on the correct date. I am guessing you see the correct date because you are ahead of UTC (utc+1). Paul and I are behind UTC (utc-7 or utc-6)
(In reply to Jon from comment #12) > Paul - You've got the right idea but the wrong fix for the code. > > > If you add 'datetime', it is thrown back 7 hours to 2022-01-27 17:00:00. > > This should be: > If you add 'localtime', it is thrown back 7 hours to 2022-01-27 17:00:00. > > Since '$to_datestring' and '$from_datestring' are both in local time, they > don't need to be referred to as 'localtime' (again) in the DATETIME function. > > Single example: > DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', > 'localtime', 'start of day') > > DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start > of day') Hello Jon, this is correct. That is where the problem is :) Those date functions were horrible to deal with in the first place, so I am not surprised that I got it wrong and I wouldn't have spotted this at all. I put this all into a patch: > https://patchwork.ipfire.org/project/ipfire/patch/20220215134027.773437-1-michael.tremer@ipfire.org/ Could you please confirm that his fixes the problem?
https://git.ipfire.org/?p=people/pmueller/ipfire-2.x.git;a=commit;h=6e40963459eca547f1857d4e518d920518ff23a5
Michael, In general everything works A-OK. Removing `localtime` corrected the errors (Lines 144-157 are easy to test) But Lines 113-142 I am not sure. I don't understand the code well enough to properly test.
I removed the “localtime” argument wherever we parse the input from the user. So I would assume that this is correct.
agree!
I applied the patch, tested, reports are correct now. Thank you all for your help. Please close the bug as completed.
Please close bug, fixed on cu 166.
Fix has been confirmed to work and has been released into CU166 so bug is being closed.