Re: 3GPP data in mysql radacct table

20 May 2016

      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@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@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@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>
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html