Problem with dailycounter
Hello all, Can't understand why my dailycounter is always empty ? See message below "dailycounter: No integer found in result string """ The radacct table in MySQL is correctly updated. My freeradius is : radiusd: FreeRADIUS Version 3.0.11, for host armv7l-unknown-linux-gnu, built on Jul 15 2016 at 22:02:47 What additional information should I give here ? Here's an extract of freeradius debug : (5) dailycounter: EXPAND %{User-Name} (5) dailycounter: --> test01 (5) dailycounter: SQL-User-Name set to 'test01' rlm_sql (sql): Reserved connection (11) (5) dailycounter: Executing select query: SELECT SUM(acctsessiontime - GREATEST((1471730400 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = 'test01' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1471730400' (5) dailycounter: NULL value in first column of result rlm_sql (sql): Released connection (11) (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 (5) dailycounter: Allowing user, &control:Max-Daily-Session value (3600) is greater than counter value (0) (5) dailycounter: Setting &reply:Session-Timeout value to 3600 (5) [dailycounter] = ok (5) [expiration] = noop Thanks much for your help ;) Best, Fred
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
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
Hello all, As said, both NAS and FreeRADIUS machines were on time - not in the same timezone as NAS was using UTC, but still, on time. I've set both machines to the CET timezone, to no effect. A reboot of both NAS and FreeRADIUS machines solved the problem, and AcctStartTime is now accurate, hence solving the problem with dailycounter. Now, let's monitor this over time. Best, Fred On Sun, Aug 21, 2016 at 09:38:24AM +0200, Frederic Fichter wrote:
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
On 21 Aug 2016, at 11:20, Frederic Fichter <ffichter@mac.com> wrote:
Hello all,
As said, both NAS and FreeRADIUS machines were on time - not in the same timezone as NAS was using UTC, but still, on time.
I've set both machines to the CET timezone, to no effect. A reboot of both NAS and FreeRADIUS machines solved the problem, and AcctStartTime is now accurate, hence solving the problem with dailycounter. Now, let's monitor this over time.
Weird. Event-Timestamp is seconds since the epoch, so is UTC. In my experience it’s easiest to assume the database timestamps are UTC and apply local conversions on the results are required. Restarting FreeRADIUS shouldn’t do anything, restarting the NAS might… -Arran Arran Cudbard-Bell <a.cudbardb@networkradius.com> Head of field engineering EMEA and APAC DDI: +1 (613) 518 1958 - Sales: +1 (613) 454 5037 FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
participants (2)
-
Arran Cudbard-Bell -
Frederic Fichter