Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 

SQL Accounting

 

This section describes the configuration file that configures SQL accounting in Steel-Belted Radius Carrier. The configuration files reside in the radiusdir directory.

The configuration file used to configure SQL accounting methods must have a .acc extension: for example, radsql.acc. The format of a configuration file is comparable to that of a Windows INI file: composed of several sections; section names are enclosed in brackets; each section may contain multiple parameter/value pairs.

Table 151: SQL Accounting Configuration File

File

Function

radsql.acc

Configures Oracle SQL accounting for Steel-Belted Radius Carrier.

radsqljdbc.acc

Configures JDBC SQL accounting for Steel-Belted Radius Carrier.

[Settings] Section

[Settings] Section

The [Settings] section of the SQL accounting configuration file defines parameters that control the database connection.

[Type] Section

[Type] Section

Each entry in the [Type] section of the SQL accounting configuration file maps an AcctStatusType attribute value to a statement name that you may assign arbitrarily. The statement name is then used to look up another section in the configuration file that describes that statement. The secondary section names are composed as [Type/ statement ], where statement is the arbitrarily assigned name for the statement.

For example, to perform separate accounting updates for network access server and user activity, you might provide the following [Type] and [Type/ statement] sections:

Note the numeric values used in the preceding [Type] section. The AcctStatusType values 1, 2, 3, 7, and 8 have been reserved by the RADIUS accounting standard with names and meanings, as described in Table 152.

Table 152: Acct-Status-Type Values

Acct-Status-Type Value

Name

Meaning

1

Start

A user session has started.

2

Stop

A user session has stopped, request contains final statistics.

3

Interim

A user session is in progress, request contains current statistics.

7

Accounting-On

The network access server has started.

8

Accounting-Off

The network access server is about to shut down.

Additional values for Acct-Status-Type have been defined by network access server vendors for use with their equipment. These vendor-specific values may also be listed in the [Type] section.

[Type/statement] Sections

[Type/statement] Sections

Table 153 lists the parameters that may be present in a [Type/ statement ] section of the SQL accounting configuration file.

Table 153: *.acc [Type|statement] Syntax

Parameter

Function

SQL

Specifies the exact SQL statement used to update the SQL database with accounting information. The SQL statement may be broken over several lines by ending each line with a backslash. The backslash must be preceded by a space character, and followed by a newline. The subsequent lines may be indented for better readability.

The general syntax of the SQL statement is as follows:

%<item>/[size][.digits][type][&][!direction]

Or

@<attribute>/[size][.digits][type][&][!direction]

Where, <> indicates required fields and [] indicates optional fields.

For example:

SQL=INSERT INTO accounting\
(TransTime, FullName, Authenticator, NASName, \
NASAddress, Type, PacketsIn, PacketsOut) \
VALUES (%TransactionTime/t, %FullName/40s, \
%AuthType/40s, %NASName/40s, %NASAddress, \
%Type, @Acct-Input-Packets/n, \
@Acct-Output-Packets/n)

Note: Include the /t (timestamp) data type qualifier with the %TransactionTime argument in SQL statements. If you do not, the %TransactionTime output is formatted as character, with differing results on JDBC and Oracle.

See SQL Parameter for more information.

MaxConcurrent

If present, MaxConcurrent overrides the value of MaxConcurrent specified in the [Settings] section for this particular statement.

Note: The MaxConcurrent parameter is valid only for Oracle SQL accounting (radsql.acc). It is not valid for JDBC SQL accounting (radsqljdbc.acc).

Note: A setting of MaxConcurrent = 1 is sufficient for all but the most demanding environments. Increase this value slowly and conservatively. For more information about executing overlapping SQL statements, see the SBR Carrier Administration and Configuration Guide.

ConcurrentTimeout

If present, ConcurrentTimeout overrides the value of ConcurrentTimeout specified in the [Settings] section for this particular statement.

[TypeNames] Section

[TypeNames] Section

Each entry in the [TypeNames] section of the SQL accounting configuration file maps an AcctStatusType attribute value to a string. If a %Type parameter is present in the corresponding SQL statement, this %Type parameter contains the given string.

If no string is given for a particular AcctStatusType, when an accounting request of that type is received, %Type is set to the numeric value of the AcctStatusType attribute, formatted as a string.

The syntax for the [TypeNames] section is:

You can include RADIUS standard and vendor-specific accounting packet types; for example:

Example: SQL Accounting Configuration File

Example: SQL Accounting Configuration File

This section provides an example of an Oracle radsql.acc configuration file.