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}'

Go to the first, previous, next, last section, table of contents.