4.11.4.1 Writing SQL Accounting Query Templates
Let's suppose you have an accounting table of the following structure:
| | CREATE TABLE calls (
status int(3),
user_name char(32),
event_date_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
nas_ip_address char(17),
nas_port_id int(6),
acct_session_id char(16) DEFAULT '' NOT NULL,
acct_session_time int(11),
acct_input_octets int(11),
acct_output_octets int(11),
connect_term_reason int(4),
framed_ip_address char(17),
called_station_id char(32),
calling_station_id char(32)
);
|
On receiving the Session Start Packet we would insert a record into this
table with status set to 1. At this point the columns
acct_session_time, acct_input_octets,
acct_output_octets as well as connect_term_reason are
unknown, so we will set them to 0:
| | # Query to be used on session start
acct_start_query INSERT INTO calls \
VALUES(%C{Acct-Status-Type},\
'%u',\
'%G',\
'%C{NAS-IP-Address}',\
%C{NAS-Port-Id},\
'%C{Acct-Session-Id}',\
0,\
0,\
0,\
0,\
'%C{Framed-IP-Address}',\
'%C{Called-Station-Id}',\
'%C{Calling-Station-Id}')
|
Then, when the Session Stop Packet request arrives we will look up
the record having status = 1, user_name matching the
value of User-Name attribute, and acct_session_id matching
that of Acct-Session-Id attribute. Once the record is found,
we will update it, setting
| | status = 2
acct_session_time = value of Acct-Session-Time attribute
acct_input_octets = value of Acct-Input-Octets attribute
acct_output_octets = value of Acct-Output-Octets attribute
connect_term_reason = value of Acct-Terminate-Cause attribute
|
Thus, every record with status = 1 will represent the active
session and every record with status = 2 will represent
the finished and correctly closed record. The constructed
acct_stop_query is then:
| | # Query to be used on session end
acct_stop_query UPDATE calls \
SET status=%C{Acct-Status-Type},\
acct_session_time=%C{Acct-Session-Time},\
acct_input_octets=%C{Acct-Input-Octets},\
acct_output_octets=%C{Acct-Output-Octets},\
connect_term_reason=%C{Acct-Terminate-Cause} \
WHERE user_name='%C{User-Name}' \
AND status = 1 \
AND acct_session_id='%C{Acct-Session-Id}'
|
Upon receiving a Keepalive Packet we will update the information
stored with acct_start_query:
| | acct_alive_query UPDATE calls \
SET acct_session_time=%C{Acct-Session-Time},\
acct_input_octets=%C{Acct-Input-Octets},\
acct_output_octets=%C{Acct-Output-Octets},\
framed_ip_address=%C{Framed-IP-Address} \
WHERE user_name='%C{User-Name}' \
AND status = 1 \
AND acct_session_id='%C{Acct-Session-Id}'
|
Further, there may be times when it is necessary to bring some NAS
down. To correctly close the currently active sessions on this NAS
we will define a acct_nasdown_query so that it would
set status column to 2 and update acct_session_time
in all records having status = 1 and
nas_ip_address equal to IP address of the NAS. Thus, all
sessions on a given NAS will be closed correctly when it brought
down. The acct_session_time can be computed as difference
between the current time and the time stored in event_date_time
column:
| | # Query to be used when a NAS goes down, i.e. when it sends
# Accounting-Off packet
acct_nasdown_query UPDATE calls \
SET status=2,\
acct_session_time=unix_timestamp(now())-\
unix_timestamp(event_date_time) \
WHERE status=1 \
AND nas_ip_address='%C{NAS-IP-Address}'
|
We have not covered only one case: when a NAS crashes, e.g. due to
a power failure. In this case it does not have a time to send
Accounting-Off request and all its records remain open. But when
the power supply is restored, the NAS will send an
Accounting On packet, so we define a acct_nasup_query to
set status column to 3 and update acct_session_time
in all open records belonging to this NAS. Thus we will know that
each record having status = 3 represents a crashed session.
The query constructed will be:
| | # Query to be used when a NAS goes up, i.e. when it sends
# Accounting-On packet
acct_nasup_query UPDATE calls \
SET status=3,\
acct_session_time=unix_timestamp(now())-\
unix_timestamp(event_date_time) \
WHERE status=1 \
AND nas_ip_address='%C{NAS-IP-Address}'
|
If you plan to use SQL database for multiple login checking
(see section Multiple Login Checking), you will have to supply at least
two additional queries for retrieving the information about currently
active sessions for a given user and realm (see section Retrieving Session Data). Each of these queries must return a list consisting of
5-element tuples:
| | user-name, nas-ip-address, nas-port-id, acct-session-id
|
For example, in our setup these queries will be:
| | mlc_user_query SELECT user_name,nas_ip_address,\
nas_port_id,acct_session_id \
FROM calls \
WHERE user_name='%C{User-Name}' \
AND status = 1
mlc_realm_query SELECT user_name,nas_ip_address,\
nas_port_id,acct_session_id \
FROM calls \
WHERE realm_name='%C{Realm-Name}'
|
While performing multiple login checking radiusd will
eventually need to close hung records, i.e. such records that are
marked as open in the database (status=1, in our setup), but
are actually not active (See section Verifying Active Sessions, for the
description of why it may be necessary). It will by default use
acct_stop_query for that, but it has a drawback that hung
records will be marked as if they were closed correctly. This may not
be suitable for accounting purposes. The special query
mlc_stop_query is provided to override
acct_stop_query. If we mark hung records with status=4,
then the mlc_stop_query will look as follows:
| | mlc_stop_query UPDATE calls \
SET status=4,\
acct_session_time=unix_timestamp(now())-\
unix_timestamp(event_date_time) \
WHERE user_name='%C{User-Name}' \
AND status = 1 \
AND acct_session_id='%C{Acct-Session-Id}'
|
This document was generated by Sergey Poznyakoff on December, 6 2008 using texi2html 1.78.