3GPP data in mysql radacct table
Hi, I have activated a freeradius server to collect accounting info from a GGSN. The server is up and running and storing "GPRS-PDP-Context” data in to the radacct table. However there are some important 3GPP fields that aren’t written to the table. for example we want to store the 3GPP-IMSI-MCC-MNC and 3GPP-Location-Info data in the DB. Is it possible to configure the sql_log to write this info into the DB? and obviously this would require also to add the fields to the table. In addition is there a function to translate the 3GPP-Location-Info from hex to decimal data? Thanks in Advance. Liran
On Thu, May 12, 2016 at 09:08:29PM +0300, liran kessel wrote:
Is it possible to configure the sql_log to write this info into the DB? and obviously this would require also to add the fields to the table.
That's why the queries are in the config and configurable. raddb/mods-config/sql/main/*/queries.conf
In addition is there a function to translate the 3GPP-Location-Info from hex to decimal data?
Assigning a hex string (prefixed with 0x) to an integer attribute should convert it automatically. Cheers, 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>
Hi, Thanks for the help, however we don’t have a mods-config directory with all its sub directories. Thanks Liran
On 13 May 2016, at 1:46 AM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
On Thu, May 12, 2016 at 09:08:29PM +0300, liran kessel wrote:
Is it possible to configure the sql_log to write this info into the DB? and obviously this would require also to add the fields to the table.
That's why the queries are in the config and configurable.
raddb/mods-config/sql/main/*/queries.conf
In addition is there a function to translate the 3GPP-Location-Info from hex to decimal data?
Assigning a hex string (prefixed with 0x) to an integer attribute should convert it automatically.
Cheers,
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
On Fri, May 13, 2016 at 11:06:41AM +0300, liran kessel wrote:
Thanks for the help, however we don’t have a mods-config directory with all its sub directories.
FreeRADIUS version 2? Look in any of raddb/modules/sql_log raddb/sql/*/*.conf 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>
yes version 2.2.6 was installed. 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? 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? How would i configure the hex translation? Thanks
On 13 May 2016, at 12:15 PM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
On Fri, May 13, 2016 at 11:06:41AM +0300, liran kessel wrote:
Thanks for the help, however we don’t have a mods-config directory with all its sub directories.
FreeRADIUS version 2?
Look in any of
raddb/modules/sql_log raddb/sql/*/*.conf
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
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>
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
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
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@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>
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@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@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
On Sun, May 22, 2016 at 09:24:46AM +0300, liran kessel wrote:
I have done as you suggested, and still I get only 0 in the locationinfo field in the DB.
What does the full debug output show? (Start with "radiusd -X", capture all the output, send a test packet, post debug back here.) 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>
Here you go, but for some reason I don’t see the whole update command. Ready to process requests. rad_recv: Accounting-Request packet from host 172.28.42.136 port 58599, id=100, length=402 Acct-Status-Type = Stop Event-Timestamp = "May 22 2016 23:21:02 IDT" Acct-Input-Octets = 9475106 Acct-Input-Gigawords = 0 Acct-Output-Octets = 72231702 Acct-Output-Gigawords = 0 Acct-Input-Packets = 74888 Acct-Output-Packets = 75139 Acct-Session-Time = 35815 Acct-Terminate-Cause = User-Request Framed-IP-Address = 10.236.64.190 Called-Station-Id = "sphone" Calling-Station-Id = "972526786885" NAS-IP-Address = 172.28.42.136 NAS-Identifier = "172.28.42.136" Framed-Protocol = GPRS-PDP-Context Service-Type = Framed-User NAS-Port-Type = Virtual Acct-Session-Id = "3E5A4441738239e7" 3GPP-IMSI = "425020173204974" 3GPP-IMSI-MCC-MNC = "42502" 3GPP-SGSN-Address = 62.90.68.102 3GPP-NSAPI = "5" 3GPP-GGSN-Address = 62.90.68.65 3GPP-GGSN-MCC-MNC = "42502" 3GPP-Charging-Characteristics = "0500" 3GPP-IMEISV = "3586350507743300" 3GPP-RAT-Type = UTRAN 3GPP-Location-Info = 0x0024f5205943a020 3GPP-Charging-ID = 1937914343 3GPP-PDP-Type = 0 3GPP-Charging-Gateway-Address = 172.28.230.13 3GPP-Session-Stop-Indicator = 255 3GPP-Selection-Mode = "1" 3GPP-Negotiated-DSCP = 20 3GPP-GPRS-Negotiated-QoS-profile = "08-0207000021C000003E80" # Executing section preacct from file /etc/raddb/sites-enabled/default +group preacct { ++[preprocess] = ok [acct_unique] WARNING: Attribute NAS-Port was not found in request, unique ID MAY be inconsistent [acct_unique] WARNING: Attribute User-Name was not found in request, unique ID MAY be inconsistent [acct_unique] Hashing ',NAS-Identifier = "172.28.42.136",NAS-IP-Address = 172.28.42.136,Acct-Session-Id = "3E5A4441738239e7",' [acct_unique] Acct-Unique-Session-ID = "49c7f864c2f3216d". ++[acct_unique] = ok [suffix] Proxy reply, or no User-Name. Ignoring. ++[suffix] = ok ++[files] = noop +} # group preacct = ok # Executing section accounting from file /etc/raddb/sites-enabled/default +group accounting { [detail] expand: %{Packet-Src-IP-Address} -> 172.28.42.136 [detail] expand: /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d -> /var/log/radius/radacct/172.28.42.136/detail-20160522 [detail] /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radius/radacct/172.28.42.136/detail-20160522 [detail] expand: %t -> Sun May 22 23:20:58 2016 ++[detail] = ok ++update request { expand: %{3GPP-Location-Info} -> 0x0024f5205943a020 ++} # update request = noop [sql] expand: %{User-Name} -> [sql] sql_set_user escaped user --> '' [sql] expand: %{Acct-Session-Time} -> 35815 [sql] expand: %{Acct-Input-Gigawords} -> 0 [sql] expand: %{Acct-Input-Octets} -> 9475106 [sql] expand: %{Acct-Output-Gigawords} -> 0 [sql] expand: %{Acct-Output-Octets} -> 72231702 [sql] expand: %{Acct-Delay-Time} -> [sql] ... expanding second conditional [sql] expand: UPDATE radacct SET acctstoptime = '%S', 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}', acctterminatecause = '%{Acct-Terminate-Cause}', acctstopdelay = '%{%{Acct-Delay-Time}:-0}', connectinfo_stop = '%{Connect-Info}' WHERE acctsessionid = '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}' AND nasipaddress = '%{NAS-IP-Address}' -> UPDATE radacct SET acctstoptime = '2016-05-22 23:20:58', acctsessiontime = '35815', acctinputoctets = '0' << 32 | '9475106', acctoutputoctets = ' rlm_sql (sql): Reserving sql socket id: 31 rlm_sql (sql): Released sql socket id: 31 ++[sql] = ok ++[exec] = noop [attr_filter.accounting_response] expand: %{User-Name} -> ++[attr_filter.accounting_response] = noop +} # group accounting = ok Sending Accounting-Response of id 100 to 172.28.42.136 port 58599 Finished request 0. Cleaning up request 0 ID 100 with timestamp +16 Going to the next request Ready to process requests. Acct-Status-Type = Start Event-Timestamp = "May 22 2016 23:21:48 IDT" Framed-IP-Address = 10.236.64.134 Called-Station-Id = "sphone" Calling-Station-Id = "972526786885" NAS-IP-Address = 172.28.42.136 NAS-Identifier = "172.28.42.136" Framed-Protocol = GPRS-PDP-Context Service-Type = Framed-User NAS-Port-Type = Virtual Acct-Session-Id = "3E5A44417351a5f0" 3GPP-IMSI = "425020173204974" 3GPP-IMSI-MCC-MNC = "42502" 3GPP-SGSN-Address = 62.90.68.102 3GPP-NSAPI = "5" 3GPP-GGSN-Address = 62.90.68.65 3GPP-GGSN-MCC-MNC = "42502" 3GPP-Charging-Characteristics = "0500" 3GPP-IMEISV = "3586350507743300" 3GPP-RAT-Type = UTRAN 3GPP-Location-Info = 0x0024f520834bf9bf 3GPP-Charging-ID = 1934730736 3GPP-PDP-Type = 0 3GPP-Charging-Gateway-Address = 172.28.230.13 3GPP-Selection-Mode = "1" 3GPP-Negotiated-DSCP = 20 3GPP-GPRS-Negotiated-QoS-profile = "08-0207000021C000003E80" # Executing section preacct from file /etc/raddb/sites-enabled/default +group preacct { ++[preprocess] = ok [acct_unique] WARNING: Attribute NAS-Port was not found in request, unique ID MAY be inconsistent [acct_unique] WARNING: Attribute User-Name was not found in request, unique ID MAY be inconsistent [acct_unique] Hashing ',NAS-Identifier = "172.28.42.136",NAS-IP-Address = 172.28.42.136,Acct-Session-Id = "3E5A44417351a5f0",' [acct_unique] Acct-Unique-Session-ID = "76377b6d63f9b985". ++[acct_unique] = ok [suffix] Proxy reply, or no User-Name. Ignoring. ++[suffix] = ok ++[files] = noop +} # group preacct = ok # Executing section accounting from file /etc/raddb/sites-enabled/default +group accounting { [detail] expand: %{Packet-Src-IP-Address} -> 172.28.42.136 [detail] expand: /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d -> /var/log/radius/radacct/172.28.42.136/detail-20160522 [detail] /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radius/radacct/172.28.42.136/detail-20160522 [detail] expand: %t -> Sun May 22 23:21:45 2016 ++[detail] = ok ++update request { expand: %{3GPP-Location-Info} -> 0x0024f520834bf9bf ++} # update request = noop [sql] expand: %{User-Name} -> [sql] sql_set_user escaped user --> '' [sql] expand: %{Acct-Delay-Time} -> [sql] ... expanding second conditional [sql] expand: INSERT INTO radacct (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, xascendsessionsvrkey, mccmnc, locationinfo) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', '%S', NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Fram rlm_sql (sql): Reserving sql socket id: 30 rlm_sql (sql): Released sql socket id: 30 ++[sql] = ok ++[exec] = noop [attr_filter.accounting_response] expand: %{User-Name} -> ++[attr_filter.accounting_response] = noop +} # group accounting = ok Sending Accounting-Response of id 101 to 172.28.42.136 port 58599 Finished request 1. Cleaning up request 1 ID 101 with timestamp +63 Going to the next request Ready to process requests.
On 22 May 2016, at 6:48 PM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
On Sun, May 22, 2016 at 09:24:46AM +0300, liran kessel wrote:
I have done as you suggested, and still I get only 0 in the locationinfo field in the DB.
What does the full debug output show? (Start with "radiusd -X", capture all the output, send a test packet, post debug back here.)
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
On Sun, May 22, 2016 at 11:22:46PM +0300, liran kessel wrote:
Here you go, but for some reason I don’t see the whole update command.
You missed all the detail from the top, so I don't know what version you're running, but tested and it works fine on latest 3.0. You'll need to update to use Tmp-Integer64-1. The number is too big for 32 bits. This works here: update request { Tmp-Integer64-1 := &3GPP-Location-Info } sql which, using your supplied packet, results in the db containing: sqlite> select test from radacct; 10402618446946336 sqlite> 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>
I have version 2.2.6 but it fails when trying to add Tmp-Integer64-1 (see attached). Module: Instantiating module "acct_unique" from file /etc/raddb/modules/acct_unique acct_unique { key = "User-Name, Acct-Session-Id, NAS-IP-Address, NAS-Identifier, NAS-Port" } Module: Checking accounting {...} for more modules to load Module: Linked to module rlm_detail Module: Instantiating module "detail" from file /etc/raddb/modules/detail detail { detailfile = "/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d" header = "%t" detailperm = 384 dirperm = 493 locking = no log_packet_header = no } /etc/raddb/sites-enabled/default[404]: ERROR: Unknown vendor name in attribute name "Tmp-Integer64-1" /etc/raddb/sites-enabled/default[378]: Errors parsing accounting section. we will upgrade to version 3?
On 23 May 2016, at 12:50 PM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
On Sun, May 22, 2016 at 11:22:46PM +0300, liran kessel wrote:
Here you go, but for some reason I don’t see the whole update command.
You missed all the detail from the top, so I don't know what version you're running, but tested and it works fine on latest 3.0.
You'll need to update to use Tmp-Integer64-1. The number is too big for 32 bits.
This works here:
update request { Tmp-Integer64-1 := &3GPP-Location-Info }
sql
which, using your supplied packet, results in the db containing:
sqlite> select test from radacct; 10402618446946336 sqlite>
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
can i install version 3 on redhat 6.7?
On 23 May 2016, at 12:50 PM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
On Sun, May 22, 2016 at 11:22:46PM +0300, liran kessel wrote:
Here you go, but for some reason I don’t see the whole update command.
You missed all the detail from the top, so I don't know what version you're running, but tested and it works fine on latest 3.0.
You'll need to update to use Tmp-Integer64-1. The number is too big for 32 bits.
This works here:
update request { Tmp-Integer64-1 := &3GPP-Location-Info }
sql
which, using your supplied packet, results in the db containing:
sqlite> select test from radacct; 10402618446946336 sqlite>
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
1 more question , how would I go about storing it as a hex string in the mysql DB? then I can translate only the part I want using SQL. Thanks
On 23 May 2016, at 12:50 PM, Matthew Newton <mcn4@leicester.ac.uk> wrote:
On Sun, May 22, 2016 at 11:22:46PM +0300, liran kessel wrote:
Here you go, but for some reason I don’t see the whole update command.
You missed all the detail from the top, so I don't know what version you're running, but tested and it works fine on latest 3.0.
You'll need to update to use Tmp-Integer64-1. The number is too big for 32 bits.
This works here:
update request { Tmp-Integer64-1 := &3GPP-Location-Info }
sql
which, using your supplied packet, results in the db containing:
sqlite> select test from radacct; 10402618446946336 sqlite>
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
On Mon, May 23, 2016 at 02:32:50PM +0300, liran kessel wrote:
I have version 2.2.6
but it fails when trying to add Tmp-Integer64-1 (see attached).
OK, you need v3. On Mon, May 23, 2016 at 02:40:48PM +0300, liran kessel wrote:
can i install version 3 on redhat 6.7?
Yes. There is a .spec file in the source so you can build RPMs. On Mon, May 23, 2016 at 02:51:00PM +0300, liran kessel wrote:
1 more question , how would I go about storing it as a hex string in the mysql DB? then I can translate only the part I want using SQL.
Just write the attribute as-is? 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>
participants (2)
-
liran kessel -
Matthew Newton