I added the attribute as follows. The tenant_id number of data coming from the captive portal to the Radacct table is ' 0 '. Actually, it was worth 1. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- dictionary #ATTRIBUTE My-Local-String 3000 string #ATTRIBUTE My-Local-IPAddr 3001 ipaddr #ATTRIBUTE My-Local-Integer 3002 integer ATTRIBUTE Tenant-Id-List 3002 integer ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ queries.conf accounting { reference = "%{tolower:type.%{Acct-Status-Type}.query}" # Write SQL queries to a logfile. This is potentially useful for bulk inserts # when used with the rlm_sql_null driver. # logfile = ${logdir}/accounting.sql 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-Id-List = "\ SELECT tenant_id \ FROM nas \ WHERE realipaddr = '%{NAS-IP-Address}' \ ORDER BY id" 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-Id-List}', \ %{Acct-Session-Id}', \ '%{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}')" ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ FREERADIUS -X (2) accounting { (2) detail : EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d (2) detail : --> /var/log/freeradius/radacct/192.168.7.1/detail-20180321 (2) detail : /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/freeradius/radacct/192.168.7.1/detail-20180321 (2) detail : EXPAND %t (2) detail : --> Wed Mar 21 16:42:25 2018 (2) [detail] = ok (2) [unix] = ok (2) sql : EXPAND %{tolower:type.%{Acct-Status-Type}.query} (2) sql : --> type.start.query (2) sql : Using query template 'query' rlm_sql (sql): Reserved connection (4) (2) sql : EXPAND %{User-Name} (2) sql : --> emrah (2) sql : SQL-User-Name set to 'emrah' (2) 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 (%{Tenant-Id-List}', %{Acct-Session-Id}', '%{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}') (2) 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 (', a633e37f24251e01', 'a633e37f24251e01', '23a6ca7ba832ee0538e0a6d7dc7ca867', 'emrah', '', '192.168.22.188', '2012', 'Ethernet', FROM_UNIXTIME(1521643345), FROM_UNIXTIME(1521643345), NULL, '0', 'RADIUS', '', '', '0', '0', '192.168.22.188', '08-00-27-78-09-cd', '', '', '', '192.168.7.237') 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 (', a633e37f24251e01', 'a633e37f24251e01', '23a6ca7ba832ee0538e0a6d7dc7ca867', 'emrah', '', '192.168.22.188', '2012', 'Ethernet', FROM_UNIXTIME(1521643345), FROM_UNIXTIME(1521643345), NULL, '0', 'RADIUS', '', '', '0', '0', '192.168.22.188', '08-00-27-78-09-cd', '', '', '', '192.168.7.237')' rlm_sql (sql): Released connection (4) (2) [sql] = ok (2) [exec] = noop (2) attr_filter.accounting_response : EXPAND %{User-Name} (2) attr_filter.accounting_response : --> emrah (2) attr_filter.accounting_response : Matched entry DEFAULT at line 12 (2) [attr_filter.accounting_response] = updated (2) } # accounting = updated Sending Accounting-Response Id 89 from 192.168.7.237:1813 to 192.168.7.1:2689 (2) Finished request Waking up in 0.2 seconds. (2) Cleaning up request packet ID 89 with timestamp +23 Waking up in 3.5 seconds. (0) Cleaning up request packet ID 221 with timestamp +22 Waking up in 1.1 seconds. (1) Cleaning up request packet ID 80 with timestamp +23 2018-03-21 15:24 GMT+03:00 Alan Buxey <alan.buxey@gmail.com>:
hi,
stop rewind.
the setting of the variable would go into the main virtual_server file - not in the SQL config - as i said, read the unlang manual and sql module guide to see how these things work.
to the SQL query in the eg accounting section BEFORE the call to sql in the accounting section eg
accounting {
.. .. ..
update request { tenant_list = %{sql:"SELECT tenant_id FROM ${client_table} WHERE realipaddr = '%{NAS-IP-Address}'"} } .... sql ... }
then use that variable in your UPDATE command in the SQL module
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html