3GPP data in mysql radacct table

[liran kessel]

I have done as you suggested, and still I get only 0 in the locationinfo field in the DB. 

here is the extract fro the sites-enabled/default file

accounting {
        #
        #  Create a 'detail'ed log of the packets.
        #  Note that accounting requests which are proxied
        #  are also logged in the detail file.
        detail
#       daily

        #  Update the wtmp file
        #
        #  If you don't use "radlast", you can delete this line.
#       unix

        #
        #  For Simultaneous-Use tracking.
        #
        #  Due to packet losses in the network, the data here
        #  may be incorrect.  There is little we can do about it.
#       radutmp
#       sradutmp

        #  Return an address to the IP Pool when we see a stop record.
#       main_pool


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

        #
        #  Log traffic to an SQL database.
        #
        #  See "Accounting queries" in sql.conf
        sql



and also an section of the dialup.conf file . 


       accounting_update_query_alt = " \
          INSERT INTO ${acct_table1} \
            (acctsessionid,    acctuniqueid,      username, \
             realm,            nasipaddress,      nasportid, \
             nasporttype,      acctstarttime,     acctsessiontime, \
             acctauthentic,    connectinfo_start, acctinputoctets, \
             acctoutputoctets, calledstationid,   callingstationid, \
             servicetype,      framedprotocol,    framedipaddress, \
             acctstartdelay,   xascendsessionsvrkey, mccmnc, locationinfo) \
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', \
             DATE_SUB('%S', \
                      INTERVAL (%{%{Acct-Session-Time}:-0} + \
                                %{%{Acct-Delay-Time}:-0}) SECOND), \
                      '%{%{Acct-Session-Time}:-0}', \
             '%{Acct-Authentic}', '', \
             '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
             '%{%{Acct-Input-Octets}:-0}', \
             '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
             '%{%{Acct-Output-Octets}:-0}', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', \
             '%{Service-Type}', '%{Framed-Protocol}', \
             '%{Framed-IP-Address}', \
             '0', '%{X-Ascend-Session-Svr-Key}','%{3GPP-IMSI-MCC-MNC}','%{Tmp-Integer-1}’)"


        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}’"





any suggestions on where and why I still can’t get any values in the DB for that field.


Thanks
Liran


> On 21 May 2016, at 12:18 AM, Matthew Newton <mcn4 at leicester.ac.uk> wrote:
> 
> On Sat, May 21, 2016 at 12:06:28AM +0300, liran kessel wrote:
>> I have added this entry as suggested to the dialup.conf file
>> 
>>>> update request {
>>>>  Tmp-Integer-1 := "%{3GPP-Location-Info}
>>>> }
> ...
>> Could yo please assist me in explaining where my mistake is?
> 
> The above should go in sites-enabled/default in the accounting{}
> section, before you call sql.
> 
> 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