New custom query - MySQL

12 Jul 2025

      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