Handling proxied accounting updates that have been delayed

[Patric]

Hi everyone,

I have an interesting dilemma that I would like to share with the list,
perhaps someone can shed some light on how they handle this kind of thing.

I have 2 freeradius 2.1.7 servers that receive accounting updates from
multiple clients, and proxy these accounting updates to eachother.
Sometimes (due to a number of reasons) the proxying is delayed.

When the proxied updates are eventually processed, they are written into 
my account logs mysql table. The acct_start_time is written as the 
current date and time, and the acct_delay_time holds the difference 
between the actual time of the accounting update and now.

As closely as I can tell, this is what I have had happen:

At 08h00 Server A receives a start record. This is put in the detail 
file to go to Server B. This is also written to the local accounting 
table with:

acct_start_time   = 2009-12-04 08:00:00

The proxy is delayed for 2 hours due to a network issue between Server A
and Server B.

At 09h00 Server B receives the next update stating that user 1 has used
7 input octets. This is put in the detail file to go to Server A. This
is also written to the local accounting table with:

acct_start_time   = 2009-12-04 09:00:00
acct_input_octets = 7

This is because the record did not exist so the 
accounting_update_query_alt was called which inserts instead of updating.

At 10h00 Server A and Server B can talk to each other, so Server A sends
its proxied start record to Server B.

The db record on Server B now reflects:

acct_start_time   = 2009-12-04 10:00:00
acct_input_octets = 7
acct_delay_time   = 3600 (delayed 2 hours)

Server B also sends its proxied update to server A, and now server A 
reflects the following:

acct_start_time   = 2009-12-04 08:00:00
acct_input_octets = 7

So what happened is that server A got the start record and created a new 
db entry at 08h00. Server B then got an interim update and since the 
record did not yet exist (proxied start record was delayed), it created 
its own record at 09h00. Proxying then started again, and server B 
received the start record and updated the start time to the current 
time, 10h00.

Now server A says that the session started at 08h00 and server B says 
the sessions tarted at 10h00

This is because the entry already existed on server B, so the alternate 
start query was used, which is currently:

         accounting_start_query_alt = "UPDATE ${acct_table_new} \
            SET \
            acct_start_time = '%S', \
            acct_delay_time = '%{Acct-Delay-Time}', \
            connect_info = '%{Connect-Info}' \
            WHERE \
            acct_session_id = '%{Acct-Session-Id}' \
            AND \
            user_name = '%{SQL-User-Name}' \
            AND \
            nas_ip_address = '%{NAS-IP-Address}'"


Currently my reporting software does not take into account the 
acct_delay time, it just uses acct_start_time and acct_end_time to 
calculate usage etc. So if the above situation occurs over midnight or 
over the end of the month, my 2 servers reflect different daily/monthly 
usage...

What do you guys do with situations such as these? Do you take into 
account the acct_delay_time when reporting? I have been toying with the 
idea of updating the accounting_start_query_alt not to update the 
acct_start_time, so basically if the record already exists then leave 
that as the start time? But as I type it now I realise this will break 
the delay time calculation so there will be no way to calculate the real 
start time...

Any advise or experiences would be much appreciated!
Many thanks
Patric