Query error in Acct table for Tenant_ID
Emrah Yıldırım
emrah.yldrm81 at gmail.com
Tue Mar 20 16:55:16 CET 2018
Hello
I'm working on a management panel for multiple use. In the query. conf
file, I made changes to the queries for the tenant_id arm. But it gives
error in the Acct table. I get a syntax error. Please help me.
I want to do it; Print the tenant_id number that I created in the NAS table
to the TENANT_ID table in the Acct table... as a condition, reellipaddr =%
{Nas-IP-Address}
Thanks
Received Accounting-Request Id 130 from 192.168.7.1:24103 to
192.168.7.237:1813 length 164
NAS-IP-Address = 192.168.22.188
NAS-Identifier = 'pfSense.localdomain'
User-Name = 'emrah'
Acct-Status-Type = Start
Acct-Authentic = RADIUS
NAS-IP-Address = 192.168.22.188
NAS-Identifier = 'pfSense.localdomain'
NAS-Port-Type = Ethernet
NAS-Port = 2012
Acct-Session-Id = '27e89601da582df5'
Framed-IP-Address = 192.168.7.237
Called-Station-Id = '192.168.22.188'
Calling-Station-Id = '08-00-27-78-09-cd'
(65) # Executing section preacct from file
/etc/freeradius/sites-enabled/default
(65) preacct {
(65) [preprocess] = ok
(65) acct_unique acct_unique {
(65) if ("%{string:Class}" =~ /ai:([0-9a-f]{32})/i)
(65) EXPAND %{string:Class}
(65) -->
(65) if ("%{string:Class}" =~ /ai:([0-9a-f]{32})/i) -> FALSE
(65) else else {
(65) update request {
(65) EXPAND
%{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}}
(65) --> 35096e6b3a3e563444ee6e26cf7ced1f
(65) Acct-Unique-Session-Id := '"35096e6b3a3e563444ee6e26cf7ced1f"'
(65) } # update request = noop
(65) } # else else = noop
(65) } # acct_unique acct_unique = noop
(65) suffix : No '@' in User-Name = "emrah", looking up realm NULL
(65) suffix : No such realm "NULL"
(65) [suffix] = noop
(65) [files] = noop
(65) } # preacct = ok
(65) # Executing section accounting from file
/etc/freeradius/sites-enabled/default
(65) accounting {
(65) detail : EXPAND
/var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d
(65) detail : --> /var/log/freeradius/radacct/192.168.7.1/detail-20180320
(65) 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-20180320
(65) detail : EXPAND %t
(65) detail : --> Tue Mar 20 17:53:35 2018
(65) [detail] = ok
(65) [unix] = ok
(65) sql : EXPAND %{tolower:type.%{Acct-Status-Type}.query}
(65) sql : --> type.start.query
(65) sql : Using query template 'query'
rlm_sql (sql): Reserved connection (42)
(65) sql : EXPAND %{User-Name}
(65) sql : --> emrah
(65) sql : SQL-User-Name set to 'emrah'
(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}')
(65) 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 (SELECT nas.tenant_id FROM nas WHERE nas.realipaddr
= '192.168.22.188', '27e89601da582df5', '35096e6b3a3e563444ee6e26cf7ced1f',
'emrah', '', '192.168.22.188', '2012', 'Ethernet',
FROM_UNIXTIME(1521561215), FROM_UNIXTIME(1521561215), 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 (SELECT nas.tenant_id FROM nas WHERE nas.realipaddr
= '192.168.22.188', '27e89601da582df5', '35096e6b3a3e563444ee6e26cf7ced1f',
'emrah', '', '192.168.22.188', '2012', 'Ethernet',
FROM_UNIXTIME(1521561215), FROM_UNIXTIME(1521561215), 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 nas.tenant_id FROM nas WHERE nas.realipaddr = '192.168.22.188',
'27e89601' 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 'SELECT nas.tenant_id FROM nas WHERE nas.realipaddr =
'192.168.22.188', '27e89601' at line 1'
(65) sql : Trying next query...
(65) 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}'
(65) sql : --> UPDATE rad_accts SET acctstarttime =
FROM_UNIXTIME(1521561215), acctupdatetime = FROM_UNIXTIME(1521561215),
connectinfo_start = '' WHERE acctsessionid = '27e89601da582df5' AND username =
'emrah' AND nasipaddress = '192.168.22.188'
rlm_sql (sql): Executing query: 'UPDATE rad_accts SET acctstarttime =
FROM_UNIXTIME(1521561215), acctupdatetime = FROM_UNIXTIME(1521561215),
connectinfo_start = '' WHERE acctsessionid = '27e89601da582df5' AND username =
'emrah' AND nasipaddress = '192.168.22.188''
rlm_sql_mysql: Rows matched: 0 Changed: 0 Warnings: 0
(65) sql : No records updated
(65) sql : No additional queries configured
rlm_sql (sql): Released connection (42)
rlm_sql (sql): Opening additional connection (43)
rlm_sql_mysql: Starting connect to MySQL server
(65) [sql] = noop
(65) [exec] = noop
(65) attr_filter.accounting_response : EXPAND %{User-Name}
(65) attr_filter.accounting_response : --> emrah
(65) attr_filter.accounting_response : Matched entry DEFAULT at line 12
(65) [attr_filter.accounting_response] = updated
(65) } # accounting = updated
Sending Accounting-Response Id 130 from 192.168.7.237:1813 to
192.168.7.1:24103
(65) Finished request
Waking up in 0.3 seconds.
Received Access-Request Id 175 from 192.168.7.1:47006 to 192.168.7.237:1812
length 131
NAS-IP-Address = 192.168.22.188
NAS-Identifier = 'pfSense.localdomain'
User-Name = 'emrah'
User-Password = '123'
Service-Type = Login-User
NAS-Port-Type = Ethernet
NAS-Port = 2012
Framed-IP-Address = 192.168.7.237
Called-Station-Id = '192.168.22.188'
Calling-Station-Id = '08-00-27-78-09-cd'
(66) # Executing section authorize from file
/etc/freeradius/sites-enabled/default
(66) authorize {
(66) filter_username filter_username {
(66) if (User-Name != "%{tolower:%{User-Name}}")
(66) EXPAND %{tolower:%{User-Name}}
(66) --> emrah
(66) if (User-Name != "%{tolower:%{User-Name}}") -> FALSE
(66) if (User-Name =~ / /)
(66) if (User-Name =~ / /) -> FALSE
(66) if (User-Name =~ /@.*@/ )
(66) if (User-Name =~ /@.*@/ ) -> FALSE
(66) if (User-Name =~ /\\.\\./ )
(66) if (User-Name =~ /\\.\\./ ) -> FALSE
(66) if ((User-Name =~ /@/) && (User-Name !~ /@(.+)\\.(.+)$/))
(66) if ((User-Name =~ /@/) && (User-Name !~ /@(.+)\\.(.+)$/)) ->
FALSE
(66) if (User-Name =~ /\\.$/)
(66) if (User-Name =~ /\\.$/) -> FALSE
(66) if (User-Name =~ /@\\./)
(66) if (User-Name =~ /@\\./) -> FALSE
(66) } # filter_username filter_username = notfound
(66) [preprocess] = ok
(66) [chap] = noop
(66) [mschap] = noop
(66) [digest] = noop
(66) suffix : No '@' in User-Name = "emrah", looking up realm NULL
(66) suffix : No such realm "NULL"
(66) [suffix] = noop
(66) eap : No EAP-Message, not doing EAP
(66) [eap] = noop
(66) sql : EXPAND %{User-Name}
(66) sql : --> emrah
(66) sql : SQL-User-Name set to 'emrah'
rlm_sql (sql): Reserved connection (43)
(66) sql : EXPAND SELECT rad_checks.id, rad_checks.username,
rad_checks.attribu, rad_checks.value, rad_checks.op, rad_checks.tenant_id,
nas.realipaddr FROM rad_checks INNER JOIN nas ON username =
'%{SQL-User-Name}' AND nas.realipaddr = '%{NAS-IP-Address}' ORDER BY id
(66) sql : --> SELECT rad_checks.id, rad_checks.username,
rad_checks.attribu, rad_checks.value, rad_checks.op, rad_checks.tenant_id,
nas.realipaddr FROM rad_checks INNER JOIN nas ON username = 'emrah' AND
nas.realipaddr = '192.168.22.188' ORDER BY id
rlm_sql (sql): Executing query: 'SELECT rad_checks.id, rad_checks.username,
rad_checks.attribu, rad_checks.value, rad_checks.op, rad_checks.tenant_id,
nas.realipaddr FROM rad_checks INNER JOIN nas ON username = 'emrah' AND
nas.realipaddr = '192.168.22.188' ORDER BY id'
(66) sql : User found in radcheck table
(66) sql : Check items matched
(66) sql : EXPAND SELECT rad_replies.id, rad_replies.username,
rad_replies.attribu, rad_replies.value, rad_replies.op,
rad_replies.tenant_id, nas.realipaddr FROM rad_replies INNER JOIN nas ON
username = '%{SQL-User-Name}' AND nas.realipaddr = '%{NAS-IP-Address}'
ORDER BY id
(66) sql : --> SELECT rad_replies.id, rad_replies.username,
rad_replies.attribu, rad_replies.value, rad_replies.op,
rad_replies.tenant_id, nas.realipaddr FROM rad_replies INNER JOIN nas ON
username = 'emrah' AND nas.realipaddr = '192.168.22.188' ORDER BY id
rlm_sql (sql): Executing query: 'SELECT rad_replies.id,
rad_replies.username, rad_replies.attribu, rad_replies.value,
rad_replies.op, rad_replies.tenant_id, nas.realipaddr FROM rad_replies
INNER JOIN nas ON username = 'emrah' AND nas.realipaddr = '192.168.22.188'
ORDER BY id'
(66) sql : EXPAND SELECT rad_user_groups.groupname, nas.realipaddr FROM
rad_user_groups INNER JOIN nas ON username = '%{SQL-User-Name}' AND
nas.realipaddr = '%{NAS-IP-Address}' ORDER BY priority
(66) sql : --> SELECT rad_user_groups.groupname, nas.realipaddr FROM
rad_user_groups INNER JOIN nas ON username = 'emrah' AND nas.realipaddr =
'192.168.22.188' ORDER BY priority
rlm_sql (sql): Executing query: 'SELECT rad_user_groups.groupname,
nas.realipaddr FROM rad_user_groups INNER JOIN nas ON username = 'emrah'
AND nas.realipaddr = '192.168.22.188' ORDER BY priority'
(66) sql : User found in the group table
(66) sql : EXPAND SELECT rad_group_checks.id, rad_group_checks.groupname,
rad_group_checks.attribu, rad_group_checks.Value, rad_group_checks.op,
nas.realipaddr FROM rad_group_checks INNER JOIN nas ON groupname =
'%{Sql-Group}' AND nas.realipaddr = '%{NAS-IP-Address}' ORDER BY id
(66) sql : --> SELECT rad_group_checks.id, rad_group_checks.groupname,
rad_group_checks.attribu, rad_group_checks.Value, rad_group_checks.op,
nas.realipaddr FROM rad_group_checks INNER JOIN nas ON groupname = 'tes'
AND nas.realipaddr = '192.168.22.188' ORDER BY id
rlm_sql (sql): Executing query: 'SELECT rad_group_checks.id,
rad_group_checks.groupname, rad_group_checks.attribu,
rad_group_checks.Value, rad_group_checks.op, nas.realipaddr FROM
rad_group_checks INNER JOIN nas ON groupname = 'tes' AND nas.realipaddr =
'192.168.22.188' ORDER BY id'
(66) sql : Group "tes" check items matched
(66) sql : EXPAND SELECT rad_group_replies.id, rad_group_replies.groupname,
rad_group_replies.attribu, rad_group_replies.Value, rad_group_replies.op,
nas.realipaddr FROM rad_group_replies INNER JOIN nas ON groupname =
'%{Sql-Group}' AND nas.realipaddr = '%{NAS-IP-Address}' ORDER BY id
(66) sql : --> SELECT rad_group_replies.id, rad_group_replies.groupname,
rad_group_replies.attribu, rad_group_replies.Value, rad_group_replies.op,
nas.realipaddr FROM rad_group_replies INNER JOIN nas ON groupname = 'tes'
AND nas.realipaddr = '192.168.22.188' ORDER BY id
rlm_sql (sql): Executing query: 'SELECT rad_group_replies.id,
rad_group_replies.groupname, rad_group_replies.attribu,
rad_group_replies.Value, rad_group_replies.op, nas.realipaddr FROM
rad_group_replies INNER JOIN nas ON groupname = 'tes' AND nas.realipaddr =
'192.168.22.188' ORDER BY id'
(66) sql : Group "tes" reply items processed
rlm_sql (sql): Released connection (43)
(66) [sql] = ok
(66) [expiration] = noop
(66) [logintime] = noop
(66) WARNING: dailycounter : Couldn't find control attribute
'control:Max-Daily-Session'
(66) [dailycounter] = noop
(66) WARNING: noresetcounter : Couldn't find control attribute
'control:Max-All-Session'
(66) [noresetcounter] = noop
(66) WARNING: monthlycounter : Couldn't find control attribute
'control:Max-Monthly-Session'
(66) [monthlycounter] = noop
(66) WARNING: expire_on_login : Couldn't find control attribute
'control:Expire-After'
(66) [expire_on_login] = noop
(66) [pap] = updated
(66) } # authorize = updated
(66) Found Auth-Type = PAP
(66) # Executing group from file /etc/freeradius/sites-enabled/default
(66) Auth-Type PAP {
(66) pap : Login attempt with password
(66) pap : User authenticated successfully
(66) [pap] = ok
(66) } # Auth-Type PAP = ok
(66) # Executing section post-auth from file
/etc/freeradius/sites-enabled/default
(66) post-auth {
(66) sql : EXPAND .query
(66) sql : --> .query
(66) sql : Using query template 'query'
rlm_sql (sql): Reserved connection (43)
(66) sql : EXPAND %{User-Name}
(66) sql : --> emrah
(66) sql : SQL-User-Name set to 'emrah'
(66) sql : EXPAND INSERT INTO rad_post_auths (username, pass, reply,
authdate) VALUES ( '%{SQL-User-Name}',
'%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S')
(66) sql : --> INSERT INTO rad_post_auths (username, pass, reply,
authdate) VALUES ( 'emrah', '123', 'Access-Accept', '2018-03-20 17:53:35')
rlm_sql (sql): Executing query: 'INSERT INTO rad_post_auths (username,
pass, reply, authdate) VALUES ( 'emrah', '123', 'Access-Accept',
'2018-03-20 17:53:35')'
rlm_sql (sql): Released connection (43)
(66) [sql] = ok
(66) [exec] = noop
(66) remove_reply_message_if_eap remove_reply_message_if_eap {
(66) if (reply:EAP-Message && reply:Reply-Message)
(66) if (reply:EAP-Message && reply:Reply-Message) -> FALSE
(66) else else {
(66) [noop] = noop
(66) } # else else = noop
(66) } # remove_reply_message_if_eap remove_reply_message_if_eap = noop
(66) } # post-auth = ok
Sending Access-Accept Id 175 from 192.168.7.237:1812 to 192.168.7.1:47006
WISPr-Bandwidth-Max-Down = 10485760
(66) Finished request
Waking up in 0.2 seconds.
(65) Cleaning up request packet ID 130 with timestamp +1262
Waking up in 4.7 seconds.
(66) Cleaning up request packet ID 175 with timestamp +1262
More information about the Freeradius-Users
mailing list