Acct-Delay-Time attribute in buffered-sql server mode

[Arran Cudbard-Bell]

On 4 Oct 2014, at 07:51, Rygl Aleš <ales at rygl.net> wrote:

> Hi all,
>  
> I have found an interesting issue related to Acct-Delay-Time attribute when buffered-sql mode is in use. I am not sure if it is a bug or feature. IMHO it is a bug but I would like to discuss it here.
>  
> I am using freeradius for accounting in buffered-sql mode with MySQL backend. The main radacct table is using memory engine so it is pretty fast nevertheless the buffered-sql mode is a must. We have more 1.000 Acct req/sec in average with additional repeated peaks. We need to store the Acct-Delay-Time for diagnostic purposes. It is not always present in the Accounting request from NAS but if it is there it indicates that NAS is experiencing some performance problems. 
>  
> And the issue is that even if there is no Acct-Delay-Time received from the NAS, it appears in radacct table then and from my observations it has nothing to do with the real delay of the session start and indicates just the amount of time for which the record was waiting in radacct/detail.work file. IMHO this is wrong because the SQL scripts in dialup.conf are doing a correction of session start time based using Acct-Delay-Time.
>  
> My accounting-start-query:
>  
> accounting_start_query = "INSERT into ${acct_table1} (AcctUpdateTime, AcctSessionId, AcctUniqueId, UserName, NASIPAddress, AcctStartTime, AcctSessionTime, AcctAuthentic, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, FramedIPAddress, AcctStartDelay, AcctStopDelay, SgsnIpAddr, Imsi, 3GPPSelectionMode, 3GPPGPRSNegotiatedQoSProfile, Acct_Status_Type, 3GPPChargingId, 3GPPNSAPI, 3GPPSGSNMNCMCC, 3GPPRATType, 3GPPUserLocationInfo, 3GPPMSTimezone, 3GPPIMEISV, TMOChargingRuleBaseName, SNVirtualAPNName, SNRulebase) values('%S', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{NAS-IP-Address}', DATE_SUB('%S', INTERVAL %{Acct-Delay-Time:-0} SECOND), '0', '%{Acct-Authentic}', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0','%{3GPP-SGSN-Address}','%{3GPP-IMSI}','%{3GPP-Selection-Mode}','%{3GPP-GPRS-Negotiated-QoS-profile}','%{Acct-Status-Type}','%{3GPP-Charging-ID}','%{3GPP-NSAPI}','%{3GPP-SGSN-MCC-MNC}','%{3GPP-RAT-Type}','%{3GPP-Location-Info}','%{3GPP-MS-Time-Zone}','%{3GPP-IMEISV}','%{TMO-Charging-Rule-Base-Name}','%{SN-Virtual-APN-Name}','%{SN-Rulebase}')"
>  
>  
> I always thought buffered-sql server is just a loader of the detail file to db. But it looks like it is working as a real radius server manipulating the  Acct-Delay-Time attribute which is IMHO wrong here and should be suppressed somehow. What is your opinion?

No, manipulating Acct-Delay-Time here is correct. It represents the amount of time between the event occurring and the current time.

Acct-Delay-Time shouldn't be used directly by the queries to calculate timestamp offsets.

On receipt of an Accounting-Request if Event-Timestamp isn't present it should be synthesised from applying the received 
Acct-Delay-Time to the current time on the RADIUS server.

If Event-Timestamp is present it should not be touched.

When writing our detail file entries Event-Timestamp should be recorded.

The queries in 3.0.x all use Event-Timestamp instead of using Acct-Delay-Time. You could alter you queries to do the same, and add
the logic to synthesise Event-Timestamp if absent.

Arran Cudbard-Bell <a.cudbardb at freeradius.org>
FreeRADIUS development team

FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 881 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20141004/dc0a73af/attachment.pgp>