5.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 7.9 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 7.9.1 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 7.9.2 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}'