Query error in Acct table for Tenant_ID

Wed Mar 21 16:14:12 CET 2018

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 at 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
>