Query error in Acct table for Tenant_ID

[Emrah Yıldırım]

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