PerlStalker's SysAdmin Notes and Tools

Printer Friendly

SQL Accounting

Introduction

RADIUS accounting provides all sorts of good information but the default accounting format, detail files, are hard to work with. Storing the accounting information in a database makes it a lot easier to work with.

Database Setup

gnu-radius allows a great deal of flexability in your database server and schema. FreeRadius has devoloped a database schema that some are beginning to accept as a de facto default. I didn't use it but may want to.

Note: RADIUS accounting is a write intensive process. You will need to adjust your database settings accordingly. For example, MySQL prior to version 4.0 used MyISAM as the default database format. MyISAM uses table level locking which can lead to deadlock under load. You can still use MySQL 3.23 but you need to use InnoDB types.

Database Schema

The following is the database schema I used on my MySQL server. This is taken basically from the gnu-radius docs.

Radius_users

Radius_groups

Radius_attribs

Radius_sessions

Note: This is the write intensive table.

gnu-radius Configuration

users File

Users who are authenticated out of the DB need to have Auth-Type set to SQL.

DEFAULT Auth-Type = SQL, Simultaneous-Use = 1
        Service-Type = Framed-User,
        Framed-Protocol = PPP,
        Framed-IP-Netmask = 255.255.255.255,
        Framed-MTU = 576,
        Idle-Timeout = 1800,
        Session-Timeout = 43200

sqlserver

The file sqlserver controls access to the SQL server.

interface mysql

server localhost
port 3306
login user
password pass

keepopen yes

idle_timeout 14400

doacct yes

acct_db Accounts

# Query to be used on session start
acct_start_query	INSERT INTO Radius_sessions \
			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}' \
				)

# Query to be used on session end
acct_stop_query	UPDATE	Radius_sessions \
		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 username='%C{User-Name}' \
		    AND status = 1 \
		    AND acct_session_id='%C{Acct-Session-Id}'

# Query to be used on receiving a keepalive record
acct_alive_query     UPDATE Radius_sessions \
                     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}'

# Query to be used when a NAS goes down, i.e. when it sends 
# Accounting-Off packet
acct_nasdown_query   UPDATE Radius_sessions \
                     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}'


# Query to be used when a NAS goes up, i.e. when it sends 
acct_nasup_query   UPDATE Radius_sessions \
                   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}'

Useful Scripts

• ruser