Re: SQL Accounting... ¿Negative?

[Alan DeKok]

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.

> 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.

  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.

  Alan DeKok.