First of all, thank you so much for helping me. I made the changes below. But there are still syntax errors... Can you tell me where I was wrong? Regards column_list = "\ tenant_id, acctsessionid, acctuniqueid, username, \ realm, nasipaddress, nasportid, \ nasporttype, acctstarttime, acctupdatetime, \ acctstoptime, acctsessiontime, acctauthentic, \ connectinfo_start, connectinfo_stop, acctinputoctets, \ acctoutputoctets, calledstationid, callingstationid, \ acctterminatecause, servicetype, framedprotocol, \ framedipaddress" tenant_list = %{sql:"SELECT tenant_id FROM ${client_table} WHERE realipaddr = '%{NAS-IP-Address}"} type { accounting-on { # # Bulk terminate all sessions associated with a given NAS # query = "\ UPDATE ${....acct_table1} \ SET \ acctstoptime = FROM_UNIXTIME(\ %{integer:Event-Timestamp}), \ acctsessiontime = '%{integer:Event-Timestamp}' \ - UNIX_TIMESTAMP(acctstarttime), \ acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \ WHERE acctstoptime IS NULL \ AND nasipaddress = '%{NAS-IP-Address}' \ AND acctstarttime <= FROM_UNIXTIME(\ %{integer:Event-Timestamp})" } accounting-off { query = "${..accounting-on.query}" } start { # # Insert a new record into the sessions table # query = "\ INSERT INTO ${....acct_table1} \ (${...column_list}) \ VALUES \ ('${...tenant_list}', \ '%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{NAS-IP-Address}', \ '%{NAS-Port}', \ '%{NAS-Port-Type}', \ FROM_UNIXTIME(%{integer:Event-Timestamp}), \ FROM_UNIXTIME(%{integer:Event-Timestamp}), \ NULL, \ '0', \ '%{Acct-Authentic}', \ '%{Connect-Info}', \ '', \ '0', \ '0', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}')" ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- rlm_sql (sql): Reserved connection (4) rlm_sql (sql): Executing query: '"SELECT', '574fa1130aeb215c', 'f3c147089a008828399934c56d4cf28f', 'emrah', '', '192.168.22.188', '2012', 'Ethernet', FROM_UNIXTIME(1521616024), FROM_UNIXTIME(1521616024), NULL, '0', 'RADIUS', '', '', '0', '0', '192.168.22.188', '08-00-27-78-09-cd', '', '', '', '192.168.7.237')' rlm_sql_mysql: MYSQL check_error: 1064 received rlm_sql (sql): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"SELECT', '574fa1130aeb215c', 'f3c147089a008828399934c56d4cf28f', 'emrah', '', '' at line 1 (12) ERROR: sql : SQL query failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"SELECT', '574fa1130aeb215c', 'f3c147089a008828399934c56d4cf28f', 'emrah', '', '' at line 1 rlm_sql (sql): Released connection (4) rlm_sql (sql): Closing connection (5): Hit idle_timeout, was idle for 61 seconds rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Closing connection (3): Hit idle_timeout, was idle for 61 seconds rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket (12) sql : EXPAND INSERT INTO rad_accts (tenant_id, acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('%{sql:"SELECT', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', FROM_UNIXTIME(%{integer:Event-Timestamp}), FROM_UNIXTIME(%{integer:Event-Timestamp}), NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}') (12) sql : --> INSERT INTO rad_accts (tenant_id, acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES (' rlm_sql (sql): Executing query: 'INSERT INTO rad_accts (tenant_id, acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('' rlm_sql_mysql: MYSQL check_error: 1064 received rlm_sql (sql): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''' at line 1 rlm_sql_mysql: MYSQL check_error: 1064 received rlm_sql_mysql: Cannot store result rlm_sql_mysql: MySQL error 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''' at line 1' (12) sql : Trying next query... (12) sql : EXPAND UPDATE rad_accts SET acctstarttime = FROM_UNIXTIME(%{integer:Event-Timestamp}), acctupdatetime = FROM_UNIXTIME(%{integer:Event-Timestamp}), connectinfo_start = '%{Connect-Info}' WHERE acctsessionid = '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}' AND nasipaddress = '%{NAS-IP-Address}' (12) sql : --> UPDATE rad_accts SET acctstarttime = FROM_UNIXTIME(1521616024), acctupdatetime = FROM_UNIXTIME(1521616024), connectinfo_start = '' WHERE acctsessionid = '574fa1130aeb215c' AND username = 'emrah' AND nasipaddress = '192.168.22.188' rlm_sql (sql): Executing query: 'UPDATE rad_accts SET acctstarttime = FROM_UNIXTIME(1521616024), acctupdatetime = FROM_UNIXTIME(1521616024), connectinfo_start = '' WHERE acctsessionid = '574fa1130aeb215c' AND username = 'emrah' AND nasipaddress = '192.168.22.188'' rlm_sql_mysql: Rows matched: 0 Changed: 0 Warnings: 0 (12) sql : No records updated (12) sql : No additional queries configured rlm_sql (sql): Released connection (6) rlm_sql (sql): Opening additional connection (7) rlm_sql_mysql: Starting connect to MySQL server (12) [sql] = noop 2018-03-20 23:09 GMT+03:00 Alan Buxey <alan.buxey@gmail.com>:
okay..so you are doing this
(65) sql : EXPAND INSERT INTO rad_accts (tenant_id, acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES (SELECT nas.tenant_id FROM nas WHERE nas.realipaddr = '%{NAS-IP-Address}', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', FROM_UNIXTIME(%{integer:Event-Timestamp}), FROM_UNIXTIME(%{integer:Event-Timestamp}), NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}')
you cant do that - that is not a valid SQL INSERT command. you will need to either have a stored procedure in the SQL server for that first value.. OR, before you do the sql stuff you will need to assign a value to a local variable that you have defined in the dictionary file eg
this_example = %{sql:"SELECT nas.tenant_id FROM nas WHERE nas.realipaddr = '%{NAS-IP-Address}"}
and then use the this_example variable in your SQL INSERT statement
obviously, read the unlang man page to see how to deal with such variables
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html