3GPP data in mysql radacct table

[liran kessel]

I have added this entry as suggested to the dialup.conf file

>> update request {
>>   Tmp-Integer-1 := "%{3GPP-Location-Info}
>> }

and then updated the insert and update commands to call the Tmp-integer-1. 
example 


        accounting_onoff_query = "\
          UPDATE ${acct_table1} \
          SET \
             acctstoptime       =  '%S', \
             acctsessiontime    =  unix_timestamp('%S') - \
                                   unix_timestamp(acctstarttime), \
             acctterminatecause =  '%{Acct-Terminate-Cause}', \
             acctstopdelay      =  %{%{Acct-Delay-Time}:-0}, \
             mccmnc             =  %{3GPP-IMSI-MCC-MNC}, \
             locationinfo       =  %{Tmp-Integer-1} \
          WHERE acctstoptime IS NULL \
          AND nasipaddress      =  '%{NAS-IP-Address}' \
          AND acctstarttime     <= '%S'"

        accounting_update_query = " \
          UPDATE ${acct_table1} \
          SET \
             framedipaddress = '%{Framed-IP-Address}', \
             acctsessiontime     = '%{%{Acct-Session-Time}:-0}', \
             acctinputoctets     = '%{%{Acct-Input-Gigawords}:-0}'  << 32 | \
                                   '%{%{Acct-Input-Octets}:-0}', \
             acctoutputoctets    = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
                                   '%{%{Acct-Output-Octets}:-0}', \
             mccmnc             =  %{3GPP-IMSI-MCC-MNC}, \
             locationinfo      =  %{Tmp-Integer-1} \
          WHERE acctsessionid = '%{Acct-Session-Id}' \
          AND username        = '%{SQL-User-Name}' \
          AND nasipaddress    = '%{NAS-IP-Address}’"



However when we get data , and its stored to the radacct table the locationinfo field (bigint) is entered with a 0 rather than the translation of the 3GPP-Location-Info translated Hexa data.

Could yo please assist me in explaining where my mistake is?

Thanks
Liran

> On 16 May 2016, at 4:40 PM, liran kessel <lirankessel at gmail.com> wrote:
> 
> I tried the change as you recommended in sql_log but it didn’t effect the servers configuration.
> 
> afterwards i changed the /etc/radact/sql/mysql/dialup.conf and this helped me get the info into the DB.
> 
> regarding your point on  configuring this update_request.
>> update request {
>>   Tmp-Integer-1 := "%{3GPP-Location-Info}"
>> }
> 
> where would I do that? in the dialup.conf file? as part of the update command or a separate section?
> 
> Thanks
> 
> 
>> On 13 May 2016, at 3:06 PM, Matthew Newton <mcn4 at leicester.ac.uk> wrote:
>> 
>> On Fri, May 13, 2016 at 12:25:08PM +0300, liran kessel wrote:
>>> I can see the schema.conf the create table of radacct, so I can
>>> just add the extra fields and recreate the table? and add the
>>> Location-info as an integer?
>> 
>> Yes
>> 
>>> regarding the sql_log this is the entry 
>>>       Start = "INSERT INTO ${acct_table} (AcctSessionId, UserName, \
>>>        NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \
>>>        AcctSessionTime, AcctTerminateCause) VALUES                 \
>>>        ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \
>>>        '%{Framed-IP-Address}', '%S', '0', '0', '');”
>>> 
>>> so I would need to add the name of the fields to the insert statements based on their field names?
>>> for example 
>>> %{ 3GPP-Location-Info} 
>>> %{3GPP-IMSI-MCC-MNC}
>>> 
>>> correct? 
>> 
>> Yes, but...
>> 
>>> How would i configure the hex translation?
>> 
>> Do something like
>> 
>> update request {
>>   Tmp-Integer-1 := "%{3GPP-Location-Info}"
>> }
>> 
>> before calling sql, then write Tmp-Integer-1 to the database
>> rather than 3GPP-Location-Info.
>> 
>> Matthew
>> 
>> 
>> -- 
>> Matthew Newton, Ph.D. <mcn4 at 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 at le.ac.uk>
>> -
>> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>