not logged in | [Login]
A common requirement is to report per-user or global data usage within defined periods, e.g. daily, weekly or monthly.
The standard FreeRADIUS schema for accounting data (the
radacct table) is
keyed by session identifier (
acctuniqueid) with each row representing a
single session, containing (amongst other session data) the session start time
acctstarttime), the time that the last interim update was received
acctupdatetime), the session stop time (
acctstoptime; if it has finished),
and the total inbound and outbound data usage received in the last
interim-update or stop packet (
This format ensures that the accounting data remains compact so that it can be updated in real time with optimal performance. However, this format has the caveat that it is not possible to retrospectively determine in which time period data was consumed for any session that spans multiple time periods.
CAUTION: There exists several guides for configuring FreeRADIUS to report periodic data usage by users. These typically amend the standard queries to write potentially large amounts of additional accounting data, or artificially limit the lifetime of sessions by splitting them so that the start of each reconnected session aligns with the start of the desired reporting interval and does not extend significantly beyond the end of the same interval. This places unnecessary load on both the RADIUS and database servers and may inconvenience a network’s users. There is usually no need to do either of these things.
FreeRADIUS provides a schema extension that implements a recommended approach suitable for most circumstances, which we describe here.
FreeRADIUS only knows the data consumed during each session if the NAS reports this information. A NAS can normally be configured to include total data usage statistics for the session in Accounting Interim-Update (“Alive”) requests and Accounting Stop requests.
If sessions may be long-lived you should enable interim-updates with an
interval that is less than the desired reporting interval. If the NAS does not
already provide interim-update packets then it can normally be configured to do
so, either through its static configuration or by returning an
Acct-Interim-Interval RADIUS attribute in authentication response.
NOTE: If you cannot enable the generation of interim-update packets yet
still require reasonably accurate data usage reporting then you may have no
choice but to set a
Session-Timeout RADIUS attribute dynamically to cause the
NAS to terminate long-running sessions and report their final data usage close
to the start of the reporting period. Alternatively, scripting the generation
of CoA/Disconnect packets for all ongoing sessions in the
radacct table may be
possible, but great care should be taken to not overwhelm a NAS’s limited CPU
resources since disconnected devices will likely be trying to reauthenticate
whilst other are still being disconnected. Either approach to terminating
existing sessions will of course result in an interruption of connectivity for
FreeRADIUS is distributed with a schema extension which introduces a
data_usage_by_period table containing data usage keyed by time-period and
user, and provides the means of “running an update process” to correctly
populate this table, either by calling a stored procedure or running a seperate
Rather than burdening the server with populating per-user, time-period data in
real time, the standard
radacct table is periodically processed to extract the
session data that was updated since the previous update was ran.
Running an update closes the current “accounting period“ allocating data consumed by all sessions that “reported in” during that period (via interim-updates or stops) to the period’s users. The update process additionally creates an open-ended, next accounting period to capture future data usage. It is careful to avoid duplicate counting of data used in sessions that span multiple accounting periods by carrying forward into this next period a negated amount for the data that was already allocated to any earlier periods.
The update process appends a number of rows to the
that is equal to the number of users whose sessions were active within the
current accounting period and therefore makes efficient use of storage.
NOTE: The update process does not have to be called at regular intervals but in production is likely to be invoked either as a cronjob or using the database server’s event scheduler. It can be safely manually invoked (for debugging or ad hoc reporting purposes) as often as necessary outside of the normal schedule without impacting normal usage reporting.
For reporting purposes, the adhoc periods within the
table can be efficiently aggregated to provide data bucketed into the required
Megacorp stores their RADIUS accounting data in a MySQL database using the standard FreeRADIUS schema. They want to provide reports of monthly data usage for given users.
Firstly, implement the extended schema to create the
table to hold the data usage amounts and the
procedure that updates this table:
mysql radius < /etc/raddb/mods-config/sql/main/mysql/process-radacct.sql
NOTE: The above command assumes that passwordless login has been configured via
~/.my.cnf file, or otherwise. This command and subsequent commands
should be adjusted accordingly where this is not the case.
Call the update process manually to create the initial accounting period for the historical data usage and start of the next period for future use:
$ echo “CALL fr_new_data_usage_period()” | mysql radius
After some time, once a few sessions have reported in, call the update process again manually to populate the current period:
$ echo “CALL fr_new_data_usage_period()” | mysql radius
Ensure that data is being correctly populated. The data within the
data_usage_by_period table will look something like this (for a single user):
mysql> SELECT * FROM data_usage_by_period; +----------------------+----------------------+----------+-----------------+------------------+ | period_start | period_end | username | acctinputoctets | acctoutputoctets | +----------------------+----------------------+----------+-----------------+------------------+ ... | 1970-01-01T00:00:00Z | 2020-01-01T12:16:42Z | bob | 1234234324 | 43523453432 | | 2020-01-01T12:16:43Z | 2020-01-01T12:35:20Z | bob | 4342 | 434 | | 2020-01-01T12:35:21Z | NULL | bob | -3456 | -567 | ... +----------------------+----------------------+----------+-----------------+------------------+
1970-01-01 represent the data usage by all sessions
present in the
radacct table when the update process was initial ran. Such rows
can be removed at any time if data for this historic period is not required.
NULL represent the open-ended period for future
acctoutputoctets for these rows will be
zero or negative. These rows must not be removed, otherwise data for ongoing
sessions will be accounted for more than once.
For this example we stated that the required reporting interval is initially
monthly, however it is easy to postulate a future requirement to increase the
granularity of the reports to some as yet unknown interval using the same
historic usage data. We may therefore decide to process the
each day, just after midnight to create fine-grained accounting periods with
intervals that begin at the start of the day. Doing this allows accurate usage
data to be reported for any interval that is some multiple of a day, yet no
CAUTION: If storage requirements and resources allow then the frequency with
radacct table is processed could be increased to hourly or even
more often. However database "partitioning" should be considered for
performance reasons if this would result in an enormous number of rows in the
Next, enable daily processing of the
radacct table by adding something like
the following into the database user’s crontab:
0 12 * * * echo “CALL fr_new_data_usage_period()” | mysql radius
To avoid storing usage data indefinitely a cronjob should be configured to clear out old accounting periods, such as the following:
15 3 * * * echo “DELETE FROM data_usage_by_period WHERE \ period_end < DATE_SUB(CURDATE(), INTERVAL 700 days); \ ” | mysql radius
data_usage_by period table will now be maintained automatically.
To extract the aggregated data usage of a user bucketed in monthly periods the
data_usage_by_period table might be queried as follows:
SELECT DATE_FORMAT(period_start, '%Y-%M') AS month, SUM(acctinputoctets)/1000/1000/1000 AS GB_in, SUM(acctoutputoctets)/1000/1000/1000 AS GB_out FROM data_usage_by_period WHERE username='bob' AND period_end IS NOT NULL GROUP BY YEAR(period_start), MONTH(period_start); +----------------+----------------+-----------------+ | month | GB_in | GB_out | +----------------+----------------+-----------------+ ... | 2019-July | 5.782279230000 | 50.545664820000 | | 2019-August | 4.230543340000 | 48.523096420000 | | 2019-September | 4.847360590000 | 48.631835480000 | | 2019-October | 6.456763250000 | 51.686231930000 | | 2019-November | 6.362537730000 | 52.385710570000 | | 2019-December | 4.301524440000 | 50.762240270000 | | 2020-January | 5.436280540000 | 49.067775280000 | +----------------+----------------+-----------------+
To obtain the overall data usage for all users the restriction on
username=’bob’ can be dropped from the
NOTE: The above procedure will differ between databases but the steps will be
substantially similar. The
process-radacct.sql files contains example queries
for reporting per-user data usage per month for the respective database
In summary, the existing
radacct table can be processed periodically to obtain
all that is required to report periodic user data usage. It is not normally
necessary to amend the tables and queries of the existing schema or to reset
ongoing sessions in order to obtain the necessary information. The collection
and reporting of the additional data required for time-period based data usage
accounting can be performed efficiently out of band.
Last edited by Terry Burton (terryburton), 2020-01-20 23:25:36
Sponsored by Network RADIUS