Hello, I am observing some peculiar behavior in my debug log. Currently running freeradius 3.0.12 When modules are loaded , I get the following debug output. I have followed this guide : https://networkradius.com/doc/3.0.9/upgrading/changed-modules.html including configuration file /etc/raddb/mods-enabled/oracle /etc/raddb/mods-enabled/oracle[171]: Reference "${....accounting_onoff_query}" not found /etc/raddb/mods-enabled/oracle[174]: Reference "${....accounting_onoff_query}" not found /etc/raddb/mods-enabled/oracle[177]: Reference "${....accounting_start_query}" not found /etc/raddb/mods-enabled/oracle[178]: Reference "${....accounting_start_query_alt}" not found /etc/raddb/mods-enabled/oracle[181]: Reference "${....accounting_update_query}" not found /etc/raddb/mods-enabled/oracle[182]: Reference "${....accounting_update_query_alt}" not found /etc/raddb/mods-enabled/oracle[185]: Reference "${....accounting_stop_query}" not found /etc/raddb/mods-enabled/oracle[186]: Reference "${....accounting_stop_query_alt}" not found But when the rlm_sql_oracle module is loaded I observe that the queries are loaded by the module as it should be expected. Please see the following debug log: accounting { reference = "%{tolower:type.%{Acct-Status-Type}.query}" type { accounting-on { query = "UPDATE radacct SET AcctStopTime=TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), AcctSessionTime=((TO_DATE('%S','yyyy-mm-dd hh24:mi:ss') - AcctStartTime)*86400), AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = %{Acct-Delay-Time} WHERE AcctSessionTime=0 AND AcctStopTime IS NULL AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStartTime <= TO_DATE('%S','yyyy-mm-dd hh24:mi:ss')" } accounting-off { query = "UPDATE radacct SET AcctStopTime=TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), AcctSessionTime=((TO_DATE('%S','yyyy-mm-dd hh24:mi:ss') - AcctStartTime)*86400), AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = %{Acct-Delay-Time} WHERE AcctSessionTime=0 AND AcctStopTime IS NULL AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStartTime <= TO_DATE('%S','yyyy-mm-dd hh24:mi:ss')" } start { query = "INSERT into radacct (RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay,CiscoAV,Status) values(radacct_seq.nextval, '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port-Id}', '%{NAS-Port-Type}', TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{ERX-Pppoe-Description}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0','%{Cisco-AVPair}','start')" } interim-update { query = "UPDATE radacct SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', ''),AcctSessionTime = '%{Acct-Session-Time}',AcctInputOctets = '%{Acct-Input-Octets}',AcctOutputOctets = '%{Acct-Output-Octets}',charged=0,Status = 'update' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}'" } stop { query = "UPDATE radacct SET AcctStopTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}',Status = 'stop' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL" } } } post-auth { reference = ".query" } } rlm_sql (sql): Driver rlm_sql_oracle (module rlm_sql_oracle) loaded and linked *The Content of my Orac**le sql file*: reference = "%{tolower:type.%{Acct-Status-Type}.query}" type { accounting-on { query = "${....accounting_onoff_query}" } accounting-off { query = "${....accounting_onoff_query}" } start { query = "${....accounting_start_query}" query = "${....accounting_start_query_alt}" } interim-update { query = "${....accounting_update_query}" query = "${....accounting_update_query_alt}" } stop { query = "${....accounting_stop_query}" query = "${....accounting_stop_query_alt}" } } } post-auth { query = "${..postauth_query}" } accounting_onoff_query = <same query used in radius 2.x > accounting_update_query = <same query used in radius 2.x > accounting_update_query_alt = <same query used in radius 2.x > accounting_start_query = <same query used in radius 2.x > accounting_start_query_alt = <same query used in radius 2.x > accounting_stop_query = <same query used in radius 2.x > accounting_stop_query_alt = <same query used in radius 2.x > The queries are the same as mentioned above, in the debug log. The issue as described above , it shows "reference not found" but then later is capable of loading the queries. And they are working as well when a user is connected to the NAS. any guidance is greatly appreciated. -- *Mustafa Mujahid | Systems* /Operations Engineer/
On Jan 24, 2017, at 4:22 AM, Mustafa Mujahid/SYS <mustafa.mujahid@nayatel.com> wrote:
Hello,
I am observing some peculiar behavior in my debug log. Currently running freeradius 3.0.12
When modules are loaded , I get the following debug output. I have followed this guide : https://networkradius.com/doc/3.0.9/upgrading/changed-modules.html
You followed the guide to do what?
including configuration file /etc/raddb/mods-enabled/oracle /etc/raddb/mods-enabled/oracle[171]: Reference "${....accounting_onoff_query}" not found /etc/raddb/mods-enabled/oracle[174]: Reference "${....accounting_onoff_query}" not found /etc/raddb/mods-enabled/oracle[177]: Reference "${....accounting_start_query}" not found /etc/raddb/mods-enabled/oracle[178]: Reference "${....accounting_start_query_alt}" not found /etc/raddb/mods-enabled/oracle[181]: Reference "${....accounting_update_query}" not found /etc/raddb/mods-enabled/oracle[182]: Reference "${....accounting_update_query_alt}" not found /etc/raddb/mods-enabled/oracle[185]: Reference "${....accounting_stop_query}" not found /etc/raddb/mods-enabled/oracle[186]: Reference "${....accounting_stop_query_alt}" not found
The guide doesn't say to do that. Whatever you've done is wrong, and that's what the message is telling you.
But when the rlm_sql_oracle module is loaded I observe that the queries are loaded by the module as it should be expected. Please see the following debug log:
Because it's reading the queries from somewhere else.
*The Content of my Orac**le sql file*:
Please don't edit the configuration unless you know why you're making those changes, and what those changes do.
accounting_onoff_query = <same query used in radius 2.x > accounting_update_query = <same query used in radius 2.x > accounting_update_query_alt = <same query used in radius 2.x > accounting_start_query = <same query used in radius 2.x > accounting_start_query_alt = <same query used in radius 2.x > accounting_stop_query = <same query used in radius 2.x > accounting_stop_query_alt = <same query used in radius 2.x >
Don't do that. The queries in v3 work, and have been updated for a reason.
The queries are the same as mentioned above, in the debug log. The issue as described above , it shows "reference not found" but then later is capable of loading the queries. And they are working as well when a user is connected to the NAS.
any guidance is greatly appreciated.
Remove all of your changes, and use a stock "sql" module from v3, using the v3 configuration, and using the v3 queries. Change it to use: driver = rlm_sql_oracle dialect = oracle It *will* work. The queries in v3 have been updated to allow for more complex situations, so that the data in the "radacct" table is updated in more situations. The underlying "radacct" table is the same, and contains the same information. Again, you've put a lot of work into destroying the configuration and breaking the server. Please don't do that. The default configuration works. Why not use that? Alan DeKok.
Hey, Sorry I destroyed the config, it was written in the article " Alternatively a 2.x.x config may be patched to work with the 3.0 module by adding the following: " The following being the accounting block I added in the sql file. Anyways Itook your queue and its working now, I'm still testing before taking it into enterprise deployment. Thank you for all the help, it's much appreciated. *Mustafa Mujahid | Systems* /Operations Engineer/ On 01/24/2017 07:55 PM, Alan DeKok wrote:
On Jan 24, 2017, at 4:22 AM, Mustafa Mujahid/SYS <mustafa.mujahid@nayatel.com> wrote:
Hello,
I am observing some peculiar behavior in my debug log. Currently running freeradius 3.0.12
When modules are loaded , I get the following debug output. I have followed this guide : https://networkradius.com/doc/3.0.9/upgrading/changed-modules.html You followed the guide to do what?
including configuration file /etc/raddb/mods-enabled/oracle /etc/raddb/mods-enabled/oracle[171]: Reference "${....accounting_onoff_query}" not found /etc/raddb/mods-enabled/oracle[174]: Reference "${....accounting_onoff_query}" not found /etc/raddb/mods-enabled/oracle[177]: Reference "${....accounting_start_query}" not found /etc/raddb/mods-enabled/oracle[178]: Reference "${....accounting_start_query_alt}" not found /etc/raddb/mods-enabled/oracle[181]: Reference "${....accounting_update_query}" not found /etc/raddb/mods-enabled/oracle[182]: Reference "${....accounting_update_query_alt}" not found /etc/raddb/mods-enabled/oracle[185]: Reference "${....accounting_stop_query}" not found /etc/raddb/mods-enabled/oracle[186]: Reference "${....accounting_stop_query_alt}" not found The guide doesn't say to do that. Whatever you've done is wrong, and that's what the message is telling you.
But when the rlm_sql_oracle module is loaded I observe that the queries are loaded by the module as it should be expected. Please see the following debug log: Because it's reading the queries from somewhere else.
*The Content of my Orac**le sql file*: Please don't edit the configuration unless you know why you're making those changes, and what those changes do.
accounting_onoff_query = <same query used in radius 2.x > accounting_update_query = <same query used in radius 2.x > accounting_update_query_alt = <same query used in radius 2.x > accounting_start_query = <same query used in radius 2.x > accounting_start_query_alt = <same query used in radius 2.x > accounting_stop_query = <same query used in radius 2.x > accounting_stop_query_alt = <same query used in radius 2.x >
Don't do that. The queries in v3 work, and have been updated for a reason.
The queries are the same as mentioned above, in the debug log. The issue as described above , it shows "reference not found" but then later is capable of loading the queries. And they are working as well when a user is connected to the NAS.
any guidance is greatly appreciated. Remove all of your changes, and use a stock "sql" module from v3, using the v3 configuration, and using the v3 queries. Change it to use:
driver = rlm_sql_oracle dialect = oracle
It *will* work.
The queries in v3 have been updated to allow for more complex situations, so that the data in the "radacct" table is updated in more situations. The underlying "radacct" table is the same, and contains the same information.
Again, you've put a lot of work into destroying the configuration and breaking the server. Please don't do that. The default configuration works. Why not use that?
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (2)
-
Alan DeKok -
Mustafa Mujahid/SYS