AcctColumnDef is used to define which attributes in accounting
requests are inserted into AccountingTable. It also specifies which column
they are inserted into, and optionally the data type of that column. The
general form is:
AcctColumnDef Column,Attribute[,Type][,Format]
Column
is the name of the SQL column where the data is inserted. Attribute is the
name of the RADIUS attribute to store there. Type is an optional data type
specifier, which specifies the data type of the SQL column. Format is an
optional format string that can be used to format the value. Columns and
their values are included in accounting SQL statements in alphabetical
order by column name.
The following types are recognised:
- integer
The insertion is done as an integer data type. RADIUS
attributes that have VALUE names defined are inserted as their integer
RADIUS value.
- integer-date
The attribute value is converted from Unix seconds
to an SQL datetime string using the date formatting characters. For
more information, see
Section 3.4.The Format field is
used as the date format (if it is present), otherwise the standard
DateFormat parameter for this AuthBy SQL is used (which defaults to
the format 'Sep 3, 1995 13:37'). This is useful for inserting the
Timestamp attribute as an SQL datetime type. The default is compatible
with at least MySQL, Microsoft SQL and Sybase datetime columns. If it
is not suitable for your database, consider defining your own
DateFormat parameter for this AuthBy SQL.
- formatted-date
formatted-date is now deprecated, and new
installations should use integer-date instead. It has a much wider
range of formatting and date options.
The attribute is converted
by Date::Format according to the format string. The Perl TimeDate
package is part of CPAN. For more information, see
Section 2.1.2. It is most useful
for SQL databases with unusual date formats, like Oracle.
formatted-date is now only provided for historical reasons, and new
installations should probably use integer- date in conjunction with
DateFormat instead.
- formatted
The attribute field is processed looking for the
special characters described in
Section 3.3. If the resulting
string is empty it is not inserted. This is useful for inserting data
from other places besides the current request, such as a GlobalVar you
have defined elsewhere, or from a data item that the previous AuthBy
put in the current reply packet. The resulting data is quoted. See
literal below to generate unquoted data
- literal
Similar to formatted, except that the resulting field is
be quoted.
- inet_aton
Converts a dotted quad IP address (such as 10.1.1.5)
to a 32 bit unsigned integer.
- Anything else
Any other type string causes the named RADIUS
attribute to be inserted literally as a string. Quotes and other
control characters are automatically escaped to suit your
database.
You can use formatted-date to create date formats to suit your
SQL database. This example inserts the Timestamp into an Oracle date type
column called TIME_STAMP:
AcctColumnDefTIME_STAMP,Timestamp,formatted-date,to_date\
('%e %m %Y %H:%M:%S', 'DD MM YYYY HH24:MI:SS')
The insert
statement is this:
insert into ACCOUNTING(TIME_STAMP, ......) values
(to_date('16 02 1999 16:40:02', 'DD MM YYYY HH24:MI:SS'), ....)
For
types other than formatted-date and integer-date, the format field can be
used to build custom values in your insert statement. This can be very
useful to call SQL conversion functions on your data. If you specify a
format, it is used as a sprintf-style format, where %s is replaced by your
value.
If any named attribute is not present in the accounting
request, nothing is inserted in the column for that value. The attribute
doe not appear in the insert statement at all, and the SQL server's
default value (usually NULL) is used for that column. With some SQL
servers, you can change the default value to be used when a column is not
specified in an insert statement.
You can have 0 or more
AcctColumnDef lines, one for each attribute you want to store in the
accounting table. If there are no AcctColumnDef lines, then the accounting
table is not updated.
The attribute Timestamp is always available
for insertion, and is set to the time the packet was received, adjusted by
Acct-Delay-Time (if present), as an integer number of seconds since
midnight Jan 1, 1970 UTC. The Timestamp attribute is added by Radiator to
all received Accounting requests, and is set to the current time according
to the host on which the Radiator is running.
Here is an example
column configuration:
AcctColumnDef USERNAME,User-Name
AcctColumnDef TIME_STAMP,Timestamp,integer
AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
AcctColumnDef ACCTINPUTOCT,Acct-Input-Octets,integer
AcctColumnDef ACCTOUTPUTOCT,Acct-Output-Octets,integer
AcctColumnDef ACCTSESSIONID,Acct-Session-Id
AcctColumnDef ACCTSESSTIME,Acct-Session-Time,integer
AcctColumnDef ACCTTERMINATECAUSE,Acct_Terminate-Cause
AcctColumnDef NASIDENTIFIER,NAS-Identifier
AcctColumnDef NASPORT,NAS-Port,integer
Note
If your accounting
table inserts are not working, run Radiator at a trace level of 4, and you
see each insert statement logged before it is executed. This helps you
determine if your AcctColumnDef lines are correct.
Note
If there
are multiple definitions for the same column with non-null values, the
last one in the configuration file is used.
Note
SQL table and
column names are generally case sensitive, and usually can consist only of
letters, digits or the underscore character ‘_’.
Note
You can
further customise the accounting insert query with
AcctInsertQuery.
Note
The ‘formatted’ type is useful for inserting
values set up in GlobalVars, or to get values from the current reply
(possibly put there by a preceding AuthBy).
AcctColumnDef ACCOUNTTYPE,%{Reply:accounttype},formatted
AcctColumnDef SERVERNAME,%{GlobalVar:servername},formatted
Note
You
can get SQL to calculate the start time of an accounting packet with
something like:
AcctColumnDef START_TIME,%b-0%{Acct-Session-Time},literal
