Issue in loading acct queries

24 Jan 2017

      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/

Issue in loading acct queries

Mustafa Mujahid/SYS