Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

Navigation  Back up to About Overview 
  
[+] Expand All
[-] Collapse All

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 156: 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

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

[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:

[Type]
1=user
2=user
3=user
7=nas
8=nas
639=nas
28=nas

[Type/user]
SQL=INSERT INTO usagelog \
(Time, NASAddress, SessionID, \
Type, Name, BytesIn, BytesOut) \
VALUES \
(%TransactionTime/t, %NASAddress, \
@Acct-Session-Id, @Acct-Status-Type, \
%FullName/40s, @Acct-Input-Octets, \
@Acct-Output-Octets)
[Type/nas]
SQL=INSERT INTO …

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

Table 157: 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

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

Table 158: *.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

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:

[TypeNames]TypeID=TypeNameTypeID=TypeName...

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

[TypeNames]
1=Start
2=Stop
3=Interim
7=On
8=Off
639=AscendType
28=3ComType

Example: SQL Accounting Configuration File

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

[Bootstrap]
Enable=0
LibraryName=radsql_acct_ora.so
InitializationString=SQL-ORACLE-ACCT

[Settings]
Connect=dbusername/dBpassword@servicename
ParameterMarker=?
MaxConcurrent=1
ConcurrentTimeout=30
WaitReconnect=2
MaxWaitReconnect=360
ShutdownTimeout=360
ErrorMap=oracle.ini
LogLevel=0

[Server]
s1=2
s2=2

[Server/s1]
Connect=admin1/passwd1@mydb1

[Server/s2]
Connect=admin2/passwd2@mydb2

[Type]
1=User
2=User
3=User

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

Modified: 2016-11-15