Problem with dailycounter

[Frederic Fichter]

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 at 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