====== The Radius Accounting tool ======

{{:tools:radius.png}} The Radius accounting tool is a NAV-tool that collects accounting-data from a [[http://www.freeradius.org/|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 =====

  * If you have an [[http://eduroam.org|eduroam]] wireless network all you wireless users will log in based on their radius credentials. The NAV radius accounting tool is useful for searching eduroam users.
  * If you use IEEE 802.1X in your wired network and thus requires users to log in to get access, the NAV radius accounting tool can track these users as well.
===== 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.

{{:screenshot:radius-acctsearch.png|}}

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:
<code>
aaa accounting dot1x default start-stop group radius
radius-server vsa send accounting
</code>

===== Files =====

{{:radius:radiusoverview1.png?550x700|Overview of the radius-module}}

  * ''radius.py'' - This is the main script, takes care of the communication between database and frontend.
  * ''radiuslib.py'' - Contains assorted helper-functions.
  * ''radius_config.py'' - Just another config-file.
  * ''radiusparser.py'' - Parses the Freeradius logfile ''radius.log'' and inserts data into the database.
  * Various ''.tmpl'' files -  These are Cheetah-template files for the web-frontend, they are not very interesting.
  * ''postgresql.conf'' and ''radiusd.conf'' are config-files for Freeradius.

===== Files to edit =====
=== radiusparser.py === 
Edit these fields:\\
<code>
host    = ""
passwd  = ""
logfile = ""
</code>

=== 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 <code>$INCLUDE  ${confdir}/sql.conf</code> to <code>$INCLUDE  ${confdir}/postgresql.conf</code>

Add a line that says <code>sql</code> 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.
<code>
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
    }
</code>

=== 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''.\\
<code>
	#######################################################################
	#  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}')"

</code>

====== 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.