accounting { reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}" # Write SQL queries to a logfile. This is potentially useful for bulk inserts # when used with the rlm_sql_null driver. # logfile = ${logdir}/accounting.sql column_list = "\ AcctSessionId, AcctUniqueId, UserName, \ Realm, NASIPAddress, NASPortId, \ NASPortType, AcctStartTime, AcctUpdateTime, \ AcctStopTime, AcctSessionTime, AcctAuthentic, \ ConnectInfoStart, ConnectInfoStop, AcctInputOctets, \ AcctOutputOctets, CalledStationId, CallingStationId, \ AcctTerminateCause, ServiceType, FramedProtocol, \ FramedIPAddress, FramedIPv6Address, FramedIPv6Prefix, \ FramedInterfaceId, DelegatedIPv6Prefix" type { accounting-on { # # Bulk terminate all sessions associated with a given NAS # query = "\ UPDATE ${....acct_table1} \ SET \ AcctStopTime = '%S', \ AcctSessionTime = DATEDIFF(second, '%S', AcctStartTime), \ AcctTerminateCause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \ WHERE AcctStopTime IS NULL \ AND NASIPAddress = '%{NAS-IP-Address}' \ AND AcctStartTime <= '%S'" } accounting-off { query = "${..accounting-on.query}" } start { # # Insert a new record into the sessions table # query = "\ INSERT INTO ${....acct_table1} \ (${...column_list}) \ VALUES \ ('%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{NAS-IP-Address}', \ '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ '%{NAS-Port-Type}', \ '%S', \ '%S', \ NULL, \ '0', \ '%{Acct-Authentic}', \ '%{Connect-Info}', \ '', \ '0', \ '0', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}', \ '%{Framed-IPv6-Address}', \ '%{Framed-IPv6-Prefix}', \ '%{Framed-Interface-Id}', \ '%{Delegated-IPv6-Prefix}')" # # Key constraints prevented us from inserting a new session, # use the alternate query to update an existing session. # query = "\ UPDATE ${....acct_table1} SET \ AcctStartTime = '%S', \ AcctUpdateTime = '%S', \ ConnectInfoStart = '%{Connect-Info}' \ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'" } interim-update { # # Update an existing session and calculate the interval # between the last data we received for the session and this # update. This can be used to find stale sessions. # query = "\ UPDATE ${....acct_table1} \ SET \ AcctUpdateTime = '%S', \ AcctStopTime = NULL, \ FramedIPAddress = '%{Framed-IP-Address}', \ fFramedIPv6Address = '%{Framed-IPv6-Address}', \ FramedIPv6Prefix = '%{Framed-IPv6-Prefix}', \ FramedInterfaceId = '%{Framed-Interface-Id}', \ DelegatedIPv6Prefix = '%{Delegated-IPv6-Prefix}', \ AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, \ AcctInputOctets = convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \ AcctOutputOctets = convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}' \ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'" # # The update condition matched no existing sessions. Use # the values provided in the update to create a new session. # query = "\ INSERT INTO ${....acct_table1} \ (${...column_list}) \ VALUES \ ('%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{NAS-IP-Address}', \ '%{%{NAS-Port-Id}:-%{NAS-Port}}', \ '%{NAS-Port-Type}', \ DATEADD(second, -1 * (%{%{Acct-Session-Time}:-0}),'%S'), \ '%S', \ NULL, \ %{%{Acct-Session-Time}:-NULL}, \ '%{Acct-Authentic}', \ '%{Connect-Info}', \ '', \ convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \ convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}', \ '%{Framed-IPv6-Address}', \ '%{Framed-IPv6-Prefix}', \ '%{Framed-Interface-Id}', \ '%{Delegated-IPv6-Prefix}')" } stop { # # Session has terminated, update the stop time and statistics. # query = "\ UPDATE ${....acct_table2} SET \ AcctStopTime = '%S', \ AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, \ AcctInputOctets = convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \ AcctOutputOctets = convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', \ AcctTerminateCause = '%{Acct-Terminate-Cause}', \ ConnectInfoStop = '%{Connect-Info}' \ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'" # # The update condition matched no existing sessions. Use # the values provided in the update to create a new session. # query = "\ INSERT INTO ${....acct_table2} \ (${...column_list}) \ VALUES \ ('%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{NAS-IP-Address}', \ '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ '%{NAS-Port-Type}', \ DATEADD(second, -1 * (%{%{Acct-Session-Time}:-0}),'%S'), \ '%S', \ '%S', \ %{%{Acct-Session-Time}:-NULL}, \ '%{Acct-Authentic}', \ '', \ '%{Connect-Info}', \ convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \ convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '%{Acct-Terminate-Cause}', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}', \ '%{Framed-IPv6-Address}', \ '%{Framed-IPv6-Prefix}', \ '%{Framed-Interface-Id}', \ '%{Delegated-IPv6-Prefix}')" } } }