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 Type names are
recognised:
integerThe insertion is done as an integer
data type. RADIUS attributes that have VALUE names defined are
inserted as their integer RADIUS value.
integer-dateThe attribute value is converted
from Unix seconds to an SQL datetime string using the date formatting
characters. For more information, see
Section 3.4. Date formatting. 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 Microsoft SQL and Sybase datetime columns. If it is not suitable
for your database, consider defining your own
DateFormat parameter for this AuthBy SQL. The
resulting value is quoted after conversion.
formatted-dateformatted-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 Perl
TimeDate module
Date::Format
according to the format string.
TimeDate is
available from CPAN. For more information, see
Section 2.1.2. CPAN. 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. The resulting value is not
quoted after conversion.
formattedThe attribute field is processed
looking for the special characters described in
Section 3.3. Special formatters. 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.
literalSimilar to formatted, except that the
resulting value is not quoted.
inet_atonConverts 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. The resulting value is
quoted.
You can use formatted-date to create date
formats to suit your SQL database. This example inserts the Timestamp into
an Oracle DATE or TIMESTAMP type column called TIME_STAMP:
AcctColumnDef TIME_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 value of
Acct-Delay-Time attribute (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
# Insert date-time without and with seconds
DateFormat %Y-%m-%d %H:%M
AcctColumnDef DATE_TIME,Timestamp, integer-date
AcctColumnDef DATE_TIME_SEC,Timestamp, integer-date, %Y-%m-%d %H:%M:%S
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
