FreeRadius Daily Accounting Large Values PoD Usage Graphs Archive Database Encryption
Valid XHTML 1.0 Strict


Get your projects done with Netexpertise and its partners
English Francais
Netexpertise.eu
 

FreeRadius

Daily Accounting with Freeradius and Mysql

Netexpertise ( contact ). Last updated March 12th 2007.

Introduction

We have seen a few posts on Freeradius user list and other forums asking how to collect accounting periodically. The Radius protocol provides accounting but not in the way that many would like. Here's a short list of things we would like to modify or improve:

  • Traffic is not collected at regular intervals
    As a result, it is impossible to generate daily graphs with download/upload. Traffic will be corrupted because it is again, impossible to calculate traffic over a given period of time with accuracy

  • The active session doesn't appear because the database only contains null values after a start record has been inserted

  • Some records may be lost on the way (between routers and the Radius server). The Radius protocol runs on UDP, which doesn't support acknowledgements.

  • Any usage over 4GB is reset to 0 if the session is not interrupted before then. The format of the field is a 32 bit unsigned value as defined in the RFC.

A number of issues arises for those who want to extract this data and do something out of it. No restrictions can be applied for example. Nor collect values and export them to a billing software. Graphs display nul values if the user hasn't disconnected for a while. Even though there are some ways to send updates from the Network Access Server (NAS), we weren't happy with this as the stop record time remains 00-00-00 and a query might become complicated and time-consuming if previous stop-records were lost on the way. In the best scenario, traffic can be collected (including the active session), but it remains impossible to know your daily traffic accurately for instance.


Method

Some suggested to reset all connections at regular intervals but why should customers be disconnected to collect a fair amount of traffic? How could we justify this for an always-on connection? Some simple modifications can fix these small issues. Let's check briefly what can be done:
Traffic is not collected at regular intervals. We could send updates from the router every so often which is a good step. But that's not sufficient as the new data overwrites the previous amount of traffic. A fair way to proceed is to create a new stop and start record instead from this update. This would minimize the missing last record impact if the time period is small enough. Lost records wouldn't be a problem either if we compare the new value against the last received.
Finally, we can work around the 4GB limit with Acct-Input-Gigawords and Acct-Input-Gigawords Radius extension attributes if your router supports it (Method we are going to use here). If your hardware doesn't support it, a few modifications in the SQL code below would do. It's been tested in a live environment with 3 Radius servers and over 5000 customers. Data is then transferred in to a billing software.


Before You Start

We have applied these changes on Fedora Core and Solaris with Freeradius 1.1.3. The operating system shouldn't really matter as most changes are made on SQL queries. You will need a running setup on Freeradius and Mysql. Scott Barlett has written some precious notes on this, available at http://www.frontios.com/freeradius.html.
Mysql 5.0 or higher is absolutely needed in this implementation. Previous versions don't support stored procedures. Make also sure your router (NAS) supports Radius accounting updates and Acct-Input-Gigawords attributes (usually the case for Cisco).


Adding Support for Radius Extension Attributes

As mentionned in RFC2869, additional attributes were created to answer certain needs through various useful functions. Acct-Input-Gigawords and Acct-Output-Gigawords attributes indicate how many times Acct-Input-Octets and Acct-Output-Octets counters were reset to 0 after reaching 2^32. They are present in Stop and Interim-Update records, which is just what we want.
You can activate this on a Cisco router by entering

aaa accounting gigawords

We don't need to create extra columns in the Radius database; We'll calculate the new amount of traffic on the fly. The benefits are it keeps the original structure of the database and accounting scripts you may have written don't need to be modified. On the other hand, it saves a lot of extra space.
The second modification is done in the SQL query.

Note You will be asked to reload the router to apply the new settings. Do this at a convenient time.


Configuring the NAS to send accounting update

There are 2 ways to achieve this. You either configure your router (NAS) to send regular accounting updates to the Radius server, either add an extra parameter in the customer's details. We'd rather do it on the router as it takes precedence over the Radius parameter. We are using a Cisco router for which the command is:

aaa accounting update periodic 180

This sends an update every 3 hours. I should mention that this applies when the customer's connection is established, meaning you need to reset appropriate interfaces.

Caution Using the aaa accounting update periodic command can cause heavy congestion when many users are logged in to the network.


Defining New Queries for Update and Stop Records

The SQL code for update and stop queries needs to be replaced. We call stored procedures because there's a bit of calculation to be done and Mysql client wouldn't accept 2 statements in one query. There might be a workaround but this is more flexible. A single change doesn't have to be replicated on each server. Insert into Mysql Radius database the 2 procedures below

CREATE PROCEDURE radius.acct_update(
  IN S DATETIME,
  IN Acct_Session_Time INT(12),
  IN Acct_Input_Octets BIGINT(12),
  IN Acct_Output_Octets BIGINT(12),
  IN Acct_Input_Gigawords INT,
  IN Acct_Output_Gigawords INT,
  IN Acct_Terminate_Cause VARCHAR(32),
  IN Acct_Delay_Time INT(12),
  IN Connect_Info VARCHAR(32),
  IN Acct_Session_Id varchar(128),
  IN SQL_User_Name VARCHAR(64),
  IN NAS_IP_Address VARCHAR(15),
  IN Acct_Unique_Session_Id VARCHAR(32),
  IN Realm VARCHAR(64),
  IN NAS_Port INT(12),
  IN NAS_Port_Type VARCHAR(32),
  IN Acct_Authentic VARCHAR(32),
  IN Called_Station_Id VARCHAR(50),
  IN Calling_Station_Id VARCHAR(50),
  IN Service_Type VARCHAR(32),
  IN Framed_Protocol VARCHAR(32),
  IN Framed_IP_Address VARCHAR(15)
)
BEGIN
  DECLARE Prev_Acct_Input_Octets BIGINT(12);
  DECLARE Prev_Acct_Output_Octets BIGINT(12);
  DECLARE Prev_Acct_Session_Time INT(12);
  DECLARE COUNTER_LIMIT BIGINT(12);
  SET COUNTER_LIMIT = POW(2,32);

  # Collect traffic previous values
  SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets), SUM(AcctSessionTime)
    INTO Prev_Acct_Input_Octets, Prev_Acct_Output_Octets, Prev_Acct_Session_Time
    FROM radacct
    WHERE AcctSessionId = Acct_Session_Id
    AND UserName = SQL_User_Name
    AND NASIPAddress = NAS_IP_Address
    AND ( AcctStopTime > 0);

  # Set values to 0 when no previous records
  IF (Prev_Acct_Session_Time IS NULL) THEN
    SET Prev_Acct_Session_Time = 0;
    SET Prev_Acct_Input_Octets = 0;
    SET Prev_Acct_Output_Octets = 0;
  END IF;

  # Update record with new traffic
  UPDATE radacct SET AcctStopTime = S,
    AcctSessionTime = (Acct_Session_Time - Prev_Acct_Session_Time),
    AcctInputOctets = (Acct_Input_Octets + (Acct_Input_Gigawords * COUNTER_LIMIT) - Prev_Acct_Input_Octets),
    AcctOutputOctets = (Acct_Output_Octets + (Acct_Output_Gigawords * COUNTER_LIMIT) - Prev_Acct_Output_Octets),
    AcctTerminateCause = Acct_Terminate_Cause,
    AcctStopDelay = Acct_Delay_Time,
    ConnectInfo_stop = Connect_Info
    WHERE AcctSessionId = Acct_Session_Id
    AND UserName = SQL_User_Name
    AND NASIPAddress = NAS_IP_Address
    AND AcctStopTime = 0;

  # Create new record
  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)
  VALUES
   (Acct_Session_Id, Acct_Unique_Session_Id, SQL_User_Name, Realm, NAS_IP_Address, NAS_Port, NAS_Port_Type,
    S, '0', '0', Acct_Authentic, Connect_Info, '', '0', '0', Called_Station_Id, Calling_Station_Id, '',
    Service_Type, Framed_Protocol, Framed_IP_Address, Acct_Delay_Time, '0');
END

Note You need to update the table names if they have been changed in sql.conf. We use the default value from the file and database structure "Radacct".
Note Don't forget to redefine the delimiter before and after the procedure or you'll get an error!
We need to retrieve the traffic from previous updates because the router sends a counter and not the amount of traffic sent during that period of time. A stop record is then created with the traffic difference. The stop query needs to be modified as well:

CREATE PROCEDURE radius.acct_stop(
  IN S DATETIME,
  IN Acct_Session_Time INT(12),
  IN Acct_Input_Octets BIGINT(12),
  IN Acct_Output_Octets BIGINT(12),
  IN Acct_Input_Gigawords INT,
  IN Acct_Output_Gigawords INT,
  IN Acct_Terminate_Cause VARCHAR(32),
  IN Acct_Delay_Time INT(12),
  IN Connect_Info VARCHAR(32),
  IN Acct_Session_Id varchar(128),
  IN SQL_User_Name VARCHAR(64),
  IN NAS_IP_Address VARCHAR(15)
)
BEGIN
  DECLARE Prev_Acct_Input_Octets BIGINT(12);
  DECLARE Prev_Acct_Output_Octets BIGINT(12);
  DECLARE Prev_Acct_Session_Time INT(12);
  DECLARE COUNTER_LIMIT BIGINT(12);
  SET COUNTER_LIMIT = POW(2,32);

  # Collect traffic previous values
  SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets), SUM(AcctSessionTime)
    INTO Prev_Acct_Input_Octets, Prev_Acct_Output_Octets, Prev_Acct_Session_Time
    FROM radacct
    WHERE AcctSessionId = Acct_Session_Id
    AND UserName = SQL_User_Name
    AND NASIPAddress = NAS_IP_Address
    AND ( AcctStopTime > 0);

  # Set values to 0 when no previous records
  IF (Prev_Acct_Session_Time IS NULL) THEN
    SET Prev_Acct_Session_Time = 0;
    SET Prev_Acct_Input_Octets = 0;
    SET Prev_Acct_Output_Octets = 0;
  END IF;

  # Update record with new traffic
  UPDATE radacct SET AcctStopTime = S,
    AcctSessionTime = (Acct_Session_Time - Prev_Acct_Session_Time),
    AcctInputOctets = (Acct_Input_Octets + (Acct_Input_Gigawords * COUNTER_LIMIT) - Prev_Acct_Input_Octets),
    AcctOutputOctets = (Acct_Output_Octets + (Acct_Output_Gigawords * COUNTER_LIMIT) - Prev_Acct_Output_Octets),
    AcctTerminateCause = Acct_Terminate_Cause,
    AcctStopDelay = Acct_Delay_Time,
    ConnectInfo_stop = Connect_Info
    WHERE AcctSessionId = Acct_Session_Id
    AND UserName = SQL_User_Name
    AND NASIPAddress = NAS_IP_Address
    AND AcctStopTime = 0;
END

This is the same as the original query except that it retrieves the previous traffic again. If you don't use accounting updates, this will do the same as before.


Updating sql.conf

The last bit is to replace the SQL code in sql.conf, to call the 2 procedures above. Replace accounting_update_query with

CALL acct_update(
  '%S', 
  '%{Acct-Session-Time}',
  '%{Acct-Input-Octets}',
  '%{Acct-Output-Octets}',
  '%{Acct-Input-Gigawords}',
  '%{Acct-Output-Gigawords}',
  'Acct-Update',
  '%{Acct-Delay-Time}',
  '%{Connect-Info}',
  '%{Acct-Session-Id}',
  '%{SQL-User-Name}',
  '%{NAS-IP-Address}',
  '%{Acct-Unique-Session-Id}',
  '%{Realm}',
  '%{NAS-Port}',
  '%{NAS-Port-Type}',
  '%{Acct-Authentic}',
  '%{Called-Station-Id}',
  '%{Calling-Station-Id}',
  '%{Service-Type}',
  '%{Framed-Protocol}',
  '%{Framed-IP-Address}')
and accounting_stop_query with 
CALL acct_stop(
  '%S',
  '%{Acct-Session-Time}',
  '%{Acct-Input-Octets}',
  '%{Acct-Output-Octets}',
  '%{Acct-Input-Gigawords}',
  '%{Acct-Output-Gigawords}',
  '%{Acct-Terminate-Cause}',
  '%{Acct-Delay-Time}',
  '%{Connect-Info}',
  '%{Acct-Session-Id}',
  '%{SQL-User-Name}',
  '%{NAS-IP-Address}')

Reboot the radius server to apply the new settings, you're done!
If you have any comment or want to share some suggestions you may have, feel free to contact us.

Netexpertise.eu (c)