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 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: , , , ,

33 responses so far

33 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

  26. # Benon 21 Nov 2016 at 10:42 pm

    I added the stored procedures (No errors) and made the modifications to the stop/update queries in dialup.conf. The update stored procedure appears to insert a stop time and set “Acct-Update” as the terminate cause correctly, though from what I understand, I get an error that shows up when further accounting updates comes in after the first one.

    [sql] Couldn’t update SQL accounting ALIVE record – Duplicate entry ‘1c29266fb359cf2d’ for key ‘acctuniqueid’

    Am I missing something? From what I understand, your stored procedures will make a separate row for each accounting update that comes in. FreeRADIUS will hash a few values from the session to form acctuniqueid, though if the user’s PPPoE session is the same even though the stored procedure has “ended” the session, there will be a conflict with the acctuniqueid column because the session is the same.

    I’m very excited to get this working! If it works, my RADIUS accounting data will be MUCH more useful and accurate.

  27. # samon 27 Jun 2017 at 7:40 am

    Dave, I followed your guide back in 2009 and have implemented the same method on subsequent radius servers, so thanks.

    I am at a point now where I am not sure that this method is best for us, and am researching other methods that achieve the same thing. This is mainly due to >5000 users with 5 minute interim updates, and a massive accounting database, on top of database neglect.

    For my rebuild I have implemented a proxy, buffered accounting, and a separate big box to host the database. This time I will use a trigger to add a new row to a separate table, and a stored procedure to summarise those to hourly and daily.

    The point of this comment is to ask if there are any new ideas or methods out there, being that it’s 8 years later.

    cheers.

  28. # daveon 05 Jul 2017 at 11:37 pm

    Hi Sam,
    This is quite old indeed
    This used to work perfectly with the same numbers on a server from back then.
    You may need to tweak your Mysql (or other) database.
    Have you checked index, engines, global variables (cache, etc…)
    Have you checked where contention is? CPU, IO, swap…

    Some ideas:
    Is archiving accounting from previous years an option?
    I used to make reads on a replicated db, and writes on the master
    Never tried with triggers (see previous comments)
    Do you need interim updates as low as 5mn?

    I haven’t been working on Freeradius for a long time

  29. # starking123engon 08 Jan 2018 at 12:35 pm

    hello
    thank you for your useful tutorial
    but when I apply this steps I have this error in freeradius

    Couldn’t update SQL accounting STOP record – Incorrect integer value: ” for column ‘Acct_Session_Time’ at row 1

  30. # Nathan Ridgeon 13 Jun 2018 at 4:06 am

    digging up an oldie here..

    I have tried to get this working on Freeradius 2.2.5, all seemed to go well per instructions but i noticed straight after restarting freeradius it was adding the interim updates into the radacct table but with acctstoptime to all zeros and the in and out bytes also all zeros

    is there any update for newer freeradius version?

    cheers

  31. # Terry Burtonon 19 Jan 2020 at 2:51 am

    The standard FreeRADIUS radacct schema contains all of the information that is required to generate accurate periodic user data usage data.

    For most purposes it is not necessary to amend the queries to record redundant information in multiple tables since this is generally detrimental to runtime performance. Instead the radacct table can be processed periodically (using a cronjob or the database event scheduler) to capture the data that was used since the last interval. Care must be taken to avoid duplicate counting of session data usage that spans multiple time intervals.

    FreeRADIUS now contains a recommended schema extension and stored procedure for each database to facilitate periodic processing of the standard radacct session data into frequent, regular (or even irregular) time intervals with can further aggregated into buckets of whatever coarse granularity is required for reports. For example, with MySQL:

    https://github.com/FreeRADIUS/freeradius-server/blob/master/raddb/mods-config/sql/main/mysql/process-radacct.sql

    This will be included in FreeRADIUS 3.0.21 but since the new schema and SPs do not interact with the RADIUS server they can be trivially cherry-picked into whatever release you are running.

  32. # Kenon 12 Feb 2020 at 10:10 am

    Any pointers for freeradius 3.0 and above

  33. # daveon 24 Mar 2020 at 10:25 am

    Hi Terry,

    Thanks for pointing out those features which were not available back then.
    The goal was also to keep track of accounting lost following a NAS crash

Comments RSS

Leave a Reply