Re: SQL Accounting... ¿Negative?

[Gabriel Trabanco Llano]

Hi.

Firstly thanks for the help.

> El 4 sept 2019, a las 17:34, Alan DeKok <aland at deployingradius.com> escribió:
> 
> On Sep 4, 2019, at 2:43 AM, Gabriel Trabanco Llano <gabriel at trabanco.soy> wrote:
>> 
>> Firstly, I use Freeradius 3 with SQL and SQL Counter module under Ubuntu Server and because I need billing periods by days I made a triggers in redacct table to separate users billing information by hours to another table because as you know, we can have sessions larger than our billing period. The solution is the file I added called fix_accounting.sql.
>> 
>> Well, In the solution I made a procedure that get the information of the session and if there is no info about the user in this new table, make an insert if not make an update with new information. This is done by the procedure call.
> 
>  OK...
> 
>> This procedure needs the username, the timestamp that sends the NAS to calculate the billing period, the downloaded/uploaded data to add and also the session time to add. Because of this in the trigger when update the redacct we do a rest between the new value to update the data and the previous to know the difference of data to add but, sometimes the billing data in the new table is negative because of this.
> 
>  Don't mangle the radacct table.  Leave it as is.  If you need to do billing, copy the data to another table, and use that.

I do not manipulate the original table, it has the original information from the FR.

> 
>> Finally, when I compare the data between the two tables they give me exactly the same data if there is no a session larger than a day even if there is negative values… Does anyone know what is happening? I have no clue about. Obviously the new data is lower than the old but, why? Am I doing something wrong?
> 
>  Very likely, yes.
> 
>> The unique possibility I can imagine is that Freeradius is not updating the values correctly because it isn’t updating the values, it is expecting to add the amount of data to the row but that is not a possibility because in SQL you set a value and you have to calculate previously (maybe in the same SQL but you have to calculate the new value previously).
> 
>  RADIUS packets contain the total session time / bandwidht.  FreeRADIUS just writes this to the DB.  It doesn't *add* the new values to the old ones.

As far as I can see and I know of database for me when I saw the FR source code is rewrite the total amount of sessiontime, data input/output… So If in the trigger I rest:
		new_value - old_value

So it should contain the difference and I am writing the amount by hours in other table. Why sometimes this value is negative? For my as far as I can see there is not such a way where this value could be negative… Just in the case the NAS is bugged.

> 
>  Leave the radacct table alone.  If you need to do billing on short cycles, copy the "online" users to a different table.  Then, do billing *differences* between their current usage and the previous usage.
> 

This is what I am doing


>  Alan DeKok.
> 
> 

Regards Alan and thank you for the help, sorry I wasn’t explain myself good.

Gabriel Trabanco.