Problem expanding "%{Calling-Station-Id}"
Hello all :) I'm using FreeRADIUS Version 3.0.8in a debian 8. I want to exec a sql query to return a value in Reply-Message, but I don't know why freeradius doesn't expand Calling-Station-Id every time... I use: Reply-Message := "%{sqllocal:SELECT username FROM radpostauth WHERE client like 'PA%%' and reply='Access-Accept' and mac='%{Calling-Station-Id}' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1}" My log: Wed May 4 11:48:37 2016 : Debug: (0) Received Access-Request Id 215 from 192.168.1.15:47353 to 192.168.1.3:1812 length 80 Wed May 4 11:48:37 2016 : Debug: (0) User-Name = 'nodowifi@nodo.unex.es' Wed May 4 11:48:37 2016 : Debug: (0) User-Password = 'password' Wed May 4 11:48:37 2016 : Debug: (0) Calling-Station-Id = 'f0:f6:1c:58:da:cd' Wed May 4 11:48:37 2016 : Debug: (0) session-state: No State attribute Wed May 4 11:48:37 2016 : Debug: (0) # Executing section authorize from file /etc/freeradius/sites-enabled/captive In Hints, freeradius doesn't expand Calling-Station-Id: DEFAULT Realm == "nodo.unex.es" Reply-Message = "%{sqllocal:SELECT username FROM radpostauth WHERE client like 'PA%%' and reply='Access-Accept' and mac='%{Calling-Station-Id}' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1}", Log: Wed May 4 11:48:37 2016 : Debug: (0) preprocess: ::: Examining Reply-Message Wed May 4 11:48:37 2016 : Debug: %{sqllocal:SELECT username FROM radpostauth WHERE client like 'PA%%' and reply='Access-Accept' and mac='%{Calling-Station-Id}' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1} Wed May 4 11:48:37 2016 : Debug: Parsed xlat tree: Wed May 4 11:48:37 2016 : Debug: xlat --> sqllocal Wed May 4 11:48:37 2016 : Debug: { Wed May 4 11:48:37 2016 : Debug: literal --> SELECT username FROM radpostauth WHERE client like 'PA Wed May 4 11:48:37 2016 : Debug: literal --> % Wed May 4 11:48:37 2016 : Debug: literal --> ' and reply='Access-Accept' and mac=' Wed May 4 11:48:37 2016 : Debug: attribute --> Calling-Station-Id Wed May 4 11:48:37 2016 : Debug: literal --> ' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1 Wed May 4 11:48:37 2016 : Debug: } Wed May 4 11:48:37 2016 : Debug: %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} Wed May 4 11:48:37 2016 : Debug: Parsed xlat tree: Wed May 4 11:48:37 2016 : Debug: if { Wed May 4 11:48:37 2016 : Debug: attribute --> Stripped-User-Name Wed May 4 11:48:37 2016 : Debug: } Wed May 4 11:48:37 2016 : Debug: else { Wed May 4 11:48:37 2016 : Debug: if { Wed May 4 11:48:37 2016 : Debug: attribute --> User-Name Wed May 4 11:48:37 2016 : Debug: } Wed May 4 11:48:37 2016 : Debug: else { Wed May 4 11:48:37 2016 : Debug: literal --> DEFAULT Wed May 4 11:48:37 2016 : Debug: } Wed May 4 11:48:37 2016 : Debug: } Wed May 4 11:48:37 2016 : Debug: (0) preprocess: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} Wed May 4 11:48:37 2016 : Debug: (0) preprocess: --> nodowifi@nodo.unex.es Wed May 4 11:48:37 2016 : Debug: (0) preprocess: SQL-User-Name set to ' nodowifi@nodo.unex.es' Wed May 4 11:48:37 2016 : Debug: (0) preprocess: ::: FROM 1 TO 1 MAX 2 Wed May 4 11:48:37 2016 : Debug: (0) preprocess: ::: Examining SQL-User-Name Wed May 4 11:48:37 2016 : Debug: (0) preprocess: ::: APPENDING SQL-User-Name FROM 0 TO 1 Wed May 4 11:48:37 2016 : Debug: (0) preprocess: ::: TO in 1 out 2 Wed May 4 11:48:37 2016 : Debug: (0) preprocess: ::: to[0] = User-Name Wed May 4 11:48:37 2016 : Debug: (0) preprocess: ::: to[1] = SQL-User-Name Wed May 4 11:48:37 2016 : Debug: rlm_sql (sqllocal): Reserved connection (4) Wed May 4 11:48:37 2016 : Debug: /var/log/freeradius/sqllog.sql Wed May 4 11:48:37 2016 : Debug: Parsed xlat tree: Wed May 4 11:48:37 2016 : Debug: literal --> /var/log/freeradius/sqllog.sql Wed May 4 11:48:37 2016 : Debug: (0) preprocess: EXPAND /var/log/freeradius/sqllog.sql Wed May 4 11:48:37 2016 : Debug: (0) preprocess: --> /var/log/freeradius/sqllog.sql Wed May 4 11:48:37 2016 : Debug: (0) preprocess: Executing select query: SELECT username FROM radpostauth WHERE client like 'PA%' and reply='Access-Accept' and mac='' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1 Wed May 4 11:48:37 2016 : Debug: (0) preprocess: SQL query returned no results In users, freeradius expand Calling-Station-Id: nodowifi Cleartext-Password := "password" Reply-Message := "%{sqllocal:SELECT username FROM radpostauth WHERE client like 'PA%%' and reply='Access-Accept' and mac='%{Calling-Station-Id}' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1}" Wed May 4 11:48:37 2016 : Debug: (0) files: ::: Examining Reply-Message Wed May 4 11:48:37 2016 : Debug: %{sqllocal:SELECT username FROM radpostauth WHERE client like 'PA%%' and reply='Access-Accept' and mac='%{Calling-Station-Id}' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1} Wed May 4 11:48:37 2016 : Debug: Parsed xlat tree: Wed May 4 11:48:37 2016 : Debug: xlat --> sqllocal Wed May 4 11:48:37 2016 : Debug: { Wed May 4 11:48:37 2016 : Debug: literal --> SELECT username FROM radpostauth WHERE client like 'PA Wed May 4 11:48:37 2016 : Debug: literal --> % Wed May 4 11:48:37 2016 : Debug: literal --> ' and reply='Access-Accept' and mac=' Wed May 4 11:48:37 2016 : Debug: attribute --> Calling-Station-Id Wed May 4 11:48:37 2016 : Debug: literal --> ' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1 Wed May 4 11:48:37 2016 : Debug: } Wed May 4 11:48:37 2016 : Debug: %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} Wed May 4 11:48:37 2016 : Debug: Parsed xlat tree: Wed May 4 11:48:37 2016 : Debug: if { Wed May 4 11:48:37 2016 : Debug: attribute --> Stripped-User-Name Wed May 4 11:48:37 2016 : Debug: } Wed May 4 11:48:37 2016 : Debug: else { Wed May 4 11:48:37 2016 : Debug: if { Wed May 4 11:48:37 2016 : Debug: attribute --> User-Name Wed May 4 11:48:37 2016 : Debug: } Wed May 4 11:48:37 2016 : Debug: else { Wed May 4 11:48:37 2016 : Debug: literal --> DEFAULT Wed May 4 11:48:37 2016 : Debug: } Wed May 4 11:48:37 2016 : Debug: } Wed May 4 11:48:37 2016 : Debug: (0) files: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} Wed May 4 11:48:37 2016 : Debug: (0) files: --> nodowifi Wed May 4 11:48:37 2016 : Debug: (0) files: SQL-User-Name set to 'nodowifi' Wed May 4 11:48:37 2016 : Debug: (0) files: ::: FROM 1 TO 9 MAX 10 Wed May 4 11:48:37 2016 : Debug: (0) files: ::: Examining SQL-User-Name Wed May 4 11:48:37 2016 : Debug: (0) files: ::: APPENDING SQL-User-Name FROM 0 TO 9 Wed May 4 11:48:37 2016 : Debug: (0) files: ::: TO in 9 out 10 Wed May 4 11:48:37 2016 : Debug: (0) files: ::: to[0] = User-Name Wed May 4 11:48:37 2016 : Debug: (0) files: ::: to[1] = User-Password Wed May 4 11:48:37 2016 : Debug: (0) files: ::: to[2] = Calling-Station-Id Wed May 4 11:48:37 2016 : Debug: (0) files: ::: to[3] = Stripped-User-Name Wed May 4 11:48:37 2016 : Debug: (0) files: ::: to[4] = Realm Wed May 4 11:48:37 2016 : Debug: (0) files: ::: to[5] = Event-Timestamp Wed May 4 11:48:37 2016 : Debug: (0) files: ::: to[6] = NAS-IP-Address Wed May 4 11:48:37 2016 : Debug: (0) files: ::: to[7] = Reply-Message Wed May 4 11:48:37 2016 : Debug: (0) files: ::: to[8] = Tipo-Usuario Wed May 4 11:48:37 2016 : Debug: (0) files: ::: to[9] = SQL-User-Name Wed May 4 11:48:37 2016 : Debug: rlm_sql (sqllocal): Reserved connection (4) Wed May 4 11:48:37 2016 : Debug: /var/log/freeradius/sqllog.sql Wed May 4 11:48:37 2016 : Debug: Parsed xlat tree: Wed May 4 11:48:37 2016 : Debug: literal --> /var/log/freeradius/sqllog.sql Wed May 4 11:48:37 2016 : Debug: (0) files: EXPAND /var/log/freeradius/sqllog.sql Wed May 4 11:48:37 2016 : Debug: (0) files: --> /var/log/freeradius/sqllog.sql Wed May 4 11:48:37 2016 : Debug: (0) files: Executing select query: SELECT username FROM radpostauth WHERE client like 'PA%' and reply='Access-Accept' and mac='f0:f6:1c:58:da:cd' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1 Wed May 4 11:48:37 2016 : Debug: rlm_sql (sqllocal): Released connection (4) Wed May 4 11:48:37 2016 : Debug: (0) files: EXPAND %{sqllocal:SELECT username FROM radpostauth WHERE client like 'PA%%' and reply='Access-Accept' and mac='%{Calling-Station-Id}' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1} Wed May 4 11:48:37 2016 : Debug: (0) files: --> 961621qb@nodo.unex.es Thank you very much in advance. :::::::::::::::::::::::::::::::::::: :: Ana Gallardo Gómez :: ::::::::::::::::::::::::::::::::::::
On May 4, 2016, at 6:05 AM, Ana Gallardo Gómez <anaougu@gmail.com> wrote:
I'm using FreeRADIUS Version 3.0.8in a debian 8.
I want to exec a sql query to return a value in Reply-Message, but I don't know why freeradius doesn't expand Calling-Station-Id every time...
Show the FULL debug output. And just use "radiusd -X". Don't use "radiusd -Xxx" unless we suggest it. Following the documentation helps you get quick and useful answers. Ignoring the documentation means it's much more difficult for us to help you. Alan DeKok.
Hello Alan, Show the FULL debug output. And just use "radiusd -X". Don't use
"radiusd -Xxx" unless we suggest it.
ok
Following the documentation helps you get quick and useful answers. Ignoring the documentation means it's much more difficult for us to help you.
Here is... (9) Received Access-Request Id 251 from 192.168.1.15:34136 to 192.168.1.6:1812 length 80 (9) User-Name = 'nodowifi@nodo.unex.es' (9) User-Password = 'pass' (9) Calling-Station-Id = 'f0:f6:1c:58:da:cd' (9) # Executing section authorize from file /etc/freeradius/sites-enabled/captive (9) authorize { (9) policy deny_realms_captive { (9) if (!&User-Name) { (9) if (!&User-Name) -> FALSE (9) if (&User-Name !~ /@((temp.)|(nodo.)|(alumnos.))?unex.es$/) { (9) if (&User-Name !~ /@((temp.)|(nodo.)|(alumnos.))?unex.es$/) -> FALSE (9) } # policy deny_realms_captive = notfound (9) policy filter_username_captive { (9) if (!&User-Name) { (9) if (!&User-Name) -> FALSE (9) if (&User-Name !~ /^[A-Za-z0-9]{3,15}@/) { (9) if (&User-Name !~ /^[A-Za-z0-9]{3,15}@/) -> FALSE (9) } # policy filter_username_captive = notfound (9) auth_log: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d (9) auth_log: --> /var/log/freeradius/radacct/ 192.168.1.15/auth-detail-20160506 (9) auth_log: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d expands to /var/log/freeradius/radacct/192.168.1.15/auth-detail-20160506 (9) auth_log: EXPAND %t (9) auth_log: --> Fri May 6 14:01:04 2016 (9) [auth_log] = ok (9) suffix: Checking for suffix after "@" (9) suffix: Looking up realm "nodo.unex.es" for User-Name = " nodowifi@nodo.unex.es" (9) suffix: Found realm "nodo.unex.es" (9) suffix: Adding Stripped-User-Name = "nodowifi" (9) suffix: Adding Realm = "nodo.unex.es" (9) suffix: Authentication realm is LOCAL (9) [suffix] = ok (9) preprocess: hints: Matched DEFAULT at 31 (9) preprocess: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (9) preprocess: --> nodowifi@nodo.unex.es (9) preprocess: SQL-User-Name set to 'nodowifi@nodo.unex.es' rlm_sql (sqllocal): Reserved connection (6) (9) preprocess: EXPAND /var/log/freeradius/sqllog.sql (9) preprocess: --> /var/log/freeradius/sqllog.sql (9) preprocess: Executing select query: SELECT username FROM radpostauth WHERE client like 'PA%' and reply='Access-Accept' and mac='' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1 (9) preprocess: SQL query returned no results rlm_sql (sqllocal): Released connection (6) (9) preprocess: EXPAND %{sqllocal:SELECT username FROM radpostauth WHERE client like 'PA%%' and reply='Access-Accept' and mac='%{Calling-Station-Id}' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1} (9) preprocess: --> (9) [preprocess] = ok (9) if (&Intentos-Reject > 10) { (9) ERROR: Failed retrieving values required to evaluate condition (9) if (&Tipo-Usuario == 'TEMPORAL') { (9) if (&Tipo-Usuario == 'TEMPORAL') -> FALSE (9) elsif (&Tipo-Usuario == 'NODO') { (9) elsif (&Tipo-Usuario == 'NODO') -> TRUE (9) elsif (&Tipo-Usuario == 'NODO') { (9) [ok] = ok (9) } # elsif (&Tipo-Usuario == 'NODO') = ok (9) ... skipping elsif for request 9: Preceding "if" was taken (9) if (&Tipo-Usuario == 'EMAIL') { (9) if (&Tipo-Usuario == 'EMAIL') -> FALSE (9) if (fail) { (9) if (fail) -> FALSE (9) elsif (notfound) { (9) elsif (notfound) -> FALSE (9) if (&Tipo-Usuario != 'TEMPORAL') { (9) if (&Tipo-Usuario != 'TEMPORAL') -> TRUE (9) if (&Tipo-Usuario != 'TEMPORAL') { (9) perlCheckRelaciones: $RAD_REQUEST{'User-Name'} = &request:User-Name -> 'nodowifi@nodo.unex.es' (9) perlCheckRelaciones: $RAD_REQUEST{'User-Password'} = &request:User-Password -> 'pass' (9) perlCheckRelaciones: $RAD_REQUEST{'NAS-IP-Address'} = &request:NAS-IP-Address -> '192.168.1.15' (9) perlCheckRelaciones: $RAD_REQUEST{'Reply-Message'} = &request:Reply-Message -> '' (9) perlCheckRelaciones: $RAD_REQUEST{'Calling-Station-Id'} = &request:Calling-Station-Id -> 'f0:f6:1c:58:da:cd' (9) perlCheckRelaciones: $RAD_REQUEST{'Event-Timestamp'} = &request:Event-Timestamp -> 'May 6 2016 14:01:04 CEST' (9) perlCheckRelaciones: $RAD_REQUEST{'Stripped-User-Name'} = &request:Stripped-User-Name -> 'nodowifi' (9) perlCheckRelaciones: $RAD_REQUEST{'Realm'} = &request:Realm -> ' nodo.unex.es' (9) perlCheckRelaciones: $RAD_REQUEST{'Module-Failure-Message'} = &request:Module-Failure-Message -> 'Failed retrieving values required to evaluate condition' (9) perlCheckRelaciones: $RAD_REQUEST{'Tipo-Usuario'} = &request:Tipo-Usuario -> 'NODO' rlm_perl: RAD_REQUEST: User-Password = pass rlm_perl: RAD_REQUEST: Tipo-Usuario = NODO rlm_perl: RAD_REQUEST: Module-Failure-Message = Failed retrieving values required to evaluate condition rlm_perl: RAD_REQUEST: Event-Timestamp = May 6 2016 14:01:04 CEST rlm_perl: RAD_REQUEST: NAS-IP-Address = 192.168.1.15 rlm_perl: RAD_REQUEST: Reply-Message = rlm_perl: RAD_REQUEST: Calling-Station-Id = f0:f6:1c:58:da:cd rlm_perl: RAD_REQUEST: User-Name = nodowifi@nodo.unex.es rlm_perl: RAD_REQUEST: Realm = nodo.unex.es rlm_perl: RAD_REQUEST: Stripped-User-Name = nodowifi (9) perlCheckRelaciones: &request:User-Password = $RAD_REQUEST{'User-Password'} -> 'pass' (9) perlCheckRelaciones: &request:Tipo-Usuario = $RAD_REQUEST{'Tipo-Usuario'} -> 'NODO' (9) perlCheckRelaciones: &request:Module-Failure-Message = $RAD_REQUEST{'Module-Failure-Message'} -> 'Failed retrieving values required to evaluate condition' (9) perlCheckRelaciones: &request:Event-Timestamp = $RAD_REQUEST{'Event-Timestamp'} -> 'May 6 2016 14:01:04 CEST' (9) perlCheckRelaciones: &request:NAS-IP-Address = $RAD_REQUEST{'NAS-IP-Address'} -> '192.168.1.15' (9) perlCheckRelaciones: &request:Reply-Message = $RAD_REQUEST{'Reply-Message'} -> '' (9) perlCheckRelaciones: &request:Calling-Station-Id = $RAD_REQUEST{'Calling-Station-Id'} -> 'f0:f6:1c:58:da:cd' (9) perlCheckRelaciones: &request:User-Name = $RAD_REQUEST{'User-Name'} -> ' nodowifi@nodo.unex.es' (9) perlCheckRelaciones: &request:Realm = $RAD_REQUEST{'Realm'} -> ' nodo.unex.es' (9) perlCheckRelaciones: &request:Stripped-User-Name = $RAD_REQUEST{'Stripped-User-Name'} -> 'nodowifi' (9) [perlCheckRelaciones] = notfound (9) if (notfound) { (9) if (notfound) -> TRUE (9) if (notfound) { (9) update { (9) &reply:Codigo-Reject = Sin-Relacion-UEX (9) } # update = noop (9) [reject] = reject (9) } # if (notfound) = reject (9) } # if (&Tipo-Usuario != 'TEMPORAL') = reject (9) } # authorize = reject (9) Using Post-Auth-Type Reject (9) # Executing group from file /etc/freeradius/sites-enabled/captive (9) Post-Auth-Type REJECT { (9) update { (9) &reply:Codigo-Reject = Credenciales-Erroneas (9) } # update = noop (9) sqllocal: EXPAND .query (9) sqllocal: --> .query (9) sqllocal: Using query template 'query' rlm_sql (sqllocal): Reserved connection (6) (9) sqllocal: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (9) sqllocal: --> nodowifi (9) sqllocal: SQL-User-Name set to 'nodowifi' (9) sqllocal: EXPAND INSERT INTO radpostauth (username, mac, client, reply, authdate, codreject, radauth) VALUES ( LOWER('%{User-Name}'), UPPER('%{Calling-Station-Id}'), '%{Client-Shortname}', '%{reply:Packet-Type}', '%S', '%{reply:Codigo-Reject}', 'radiusprueba') (9) sqllocal: --> INSERT INTO radpostauth (username, mac, client, reply, authdate, codreject, radauth) VALUES ( LOWER('nodowifi@nodo.unex.es'), UPPER('f0:f6:1c:58:da:cd'), 'cau1PC', 'Access-Reject', '2016-05-06 14:01:04', 'Sin-Relacion-UEX', 'radiusprueba') (9) sqllocal: EXPAND /var/log/freeradius/post-auth.sql (9) sqllocal: --> /var/log/freeradius/post-auth.sql (9) sqllocal: Executing query: INSERT INTO radpostauth (username, mac, client, reply, authdate, codreject, radauth) VALUES ( LOWER(' nodowifi@nodo.unex.es'), UPPER('f0:f6:1c:58:da:cd'), 'cau1PC', 'Access-Reject', '2016-05-06 14:01:04', 'Sin-Relacion-UEX', 'radiusprueba') (9) sqllocal: SQL query returned: success (9) sqllocal: 1 record(s) updated rlm_sql (sqllocal): Released connection (6) (9) [sqllocal] = ok (9) if (fail) { (9) if (fail) -> FALSE (9) attr_filter.access_reject: EXPAND %{User-Name} (9) attr_filter.access_reject: --> nodowifi@nodo.unex.es (9) attr_filter.access_reject: Matched entry DEFAULT at line 18 (9) [attr_filter.access_reject] = updated (9) } # Post-Auth-Type REJECT = updated (9) Delaying response for 1.000000 seconds Waking up in 0.3 seconds. Waking up in 0.6 seconds. (9) <delay>: Sending delayed response (9) <delay>: Sent Access-Reject Id 251 from 192.168.1.6:1812 to 192.168.1.15:34136 length 20 Waking up in 3.9 seconds. (9) <delay>: Cleaning up request packet ID 251 with timestamp +122 Ready to process requests :::::::::::::::::::::::::::::::::::: :: Ana Gallardo Gómez :: ::::::::::::::::::::::::::::::::::::
On May 6, 2016, at 8:28 AM, Ana Gallardo Gómez <anaougu@gmail.com> wrote:
Here is...
(9) Received Access-Request Id 251 from 192.168.1.15:34136 to 192.168.1.6:1812 length 80 (9) User-Name = 'nodowifi@nodo.unex.es' (9) User-Password = 'pass' (9) Calling-Station-Id = 'f0:f6:1c:58:da:cd'
And that shows the Calling-Station-Id exists, and is expanded properly. So... what's wrong? Alan DeKok.
Hello,
(9) Received Access-Request Id 251 from 192.168.1.15:34136 to
192.168.1.6:1812 length 80 (9) User-Name = 'nodowifi@nodo.unex.es' (9) User-Password = 'pass' (9) Calling-Station-Id = 'f0:f6:1c:58:da:cd'
And that shows the Calling-Station-Id exists, and is expanded properly.
So... what's wrong?
Here I don't have Calling-Station-Id to my sql query: (9) preprocess: Executing select query: SELECT username FROM radpostauth WHERE client like 'PA%' and reply='Access-Accept' and mac='' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1 (9) preprocess: SQL query returned no results rlm_sql (sqllocal): Released connection (6) (9) preprocess: EXPAND %{sqllocal:SELECT username FROM radpostauth WHERE client like 'PA%%' and reply='Access-Accept' and mac='%{Calling-Station-Id}' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1} and here in postauth section, I have: (9) sqllocal: EXPAND INSERT INTO radpostauth (username, mac, client, reply, authdate, codreject, radauth) VALUES ( LOWER('%{User-Name}'), UPPER('%{Calling-Station-Id}'), '%{Client-Shortname}', '%{reply:Packet-Type}', '%S', '%{reply:Codigo-Reject}', 'radiusprueba') (9) sqllocal: --> INSERT INTO radpostauth (username, mac, client, reply, authdate, codreject, radauth) VALUES ( LOWER('nodowifi@nodo.unex.es'), UPPER('f0:f6:1c:58:da:cd'), 'cau1PC', 'Access-Reject', '2016-05-06 14:01:04', 'Sin-Relacion-UEX', 'radiusprueba') Thank you. :::::::::::::::::::::::::::::::::::: :: Ana Gallardo Gómez :: ::::::::::::::::::::::::::::::::::::
On May 6, 2016, at 8:53 AM, Ana Gallardo Gómez <anaougu@gmail.com> wrote:
Here I don't have Calling-Station-Id to my sql query:
(9) preprocess: Executing select query: SELECT username FROM radpostauth WHERE client like 'PA%' and reply='Access-Accept' and mac='' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1
Your query is wrong. You cannot put "PA%" into the query. You MUST escape the "%", by using "%%". Pay attention to the real problem. The Calling-Station-Id exists in the Access-Request, and is expanded everywhere else. In the query you're worried about, the SQL module returns no results. The Calling-Station-Id exists, so the problem is somewhere else. So... the problem isn't the expansion of Calling-Station-Id. Alan DeKok.
Hello Alan, Your query is wrong. You cannot put "PA%" into the query. You MUST
escape the "%", by using "%%".
I have %% in my query: $ cat /etc/freeradius/mods-config/preprocess/hints DEFAULT Realm == "nodo.unex.es" Reply-Message = "%{sqllocal:SELECT username FROM radpostauth WHERE client like 'PA%%' and reply='Access-Accept' and mac='%{Calling-Station-Id}' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1}", Tipo-Usuario := NODO Pay attention to the real problem. The Calling-Station-Id exists in the
Access-Request, and is expanded everywhere else.
In the query you're worried about, the SQL module returns no results. The module returns no results because mac its empty... ¿why? (0) preprocess: hints: Matched DEFAULT at 31 (0) preprocess: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (0) preprocess: --> nodowifi@nodo.unex.es (0) preprocess: SQL-User-Name set to 'nodowifi@nodo.unex.es' rlm_sql (sqllocal): Reserved connection (4) (0) preprocess: EXPAND /var/log/freeradius/sqllog.sql (0) preprocess: --> /var/log/freeradius/sqllog.sql (0) preprocess: Executing select query: SELECT username FROM radpostauth WHERE client like 'PA%' and reply='Access-Accept' and mac='' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1 (0) preprocess: SQL query returned no results rlm_sql (sqllocal): Released connection (4) (0) preprocess: EXPAND %{sqllocal:SELECT username FROM radpostauth WHERE client like 'PA%%' and reply='Access-Accept' and mac='%{Calling-Station-Id}' and authdate > (SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)) order by authdate desc limit 1} (0) preprocess: -->
The Calling-Station-Id exists, so the problem is somewhere else.
So... the problem isn't the expansion of Calling-Station-Id.
I don't know where is my problem, but I think my query its ok Thank you very much :::::::::::::::::::::::::::::::::::: :: Ana Gallardo Gómez :: ::::::::::::::::::::::::::::::::::::
On Mon, May 09, 2016 at 09:12:42AM +0200, Ana Gallardo Gómez wrote:
The module returns no results because mac its empty... ¿why?
I've just tested this and reproduced on 3.0.8. It's fixed in 3.0.x HEAD. You need to upgrade, as I suggested last week. Matthew -- Matthew Newton, Ph.D. <mcn4@le.ac.uk> Systems Specialist, Infrastructure Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom For IT help contact helpdesk extn. 2253, <ithelp@le.ac.uk>
Hello!! I'm using 2.2.9 version, with mysql and Mikrotik NAS. My acct_unique module is in default mode: acct_unique { key = "User-Name, Acct-Session-Id, NAS-IP-Address, NAS-Identifier, NAS-Port" } Sometimes I have this error in my radius.log Error: [sql] Couldn't insert SQL accounting START record - Duplicate entry 'c06a0179ea129d88' for key 'acctuniqueid' How can I solve this? Any idea? Thanks Aurélio
On May 10, 2016, at 9:15 PM, Aurélio de Souza Ribeiro Neto <netolistas@mpc.com.br> wrote:
I'm using 2.2.9 version, with mysql and Mikrotik NAS.
My acct_unique module is in default mode:
acct_unique { key = "User-Name, Acct-Session-Id, NAS-IP-Address, NAS-Identifier, NAS-Port" }
Sometimes I have this error in my radius.log
Error: [sql] Couldn't insert SQL accounting START record - Duplicate entry 'c06a0179ea129d88' for key 'acctuniqueid'
How can I solve this? Any idea?
It means you have duplicate accounting Start records. The problem is most likely that your NAS is re-using values for Acct-Session-Id. Either upgrade your NAS, or buy a NAS that works. There is pretty much nothing you can do to FreeRADIUS to "fix" the issue. The NAS is essentially lying to the server, and no amount of server-side changes will make the NAS work correctly. Alan DeKok.
Hi Matthew, sorry but I don't read your suggestion... ¿where is? I had problems with the list because I had mail delivery disable. I reply you via web interface but I don't know if you receive my response. Thank you very much for your response. I'll upgrade. Sorry for my english. :::::::::::::::::::::::::::::::::::: :: Ana Gallardo Gómez :: ::::::::::::::::::::::::::::::::::::
participants (4)
-
Alan DeKok -
Ana Gallardo Gómez -
Aurélio de Souza Ribeiro Neto -
Matthew Newton