May 30 2009

Daily Accounting in Freeradius

Published by at 5:50 pm under Cisco,Freeradius




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

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”.
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:

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

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, you're done!


25 responses so far

25 Responses to “Daily Accounting in Freeradius”

  1. Ebay hot itemson 07 Sep 2008 at 11:02 am

    Very interesting site, nice design, greetings

  2. Cason 03 Dec 2008 at 12:33 pm

    Please start the mysql procedures with line:
    DELIMITER //

    and replace last lines with:
    END;
    //

    DELIMITER ;

    to overcome an 1064, syntax error near ‘BIGINT(12)’ in line 26 error.

  3. Tonyon 29 Jan 2009 at 12:28 am

    I’m trying to implement the above with MySQL 5 and Freeradius 2.0.2.
    The MySQL part went well but I’ve encountered and error when trying to start freeradius with the new entries.
    I’m getting:

    including configuration file /usr/local/etc/raddb/sql.conf
    including configuration file /usr/local/etc/raddb/sql/mysql/dialup.conf
    /usr/local/etc/raddb/sql/mysql/dialup.conf[168]: Expecting section start brace ‘{‘ after “CALL acct_update”

    Anyone know why?
    Thanks
    Tony

  4. Ivan Kalikon 05 May 2009 at 3:41 pm

    Because this article is now outdated. It works for Freeradius 1.1.x versions. In 2.x gigaword accounting is enabled by default.

  5. Daveon 30 May 2009 at 5:43 pm

    I’ve updated the script for Freeradius 2.1.
    Minor changes were needed since 1.1.7, not gigawords related though.

    I think above “start brace” error was due to missing backslashes in the end of the lines.

    Dave

  6. Terry Aon 14 Aug 2009 at 9:00 am

    Hi Dave
    Thanks for this if nothing else I can now do procedures in mysql.
    I still get this when I run radiusd -X
    Expecting section start brace ?{? after ?CALL acct_update?
    checked the backslashes all seem to be there

    ALSO
    still had to do this to get the procedure in
    Please start the mysql procedures with line:
    DELIMITER //

    and replace last lines with:
    END;
    //

    DELIMITER ;

    Any ideas re the brace

  7. daveon 25 Aug 2009 at 11:18 pm

    Think I know what you did:
    Replace accounting_update_query meaning replace it with:

    accounting_update_query = ” \
    CALL acct_update( \
    and so on

    You probably skipped the first line
    I get the brace issue to if I remove it

  8. daveon 25 Aug 2009 at 11:25 pm

    I updated the post by the way

  9. Marceloon 30 Aug 2009 at 2:26 am

    Hi,

    Wouldn’t be easier to use triggers instead ?

    I have a WISP and I needed a solution like that a couple months ago and I created another table like user_acct and for every update I triggered a trigger to insert a new row in that table, so I can still have one row per session in the radacct table.

    Just another idea,

    Bye

  10. daveon 30 Aug 2009 at 7:49 pm

    Hi Marcelo,

    You’re right and I did think about the trigger solution but was still in beta version at the time I wrote this

    I wouldn’t say it is easier but a clear advantage is it needs no change in Freeradius. That’s no bother anyway since SQL code in Freeradius isn’t modified much.
    On the other hand, it’s annoying to store twice the same data and it gets confusing to have 2 accounting tables.

    In the end, the 2 solutions let everyone come closer to the right amount of traffic limiting packet loss impact on the network and get a better picture of customer’s usage

    Thanks for this great suggestion
    Dave

  11. Jo Kingon 13 Sep 2009 at 5:37 pm

    Can you please write update version of this article based on FreeRadius 2

  12. daveon 13 Sep 2009 at 6:32 pm

    Hi, what makes you tell this isn’t for FR2?

  13. Brenton 27 Apr 2011 at 8:13 pm

    Marcelo – I know this is an old post, but would you mind providing more info on the trigger you used to update accting records?

  14. Hiteshon 02 Dec 2011 at 2:48 pm

    Hi Dave,

    I m using FR 2.1.7 I am unable to locate accounting_update_query”

    Section in “sql.conf” file.

    Please help

    Thanks
    Hitesh

  15. daveon 06 Dec 2011 at 12:08 am

    See the last line in “sql.conf”: $INCLUDE sql/${database}/dialup.conf
    Database specific conf has moved to sql/$database/dialup.conf
    Look in there

  16. jameson 19 Apr 2012 at 3:20 pm

    Hi, Thanks for the great tips and work youve done!!

    I have a slight issue with the above article and I think it relates to gigawords support within the stored procedures when the procedure is called. I think if the value is above 2 gig or whatever the limit is it causes a negative value to be set in the database.

    Am I right about that or have I made some basic error?

  17. daveon 23 Apr 2012 at 9:53 pm

    That’s right, you need to enable gigawords on your NAS to support larger numbers than 4G
    Check this out: http://www.netexpertise.eu/en/freeradius/gigawords-support.html
    The SQL code should be up to date in Freeradius v2 and on

  18. pouyaon 19 Sep 2012 at 11:42 am

    hi, thank you very much .

  19. paulon 14 Oct 2012 at 4:10 pm

    Hi, thanks for your effort on this it’s exactly what I have been looking for, however I have done everything here but the additional fields didn’t get created in the radacct table and when I create them manually nothing happens. I have checked the procedure has been created but just doesn’t seem to be working.
    I am using a bog standard install of freeradius v2 and have updated the dialup.conf file with the new info and the mysql code for the procedures.

    Any suggestions would be appreciated.
    Thanks

  20. daveon 18 Oct 2012 at 3:10 pm

    Hi Paul,
    Please check your mailbox, I replied back

  21. Warrenon 01 Sep 2014 at 12:30 am

    Great, if you have 20 subscribers and your router(s) push updates every 2 hours.

    But when you have 1000’s of customers, and your routers update every couple minutes, you end up with millions of records a day.

  22. daveon 17 Sep 2014 at 10:07 am

    That’s what databases are meant for…
    I had a few thousands of customers and yes, this did the job very well for years. We’re not talking about big data
    Now, you’re always welcome to suggest new ideas

  23. Eroon 02 Jun 2015 at 3:12 pm

    how can i update the radius server when a user updates his/her internet account information?

  24. Stephenon 11 Apr 2016 at 1:59 pm

    We are getting interim updatesevery 15 however after midnight we get an update that contains the total session time. This is skewing the daily update records and here’s what we see in the DB.

    The longer the session is up the more off our daily results are showing. Would this be due to the calculations? I have a wireshark trace on our NAS today and should see if it’s sending these by tomorrow.

    +——————+——————–+—————–+
    | AcctOutputOctets | acctterminatecause | acctsessiontime |
    +——————+——————–+—————–+
    | 73744115 | Acct-Update | 904 |
    | 33638087939 | Acct-Update | 459345 |
    | 17521727 | Acct-Update | 953 |
    | 14298019 | Acct-Update | 965 |
    | 34874264 | Acct-Update | 902 |
    | 17469562 | Acct-Update | 944 |
    | 34057839 | Acct-Update | 947 |
    | 47435580 | Acct-Update | 933 |
    | 8625323 | Acct-Update | 922 |
    | 7639543 | Acct-Update | 917 |
    | 9428053 | Acct-Update | 909 |
    | 132325519 | Acct-Update | 916 |
    | 27871762 | Acct-Update | 959 |
    | 557683247 | Acct-Update | 951 |
    | 294418532 | Acct-Update | 927 |
    | 220896548 | Acct-Update | 985 |

    Thanks

  25. daveon 15 Apr 2016 at 4:07 pm

    Are these all for the same user?
    The 2nd line looks odd.
    Let me know the wireshark’s results. You should see if they match what’s in your db

Comments RSS

Leave a Reply