3.41.11. AcctColumnDef Previous topic Parent topic Child topic Next topic

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:
  • 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. 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-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 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.
  • formatted
    The 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.
  • literal
    Similar to formatted, except that the resulting value is not 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. 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