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
On Mar 20, 2018, at 3:55 PM, Emrah Yıldırım <emrah.yldrm81@gmail.com> wrote:
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.
You need to read the MySQL documentation to see how to create MySQL queries. Alan DeKok.
You misunderstand me... I'm not the kind of guy who tries to get ready information without doing research. You do the same thing to me every time, I have a grudge... Tell me my brother if I write... I don't know what happened to the bandwagon as we share information. What if you could help us in this life for two minutes? 20 Mar 2018 Sal 20:16 tarihinde Alan DeKok <aland@deployingradius.com> şunu yazdı:
On Mar 20, 2018, at 3:55 PM, Emrah Yıldırım <emrah.yldrm81@gmail.com> wrote:
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.
You need to read the MySQL documentation to see how to create MySQL queries.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
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
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
On Mar 21, 2018, at 7:14 AM, Emrah Yıldırım <emrah.yldrm81@gmail.com> wrote:
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?
Your MySQL syntax is still wrong. Fix it. You can't just randomly edit configuration files and expect things to work.
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
Read the MySQL documentation to see how to create MySQL queries. This is NOT a FreeRADIUS question. Alan DeKok.
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
hi, The Freeradius service does not work when I add the following. sites-enabled/default -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- accounting { # # See "Accounting queries" in sql.conf update request { tenant_list = %{sql:"SELECT tenant_id FROM ${client_table} WHERE realipaddr = '%{NAS-IP-Address}'"} } sql # ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Freeradius -X server default { # from file /etc/freeradius/sites-enabled/default # Creating Auth-Type = digest # Loading authenticate {...} # Loading authorize {...} # Loading preacct {...} # Loading accounting {...} Unknown attribute "tenant_list" /etc/freeradius/sites-enabled/default[573]: Syntax error in attribute definition /etc/freeradius/sites-enabled/default[538]: Errors parsing accounting section. 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
hi,
accounting {
# # See "Accounting queries" in sql.conf
update request { tenant_list = %{sql:"SELECT tenant_id FROM ${client_table} WHERE realipaddr = '%{NAS-IP-Address}'"} }
okay.....
Unknown attribute "tenant_list"
so did you add that value to the dictionary? no, you didnt. you then went on to add something completely different and irrelevant "Tenant-Id-List" - seen in your next email. worse, you then went and did something random, sticking stuff back into queries.conf ...and you've got syntax errors in your insert statement - "VALUES (%{Tenant-Id-List}', %{Acct-Session-Id}', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id...." - you're missing a load of apostrophes for those initial elements. basic SQL 101. alan
Tenant_list the "tenant_list " Freeradius service does not work if you do not capitalize the initials. I did great with your knowledge. Currently the service worked 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. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ PhpMyAdmin Tenant_List Acct-Session-Id Acct-Unique-Session-Id {SQL-User-Nam 2509 0 1a4670265078e308 d95e03d919d03623407adbe4ebe55239 emrah 192.168.22.188 2012 Ethernet 2018-03-22 08:35:31 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 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}', \ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- #ATTRIBUTE My-Local-String 3000 string #ATTRIBUTE My-Local-IPAddr 3001 ipaddr #ATTRIBUTE My-Local-Integer 3002 integer ATTRIBUTE Tenant_List 3000 string ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- # Log traffic to an SQL database. # # See "Accounting queries" in sql.conf update request { Tenant_List = %{sql:"SELECT tenant_id FROM nas WHERE realipaddr = '%{NAS-IP-Address}'"} } sql -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2018-03-21 21:16 GMT+03:00 Alan Buxey <alan.buxey@gmail.com>:
hi,
accounting {
# # See "Accounting queries" in sql.conf
update request { tenant_list = %{sql:"SELECT tenant_id FROM ${client_table} WHERE realipaddr = '%{NAS-IP-Address}'"} }
okay.....
Unknown attribute "tenant_list"
so did you add that value to the dictionary?
no, you didnt.
you then went on to add something completely different and irrelevant "Tenant-Id-List" - seen in your next email.
worse, you then went and did something random, sticking stuff back into queries.conf
...and you've got syntax errors in your insert statement - "VALUES (%{Tenant-Id-List}', %{Acct-Session-Id}', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id...." - you're missing a load of apostrophes for those initial elements. basic SQL 101.
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
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
On Mar 20, 2018, at 6:13 PM, Emrah Yıldırım <emrah.yldrm81@gmail.com> wrote:
You misunderstand me... I'm not the kind of guy who tries to get ready information without doing research.
Apparently you are. Because you didn't create a syntactically correct MySQL query.
You do the same thing to me every time, I have a grudge... Tell me my brother if I write...
You shouldn't have a grudge. You should *clue in*. And learn how to ask good questions.
I don't know what happened to the bandwagon as we share information.
What if you could help us in this life for two minutes?
I've been doing this for twenty years. The only people who complain about not being helped are the people who ignore the help that they're given. Stop complaining and start following instructions. This is your only warning. If you keep complaining about how mean we are for helping you, you will be unsubscribed from the list and permanently banned. Alan DeKok.
Apparently you are. Because you didn't create a syntactically correct MySQL query.
You may be right about what you say, but I disagree. I researched it, and I did my best. There's a syntax in the middle, and I've asked for help to rectify my mistake here. I don't have a lot of information about this. I managed to investigate a number of queries. as follows authorize_check_query = "\ SELECT ${authcheck_table}.id, ${authcheck_table}.username, ${authcheck_table}.attribu, ${authcheck_table}.value, ${authcheck_table}.op, ${authcheck_table}.tenant_id, ${client_table}.realipaddr \ FROM ${authcheck_table} INNER JOIN ${client_table} \ ON username = '%{SQL-User-Name}' \ AND ${client_table}.realipaddr = '%{NAS-IP-Address}' \ ORDER BY id"
I've been doing this for twenty years. The only people who complain about not being helped are the people who ignore the help that they're given.
If you've been doing this for 20 years, it's weird that you said I'd stop the slightest complaint. I didn't disrespect anyone. I won't extend the polemic anymore. Excuse me, I'm sorry. 2018-03-21 10:42 GMT+03:00 Alan DeKok <aland@deployingradius.com>:
On Mar 20, 2018, at 6:13 PM, Emrah Yıldırım <emrah.yldrm81@gmail.com> wrote:
You misunderstand me... I'm not the kind of guy who tries to get ready information without doing research.
Apparently you are. Because you didn't create a syntactically correct MySQL query.
You do the same thing to me every time, I have a grudge... Tell me my brother if I write...
You shouldn't have a grudge. You should *clue in*. And learn how to ask good questions.
I don't know what happened to the bandwagon as we share information.
What if you could help us in this life for two minutes?
I've been doing this for twenty years. The only people who complain about not being helped are the people who ignore the help that they're given.
Stop complaining and start following instructions. This is your only warning. If you keep complaining about how mean we are for helping you, you will be unsubscribed from the list and permanently banned.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
On Mar 21, 2018, at 7:56 AM, Emrah Yıldırım <emrah.yldrm81@gmail.com> wrote:
Apparently you are. Because you didn't create a syntactically correct MySQL query.
You may be right about what you say, but I disagree.
The MySQL error doesn't lie.
I researched it, and I did my best. There's a syntax in the middle, and I've asked for help to rectify my mistake here.
This is the FreeRADIUS list. Not the MySQL list. If you want to know how to write correct MySQL statements, read the MySQL documentation and ask the MySQL people. It's that simple.
I've been doing this for twenty years. The only people who complain about not being helped are the people who ignore the help that they're given.
If you've been doing this for 20 years, it's weird that you said I'd stop the slightest complaint. I didn't disrespect anyone.
After 20 years, I have *very* little patience for people who don't follow instructions, and then argue about it, and then complain about it.
I won't extend the polemic anymore. Excuse me, I'm sorry.
Thanks. Alan DeKok.
participants (3)
-
Alan Buxey -
Alan DeKok -
Emrah Yıldırım