Download This Guide
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:
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)
