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 Statement Construction

For each accounting request whose Acct-Status-Type is mapped to a SQL statement, that accounting request is logged to the back-end database by executing the associated SQL statement.

While a sample SQL statement is provided in the original configuration file, you must configure one or more SQL entries of the configuration file with a statement appropriate to your database. Each SQL statement is typically an INSERT INTO statement and may contain additional syntax elements that are preprocessed by the SQL accounting module.

The SQL accounting module executes SQL statements in parameterized form. This means that the SQL statement is compiled once, with parameter markers (usually question marks) as placeholders for data items that vary from one execution to the next. Only upon execution of the statement are the actual data values supplied.

The SQL statement you compose must not include parameter markers directly. Instead, the names of the parameters should be included where parameter markers would appear, in a format described below. The SQL authentication module translates the SQL statement provided, replacing parameter names with parameter markers before passing the SQL statement to the database engine.

A SQL statement can be very simple. Basically, all that is required is to set fields of the database record with values from the request. The SQL statement can also be quite complex; it can include inner joins, and it can contain expressions. The underlying database engine is responsible for handling the SQL statement; The SQL accounting module performs no interpretation of the SQL statement other than to translate parameter names to parameter markers.

INSERT Statement and VALUES Section

The following example shows a SQL INSERT statement that might be found in a Steel-Belted Radius Carrier .acc file:

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)

In the VALUES section, the names (between parentheses) represent the values inserted into the SQL table columns. To support the SQL accounting module, each item in the VALUES section must be prefixed with a @ sign or a % sign.

  • @ indicates a RADIUS accounting attribute. The attribute name must also be listed in the account.ini file. This remains true even if the account.ini file is disabled.
  • % indicates an item associated with the INSERT request that is not a RADIUS accounting attribute. Table 50 lists the Steel-Belted Radius Carrier items that may be provided.

    Table 50: Insert Statement Syntax

    Item

    Data Type

    Meaning

    %TransactionTime

    Time

    The date/time that the event occurred that is the subject of the request.

    Note: You should 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.

    %Time

    Time

    The date/time when the request is being processed. (This is later than %TransactionTime if the request is a retry.)

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

    %Type

    String

    The RADIUS accounting request type.

    %NASAddress

    IP address

    The IP address of the requesting NAS.

    %NASName

    String

    The name of the network access server that originated the request. This may be the name of the RADIUS client entry in the database or the value of the NAS-Identifier or NAS-IP-Address attribute.

    %NASModel

    String

    The NAS make/model.

    %FullName

    String

    The full name of the logged in user.

    %AuthType

    String

    The method by which the user was authenticated.

    %RADIUSClientName

    String

    The name of the network access server, as specified in a RADIUS client entry in the Steel-Belted Radius Carrier database.

A format specifier may appear immediately following each parameter. The format specifier should describe the database storage format of the column that corresponds to the parameter. It consists of a slash (/), possibly a length, and a data type. Table 51 lists the available data types.

Table 51: Data Types

Format Specifier

Meaning

/xs

A text string of length x.

/s indicates a string with the default length of 256.

/xb

A binary data string of length x. A binary string is different from a text string in that it is not NULL-terminated and is not restricted to ASCII characters.

/b indicates a binary data string with the default length of 256.

/n

32-bit integer

/n8

8-bit integer

/n16

16-bit integer

/n32

32-bit integer (same as /n)

/nxx

Integer xx bits in length. For example, /n64 indicates a number with a length of 64 bits.

/t

Timestamp

Note: Steel-Belted Radius Carrier supports integers larger than 32 bits by manipulating them as binary data strings. The Solaris Oracle 8 plug-ins are able to convert binary data strings between Oracle VARRAW types (/xb) and Oracle NUMBER types (/n). Oracle types must be declared with enough precision to avoid truncation when inserting into the database, and care must also be taken to avoid truncation when retrieving from the database. In particular, avoid retrieving Oracle VARRAW types larger than 256 bytes. Other database/operating-system combinations may not allow for integers larger than 32 bits.

If a format specifier is not present in the SQL statement syntax, Steel-Belted Radius Carrier automatically defaults to an appropriate specifier based on the actual parameter type. For example, @Acct-Input-Octets is a number, and defaults to /n.

Note: For strings, always include a format specifier, and be sure to specify a length no greater than the actual field size in the database. The compilation of the SQL statement may fail if a length greater than the actual field size is specified. If no format specifier is present, the length defaults to 256 characters, which may cause the compilation to fail.

Steel-Belted Radius Carrier automatically attempts to convert between the internal format of a parameter and its format in the database, as described by the format specifier. In most cases, the formats are equivalent; if not, Steel-Belted Radius Carrier performs reasonable conversions.

Table 52 lists the internal formats and their compatible database formats:

Table 52: Internal Formats and Compatible Database Formats

Internal Format

Compatible Database Formats

Binary data string

/b, /xb, /n, /n8, /n16, /n32

Number

/n, /n8, /n16, /n32, /xs, /s

String

/xs, /s

Time (seconds since 1/1/70)

/t, /n, /n32, /xs, /s

IP address

/n, /n32, /xs, /s

As you write the INSERT statement for your SQL accounting configuration file (.acc), we recommend the following syntax check list:

  • The column names and their corresponding attributes in the VALUES section are order-dependent. In the example, the %TransactionTime/t value would be inserted into the Time column (and formatted as a timestamp), the %NASAddress value would be inserted into the NASAddress column, and so forth. The ordering of these settings is critical to proper RADIUS accounting data insertion, since each column in the SQL table may be a specific data type, such as varchar or int.
  • The use of left and right parentheses ( ), the backslash \, the forward slash / and even blank spaces are all extremely important and must be exact. You can add as many columns and attributes as you want for your RADIUS accounting needs; however, be sure to model your INSERT statement syntax as shown in example.
  • An attribute listed incorrectly in the VALUES section, such as @Acct_Session-Id rather than @Acct-Session-Id, causes the SQL statement to fail during a RADIUS accounting transaction. The attribute’s syntax must match its corresponding attribute name in the account.ini file, which in turn matches the attribute’s name in the appropriate dictionary file, which allows Steel-Belted Radius Carrier to process the attribute correctly when it is received from the NAS (the RADIUS client).
  • An attribute listed in the VALUES section that is missing its prefix of @ or % causes the SQL statement to fail during a RADIUS accounting transaction.
  • If a carriage return is present within the INSERT statement without the backslash \ to indicate the end of the line, the SQL statement fails during a RADIUS accounting transaction.
  • Do not make the lines in the .acc file too long. There is a line length limit of 255 characters. Use the backslash \ to indicate the end of the line before that limit is reached. If a line exceeds this limit, the SQL statement fails during a RADIUS accounting transaction.

Using Multiple SQL Statements

The most common use of accounting is to track user sessions. However, accounting requests are generated when the NAS starts up and shuts down; and, vendor-specific uses of accounting are used to track other NAS phenomena. Clearly, it might be advisable to log different types of accounting events to different tables.

The Acct-Status-Type attribute of an accounting request indicates the request type. You may, if you like, create multiple SQL statements, and map each Acct-Status-Type to one of these SQL statements. The different statements may update different tables in the database, but they all share the single database connection.

Overlapped Execution of SQL Statements

The SQL accounting module is multi-threaded. SQL accounting can be configured with a maximum number of simultaneous executions of any SQL statement, using the MaxConcurrent entry in the .acc file’s [Settings] section.

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

If MaxConcurrent is set to 1, SQL execution occurs serially, and the SQL execution for each accounting request must complete before execution for the next request may begin.

By increasing MaxConcurrent, it may be possible to increase throughput by overlapping operations, especially if the database server is remote and a large part of the time to complete a statement execution is taken up by network latency. If the database server is local, the point of diminishing returns may be reached at a small value of MaxConcurrent, possibly even at 1 or 2. You can find the optimum value for your system by experimentation.

Note: A setting of MaxConcurrent = 1 should be sufficient for all but the most demanding environments. Increase this value slowly and conservatively.

MaxConcurrent determines the maximum overlap for executing any single SQL statement. Multiple SQL statements for different request types are not interdependent, and executions of one statement do not affect executions of a different statement.

You might expect that databases that are licensed by number of connections would debit a single connection regardless of how many SQL statements are active. This is not necessarily the case; some databases count each open compiled SQL statement against the licensed number of connections. The database license may also have an influence on the optimum setting for MaxConcurrent.

Modified: 2017-03-07