Hello, I am trying to run a custom query in order to generate reports for the company. Here's the idea: Every time there is an authentication attempt, I would like to save the record in a separate table. ####################################################################### # Authentication Logging Queries ####################################################################### # postauth_query - Insert some info after authentication ####################################################################### post-auth { # Write SQL queries to a logfile. This is potentially useful for bulk inserts # when used with the rlm_sql_null driver. # logfile = ${logdir}/post-auth.sql query = "\ INSERT INTO ${..postauth_table} \ (username, pass, reply, authdate ${..class.column_name}) \ VALUES ( \ '%{SQL-User-Name}', \ LEFT('%{%{User-Password}:-%{Chap-Password}}', 64), \ '%{reply:Packet-Type}', \ '%S.%M' \ ${..class.reply_xlat})" ## ADD query = "\ INSERT INTO radcircuitid \ (username, circuitid) \ VALUES ( \ '%{SQL-User-Name}', \ '%{string:ADSL-Agent-Circuit-Id}') \ ON DUPLICATE KEY UPDATE \ circuitid = '%{string:ADSL-Agent-Circuit-Id}'" } I know that my current solution doesn't work, so I’m reaching out to ask for guidance on the best approach to execute this custom query. Best Resgard, Rodrigo Prazim
On Jul 12, 2025, at 2:22 AM, Rodrigo Prazim <rodrigoprazim@hotmail.com> wrote:
I am trying to run a custom query in order to generate reports for the company. Here's the idea: Every time there is an authentication attempt, I would like to save the record in a separate table.
Just run the query in place, in sites-enabled/default: post-auth { ... %{sql:"INSERT INTO ..."} } Alan DeKok.
Hi, Alan. I'm using the radclient for test: echo "User-Name='teste@allohaligue',User-Password=testeligue,ADSL-Agent-Circuit-Id=HelloWorld" | radclient localhost:1812 auth testing123 And my config "sites-enabled/default": post-auth { ... sql %{sql:"INSERT INTO radcircuitid (username, circuitid) VALUES ('%{SQL-User-Name}', '%{string:ADSL-Agent-Circuit-Id}') ON DUPLICATE KEY UPDATE circuitid = '%{string:ADSL-Agent-Circuit-Id}'"} ... } But nothing arrives in the database: 2025-07-12T13:33:13.964462Z 49120 Query SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'teste@allohaligue' ORDER BY id 2025-07-12T13:33:13.964824Z 49120 Query SELECT id, username, attribute, value, op FROM radreply WHERE username = 'teste@allohaligue' ORDER BY id 2025-07-12T13:33:13.965017Z 49120 Query SELECT groupname FROM radusergroup WHERE username = 'teste@allohaligue' ORDER BY priority 2025-07-12T13:33:13.965180Z 49120 Query SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'TESTE789' ORDER BY id 2025-07-12T13:33:13.965323Z 49120 Query SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'TESTE789' ORDER BY id 2025-07-12T13:33:13.965609Z 49121 Query INSERT INTO radpostauth (username, pass, reply, authdate ) VALUES ( 'teste@allohaligue', LEFT('testeligue', 64), 'Access-Accept', '2025-07-12 10:33:13.964051' ) FreeRadius v3.2.7 Best Resgard, Rodrigo Prazim ________________________________ De: Freeradius-Users <freeradius-users-bounces+rodrigoprazim=hotmail.com@lists.freeradius.org> em nome de Alan DeKok via Freeradius-Users <freeradius-users@lists.freeradius.org> Enviado: sábado, 12 de julho de 2025 07:01 Para: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Cc: Alan DeKok <aland@deployingradius.com> Assunto: Re: New custom query - MySQL On Jul 12, 2025, at 2:22 AM, Rodrigo Prazim <rodrigoprazim@hotmail.com> wrote:
I am trying to run a custom query in order to generate reports for the company. Here's the idea: Every time there is an authentication attempt, I would like to save the record in a separate table.
Just run the query in place, in sites-enabled/default: post-auth { ... %{sql:"INSERT INTO ..."} } Alan DeKok. - List info/subscribe/unsubscribe? See https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freeradius.org%2Flist%2Fusers.html&data=05%7C02%7C%7Cd4dd8fdef4994e0bdf5d08ddc12b40fa%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638879113714628530%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=M4r0V%2FPCcT%2FaYIlo8n4yGqQmnypKa7q10AlOJis7Au8%3D&reserved=0<http://www.freeradius.org/list/users.html>
On 12/07/2025 14:41, Rodrigo Prazim wrote:
I'm using the radclient for test: echo "User-Name='teste@allohaligue',User-Password=testeligue,ADSL-Agent-Circuit-Id=HelloWorld" | radclient localhost:1812 auth testing123
But nothing arrives in the database:
I guess nothing arrives in the FreeRADIUS debug output, either. -- Matthew
Hi, Matthew. Correction: In radiusd -X The console displayed the query error: (0) Executing select query: "INSERT INTO radius.radcircuitid (username, circuitid) VALUES ('teste@allohaligue', 'HelloWorld') ON DUPLICATE KEY UPDATE circuitid = 'HelloWorld'" (0) ERROR: rlm_sql_mysql: ERROR 1064 (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"INSERT INTO radius.radcircuitid (username, circuitid) VALUES ('teste@allohaligu' at line 1): 42000 (0) ERROR: SQL query failed: server error Then I made the correction to: "%{sql:INSERT INTO...}" And no erro appeared: (0) Executing query: INSERT INTO radcircuitid (username, circuitid) VALUES ('teste@allohaligue', 'OlaMundo112') ON DUPLICATE KEY UPDATE circuitid = 'HelloWorld' rlm_sql (sql): Released connection (6) Even though it worked, I would like to know if it is the best way out. ... Rodrigo Prazim ________________________________ De: Freeradius-Users <freeradius-users-bounces+rodrigoprazim=hotmail.com@lists.freeradius.org> em nome de Matthew Newton via Freeradius-Users <freeradius-users@lists.freeradius.org> Enviadas: Sábado, 12 de Julho de 2025 10:43 Para: freeradius-users@lists.freeradius.org <freeradius-users@lists.freeradius.org> Cc: Matthew Newton <mcn@freeradius.org> Assunto: Re: New custom query - MySQL On 12/07/2025 14:41, Rodrigo Prazim wrote:
I'm using the radclient for test: echo "User-Name='teste@allohaligue',User-Password=testeligue,ADSL-Agent-Circuit-Id=HelloWorld" | radclient localhost:1812 auth testing123
But nothing arrives in the database:
I guess nothing arrives in the FreeRADIUS debug output, either. -- Matthew - List info/subscribe/unsubscribe? See https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freeradius.org%2Flist%2Fusers.html&data=05%7C02%7C%7C7efbe347a26c484954b308ddc14a2f6a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638879246557523792%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=eIXX8dxg83zZKjwlBATgzJs4EhMnUgvsbndxQeDi2ac%3D&reserved=0<http://www.freeradius.org/list/users.html>
It may be worth going over the relevant mysql documentation again. https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html Have fun On 2025/07/12 15:58, Rodrigo Prazim wrote:
Hi, Matthew.
Correction:
In radiusd -X
The console displayed the query error:
(0) Executing select query: "INSERT INTO radius.radcircuitid (username, circuitid) VALUES ('teste@allohaligue', 'HelloWorld') ON DUPLICATE KEY UPDATE circuitid = 'HelloWorld'" (0) ERROR: rlm_sql_mysql: ERROR 1064 (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"INSERT INTO radius.radcircuitid (username, circuitid) VALUES ('teste@allohaligu' at line 1): 42000 (0) ERROR: SQL query failed: server error
Then I made the correction to:
"%{sql:INSERT INTO...}"
And no erro appeared:
(0) Executing query: INSERT INTO radcircuitid (username, circuitid) VALUES ('teste@allohaligue', 'OlaMundo112') ON DUPLICATE KEY UPDATE circuitid = 'HelloWorld' rlm_sql (sql): Released connection (6)
Even though it worked, I would like to know if it is the best way out.
... Rodrigo Prazim
________________________________ De: Freeradius-Users<freeradius-users-bounces+rodrigoprazim=hotmail.com@lists.freeradius.org> em nome de Matthew Newton via Freeradius-Users<freeradius-users@lists.freeradius.org> Enviadas: Sábado, 12 de Julho de 2025 10:43 Para:freeradius-users@lists.freeradius.org <freeradius-users@lists.freeradius.org> Cc: Matthew Newton<mcn@freeradius.org> Assunto: Re: New custom query - MySQL
On 12/07/2025 14:41, Rodrigo Prazim wrote:
I'm using the radclient for test: echo "User-Name='teste@allohaligue',User-Password=testeligue,ADSL-Agent-Circuit-Id=HelloWorld" | radclient localhost:1812 auth testing123 But nothing arrives in the database: I guess nothing arrives in the FreeRADIUS debug output, either.
-- Matthew
- List info/subscribe/unsubscribe? Seehttps://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freeradius.org%2Flist%2Fusers.html&data=05%7C02%7C%7C7efbe347a26c484954b308ddc14a2f6a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638879246557523792%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=eIXX8dxg83zZKjwlBATgzJs4EhMnUgvsbndxQeDi2ac%3D&reserved=0<http://www.freeradius.org/list/users.html> - List info/subscribe/unsubscribe? Seehttp://www.freeradius.org/list/users.html
participants (4)
-
Alan DeKok -
Conrad Classen -
Matthew Newton -
Rodrigo Prazim