freeradius+mysql not recording usage properly
-
I am using freeRADIUS package with pfSense 2.6.0. Captive-portal is configured to use freeRADIUS with MAC authentication. I have installed mariadb-server on a local linux server and configured freeRADIUS at, Package / FreeRADIUS: SQL / SQL, to use that server. Tables were created using,
/usr/local/etc/raddb/mods-config/sql/main/mysql/schema.sql
Here are the results of 3 SQL queries made to the server with,
SELECT radacctid,acctsessionid,acctuniqueid,username,acctstarttime,acctupdatetime,acctstoptime,acctsessiontime,acctinputoctets,acctoutputoctets FROM radacct;
and put into long form
query name value
<chr> <chr> <chr>
1 1 radacctid 1
2 1 acctsessionid 10d49d5fd3d0317f
3 1 acctuniqueid 4c2c62206e0f1fe05acd5c6d737ff8cb
4 1 username ec:92:de:1b:16:4d
5 1 acctstarttime 2023-03-30 11:17:16
6 1 acctupdatetime 2023-03-30 11:17:16
7 1 acctstoptime NULL
8 1 acctsessiontime 0
9 1 acctinputoctets 0
10 1 acctoutputoctets 0
11 2 radacctid 1
12 2 acctsessionid 10d49d5fd3d0317f
13 2 acctuniqueid 4c2c62206e0f1fe05acd5c6d737ff8cb
14 2 username ec:92:de:1b:16:4d
15 2 acctstarttime 2023-03-30 11:17:45
16 2 acctupdatetime 2023-03-30 11:17:45
17 2 acctstoptime 2023-03-30 11:17:44
18 2 acctsessiontime 60
19 2 acctinputoctets 23963
20 2 acctoutputoctets 44194
21 3 radacctid 1
22 3 acctsessionid 10d49d5fd3d0317f
23 3 acctuniqueid 4c2c62206e0f1fe05acd5c6d737ff8cb
24 3 username ec:92:de:1b:16:4d
25 3 acctstarttime 2023-03-30 11:33:13
26 3 acctupdatetime 2023-03-30 11:33:13
27 3 acctstoptime 2023-03-30 11:33:12
28 3 acctsessiontime 60
29 3 acctinputoctets 0
30 3 acctoutputoctets 0Note that each query returns just one record, but the values of acctstarttime,acctstoptime,acctinputoctets,acctoutputoctets have changed between queries. Why doesn't freeRADIUS append new records to the database for subsequent time intervals?
My goal is to use the script, /usr/local/etc/raddb/mods-config/sql/main/mysql/process-radacct.sql, to summarize captive-portal use per client.
-
I may have resolved this. I had the captive-portal zone configured as
I changed Send accounting updates
Now, when I submit the SQL query,
SELECT radacctid,username,acctstarttime,acctupdatetime,acctstoptime,acctinterval,acctinputoctets,acctoutputoctets FROM radacct
multiple times, I see ( in long format again )
query name value
<int> <chr> <chr>
1 1 radacctid 1
2 1 username ec:92:de:1b:16:4d
3 1 acctstarttime 2023-03-31 15:07:43
4 1 acctupdatetime 2023-03-31 15:10:55
5 1 acctstoptime NULL
6 1 acctinterval 60
7 1 acctinputoctets 257562
8 1 acctoutputoctets 10413921
9 2 radacctid 1
10 2 username ec:92:de:1b:16:4d
11 2 acctstarttime 2023-03-31 15:07:43
12 2 acctupdatetime 2023-03-31 15:11:56
13 2 acctstoptime NULL
14 2 acctinterval 61
15 2 acctinputoctets 283941
16 2 acctoutputoctets 11085058
17 3 radacctid 1
18 3 username ec:92:de:1b:16:4d
19 3 acctstarttime 2023-03-31 15:07:43
20 3 acctupdatetime 2023-03-31 15:12:57
21 3 acctstoptime NULL
22 3 acctinterval 61
23 3 acctinputoctets 530469
24 3 acctoutputoctets 20836789
25 4 radacctid 1
26 4 username ec:92:de:1b:16:4d
27 4 acctstarttime 2023-03-31 15:07:43
28 4 acctupdatetime 2023-03-31 15:24:06
29 4 acctstoptime NULL
30 4 acctinterval 60
31 4 acctinputoctets 530469
32 4 acctoutputoctets 20838821The accountupdatetime value increases across the 4 queries and the acctstoptime is NULL ( the session is still active ). The values of acctinputoctets and acctoutputoctets are ( monotonically ) increasing.