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 compute 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 arise 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 Freeradius daily accounting 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 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 of Freeradius and Mysql. You need a Mysql version that supports 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 which should be sufficient to get your Freeradius daily accounting. I should mention that this applies when the customer’s connection is established, meaning you need to reset appropriate interfaces if you don’t want to wait for the connections to reset.
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 processssing to be done. Insert into Mysql Radius database these 2 procedures.
DROP PROCEDURE IF EXISTS radius.acct_update;
delimiter //
CREATE PROCEDURE radius.acct_update(
IN S DATETIME,
IN Acct_Session_Time INT(12),
IN Acct_Input_Octets BIGINT(20),
IN Acct_Output_Octets BIGINT(20),
IN Acct_Terminate_Cause VARCHAR(32),
IN Acct_Session_Id varchar(64),
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 VARCHAR(15),
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(20);
DECLARE Prev_Acct_Output_Octets BIGINT(20);
DECLARE Prev_Acct_Session_Time INT(12);
# 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 - Prev_Acct_Input_Octets),
AcctOutputOctets = (Acct_Output_Octets - Prev_Acct_Output_Octets),
AcctTerminateCause = Acct_Terminate_Cause
WHERE AcctSessionId = Acct_Session_Id
AND UserName = SQL_User_Name
AND NASIPAddress = NAS_IP_Address
AND (AcctStopTime IS NULL OR AcctStopTime = 0);
# Create new record
INSERT INTO radacct
(AcctSessionId, AcctUniqueId, UserName,
Realm, NASIPAddress, NASPortId, NASPortType,
AcctStartTime, AcctStopTime, AcctSessionTime,
AcctAuthentic, 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, '0', '0',
Called_Station_Id, Calling_Station_Id, '',
Service_Type, Framed_Protocol, Framed_IP_Address,
'0', '0');
END;
//
delimiter ;
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”.
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:
DROP PROCEDURE IF EXISTS radius.acct_stop;
delimiter //
CREATE PROCEDURE radius.acct_stop(
IN S DATETIME,
IN Acct_Session_Time INT(12),
IN Acct_Input_Octets BIGINT(20),
IN Acct_Output_Octets BIGINT(20),
IN Acct_Terminate_Cause VARCHAR(32),
IN Acct_Delay_Time INT(12),
IN Connect_Info VARCHAR(32),
IN Acct_Session_Id varchar(64),
IN SQL_User_Name VARCHAR(64),
IN NAS_IP_Address VARCHAR(15)
)
BEGIN
DECLARE Prev_Acct_Input_Octets BIGINT(20);
DECLARE Prev_Acct_Output_Octets BIGINT(20);
DECLARE Prev_Acct_Session_Time INT(12);
# 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 - Prev_Acct_Input_Octets),
AcctOutputOctets = (Acct_Output_Octets - 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 IS NULL OR AcctStopTime=0);
END;
//
delimiter ;
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 on Freeradius
The last bit is to replace the SQL code in sql.conf, to call the 2 procedures above.
Replace accounting_update_query with
accounting_update_query = " \
CALL acct_update( \
'%S', \
'%{Acct-Session-Time}', \
'%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \
'%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \
'Acct-Update', \
'%{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
accounting_stop_query = " \
CALL acct_stop( \
'%S', \
'%{Acct-Session-Time}', \
'%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \
'%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \
'%{Acct-Terminate-Cause}', \
'%{%{Acct-Delay-Time}:-0}', \
'%{Connect-Info}', \
'%{Acct-Session-Id}', \
'%{SQL-User-Name}', \
'%{NAS-IP-Address}')"
Reboot the radius server to apply the new settings, and you will now get Freeradius daily accounting records splitting traffic from long sessions!
Tags: Accounting, Cisco, Database, Freeradius, Mysql
