Table of Contents

The Radius Accounting tool

The Radius accounting tool is a NAV-tool that collects accounting-data from a Freeradius-server and makes them available to the user through a web-frontend. The goal is to make a more accessible and user-friendly interface to the accounting data instead of having to manually look through logfiles. The frontend is divided into three tabs where you can browse different sets of data from the radius-server.

Use cases

Accounting Log

This tab allows you to search through the accounting log. Here you can specify and limit your search to your needs. You can search for partial matches of strings by using the wildcard-symbol *, see screenshot.

In the first drop-down menu you can tell NAV what type of data you want to search for, default is username, but you can also search for an IP-address/hostname for a NAS (Network Access Server) or IP-address/hostname for a usermachine, the final option IP-range, allows you to search through a range of user IP's by using a search-string in CIDR-format. In the Port Type dropdown menu you can limit your search to the type of the connection the user has made, options are .1x, ISDN, Modem and VPN. Time let's you specify the period to search in or a more exact timestamp to limit your search further. If you mark one or more of the DNS-lookup boxes NAV will attempt to find the hostname belonging to the IP's.

By clicking on one of the hostnames or IP-addresses that turn up in the search result you will start a new search based on that. Clicking on a session ID will transport you to the session-details page.

Accounting Charts

This tab shows a top ten table of the users who have generated the most traffic on the network in the chosen time period. You can choose to see top downloaders, uploaders or a composite. Due to some problems with duplicate sessions this table can not be trusted explicitly to show correct data, but it will give some indication of the traffic.

Error Log

In this tab we can do searches through the error-messages in the Freeradius logfile. You can do searches based on different parts of the message, either the username, client, port or the whole message. Client refers to the NAS the user is connecting to, and port is the module and port-number on that device. To further refine your search, you can choose which type of log-message you are interested in, and you can choose a period or timestamp to get more precise results.

The results will show a timestamp of when an error occurred, the type of the error and the error-message itself. By clicking view you can see a more detailed message-summary.

Installation and configuration

After you have finished the installation/upgrade of NAV, there are a couple of things that must be done manually to make the Radius-module work as intended. See the file INSTALL in the radius-directory for a step by step and updated rundown of the process.

NAS Configuration

Your NAS (Network Access Server) needs to be configured to enable radius-accounting, for Cisco-equipment running IOS you would do:

aaa accounting dot1x default start-stop group radius
radius-server vsa send accounting

Files

Overview of the radius-module

Files to edit

radiusparser.py

Edit these fields:

host    = ""
passwd  = ""
logfile = ""

radiusd.conf

This is a configuration file that you will find on your Freeradius-server. For details see the INSTALL document in the radius-directory.

You will probably want to change

$INCLUDE  ${confdir}/sql.conf

to

$INCLUDE  ${confdir}/postgresql.conf

Add a line that says

sql

last in the accounting{} section.

This small script needs to be defined in the main{} section and called from the preacct{} section before the acct_unique script. See examples in the file for where to put it.

attr_rewrite modify_acctsessionid {
            attribute = Acct-Session-Id
            searchin = packet
            searchfor = "[0-3][0-9]\/[0-3][0-9]\/[0-9]{2}\ [0-2][0-9]\:[0-5][0-9]\:[0-5][0-9]"
            replacewith = ""
            append = no
    }

postgresql.conf

This is also a configuration file that you will find on your Freeradius-server. Below you will find the modified accounting queries, which replaces the standard ones in postgresql.conf.

	#######################################################################
	#  Accounting Queries
	#######################################################################
	# accounting_onoff_query	- query for Accounting On/Off packets 
	# accounting_update_query	- query for Accounting update packets 
	# accounting_update_query_alt	- query for Accounting update packets 
	#                               (alternate in case first query fails)
	# accounting_start_query	- query for Accounting start packets 
	# accounting_start_query_alt	- query for Accounting start packets 
	#                               (alternate in case first query fails)
	# accounting_stop_query		- query for Accounting stop packets 
	# accounting_stop_query_alt	- query for Accounting start packets 
	#                               (alternate in case first query doesn't
	#                                affect any existing rows in the table)
	#######################################################################
	
	accounting_onoff_query = "UPDATE ${acct_table1} \
		SET AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval), \
		AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
		AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}' \
		WHERE AcctSessionTime IS NULL AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= now()"


	accounting_update_query = "UPDATE ${acct_table1} \
                SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
                AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
                AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
                AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint) \
                WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' AND UserName = '%{SQL-User-Name}' \
                AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"

        accounting_update_query_alt = "INSERT into ${acct_table1} \
                (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortType, CiscoNASPort, AcctStartTime, \
                AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
                FramedProtocol, FramedIPAddress) \
                values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
                '%{NAS-Port-Type}', NULLIF('%{Cisco-NAS-Port}', ''), (now() -  '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
                NULLIF('%{Acct-Session-Time}','')::bigint, \
                (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
                (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
                '%{Calling-Station-Id}', '%{Framed-Protocol}', \
                NULLIF('%{Framed-IP-Address}', '')::inet)"

        accounting_start_query = "INSERT into ${acct_table1} \
                (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortType, CiscoNASPort, AcctStartTime, \
                CalledStationId, CallingStationId, FramedProtocol, FramedIPAddress, AcctStartDelay) \
                values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
                '%{NAS-Port-Type}', NULLIF('%{Cisco-NAS-Port}', ''), (now() - '%{Acct-Delay-Time:-0}'::interval), \
                '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Framed-Protocol}', \
                NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}') "

        accounting_start_query_alt  = "UPDATE ${acct_table1} \
                SET AcctStartTime = (now() - '%{Acct-Delay-Time:-0}'::interval), \
                WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' AND UserName = '%{SQL-User-Name}' \
                AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"

        accounting_stop_query = "UPDATE ${acct_table2} \
                SET AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval), \
                AcctSessionTime = NULLIF('%{Acct-Session-Time}', '')::bigint, \
                AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
                AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), \
                AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}', \
                FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet \
                WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' AND UserName = '%{SQL-User-Name}' \
                AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"

        accounting_stop_query_alt = "INSERT into ${acct_table2} \
                (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortType, CiscoNASPort, AcctStartTime, AcctStopTime, \
                AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
                AcctTerminateCause, FramedProtocol, FramedIPAddress, AcctStopDelay) \
                values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
                '%{NAS-Port-Type}', NULLIF('%{Cisco-NAS-Port}', ''), (now() -  '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
                (now() - '%{Acct-Delay-Time:-0}'::interval), NULLIF('%{Acct-Session-Time}', '')::bigint, \
                (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
                (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
                '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Framed-Protocol}', \
                NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}')"

Known Issues

We've had one report where the Freeradius-server crashes when it loses connection with the postgresql-database and can no longer push accounting-messages to it. The reason for this is currently unknown, but it seems to be connected with the rlm_sql and rlm_sql_postgresql modules for freeradius.