Thank you for your message ! Result for this query is NULL. AcctStartTime for my recent test sessions (yesterday evening and this morning) is off byalmost a week... while both my NAS and my FreeRADIUS machines are on time. Any idea where I should look ? Thanks again and best regards, Fred MariaDB [radius]> SELECT SUM(acctsessiontime - GREATEST((1471762751 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = 'test01' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >'1471762751'; +----------------------------------------------------------------------------------+ | SUM(acctsessiontime - GREATEST((1471762751 - UNIX_TIMESTAMP(acctstarttime)), 0)) | +----------------------------------------------------------------------------------+ | NULL | +----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [radius]> SELECT Username, FramedIPAddress, CallingStationId, AcctStartTime, UNIX_TIMESTAMP(`AcctStartTime`) as AcctStartTime_UNIX,AcctSessionTime, NASIPAddress FROM radacct; +----------+-----------------+-------------------+---------------------+--------------------+-----------------+---------------+ | Username | FramedIPAddress | CallingStationId | AcctStartTime | AcctStartTime_UNIX | AcctSessionTime | NASIPAddress | +----------+-----------------+-------------------+---------------------+--------------------+-----------------+---------------+ | test01 | 192.168.182.7 | 9C-04-EB-9C-CE-5E | 2016-08-14 12:20:19 | 1471170019 | 370 | 192.168.182.1 | | test01 | 192.168.182.7 | 9C-04-EB-9C-CE-5E | 2016-08-14 12:27:25 | 1471170445 | 168 | 192.168.182.1 | | test01 | 192.168.182.7 | 9C-04-EB-9C-CE-5E | 2016-08-14 12:30:27 | 1471170627 | 0 | 192.168.182.1 | | test01 | 192.168.182.6 | 00-08-CA-A1-C5-97 | 2016-08-14 21:27:57 | 1471202877 | 457 | 192.168.182.1 | | test01 | 192.168.182.6 | 00-08-CA-A1-C5-97 | 2016-08-14 21:35:43 | 1471203343 | 64 | 192.168.182.1 | | test01 | 192.168.182.6 | 00-08-CA-A1-C5-97 | 2016-08-14 21:37:28 | 1471203448 | 902 | 192.168.182.1 | | test01 | 192.168.182.7 | 9C-04-EB-9C-CE-5E | 2016-08-14 21:39:47 | 1471203587 | 601 | 192.168.182.1 | +----------+-----------------+-------------------+---------------------+--------------------+-----------------+---------------+ 7 rows in set (0.00 sec) On Sun, Aug 21, 2016 at 02:13:59AM +0200, Arran Cudbard-Bell wrote:
On Aug 21, 2016, at 12:18 AM, Frederic Fichter <ffichter@mac.com> wrote:
(5) dailycounter: EXPAND %{sql:SELECT SUM(acctsessiontime - GREATEST((1471730400 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1471730400'} (5) dailycounter: --> (5) dailycounter: No integer found in result string "". May be first session, setting counter to 0
Have you tried running the query manually? What do you get.
-Arran - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html