SQL Accounting... ¿Negative?

classic Classic list List threaded Threaded
5 messages Options
| Threaded
Open this post in threaded view
|

SQL Accounting... ¿Negative?

Gabriel Trabanco Llano
Hi everyone!

I do not know if it is really a problem… Because all data is ok but it is really strange.

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.

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.


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?

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

Thank you for your attention =)


Regards.




-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

fix_accounting.sql (8K) Download Attachment
| Threaded
Open this post in threaded view
|

Re: SQL Accounting... ¿Negative?

Alan DeKok-2
On Sep 4, 2019, at 2:43 AM, Gabriel Trabanco Llano <[hidden email]> 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.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: SQL Accounting... ¿Negative?

Gabriel Trabanco Llano
Hi.

Firstly thanks for the help.

> El 4 sept 2019, a las 17:34, Alan DeKok <[hidden email]> escribió:
>
> On Sep 4, 2019, at 2:43 AM, Gabriel Trabanco Llano <[hidden email]> 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.

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: SQL Accounting... ¿Negative?

Alan DeKok-2
On Sep 4, 2019, at 10:43 AM, Gabriel Trabanco Llano <[hidden email]> wrote:
> I do not manipulate the original table, it has the original information from the FR.

  That's good.

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

  RADIUS packets can come out of order.

  My $0.02 is to skip using a trigger.  Just run a stored procedure at the end of the billing cycle to copy the data to another table.  Then, run another stored procedure to do the "diffs" in usage between billing cycles.

  That way you're operating on the *data* and not on the *RADIUS packets*.  It's much safer.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: SQL Accounting... ¿Negative?

Gabriel Trabanco Llano
I checked the data many times comparing the table I created with the table and redacct and also with the negative data I have exactly the same amount of data for all users… It is strange ¬¬’

Anyway it is not for make a payable access point or whatever to do any payment so if there is any missing 100 mb there is no problem it does not matter if it is approximately or exactly whenever it is close to the right data. The big thing here is that is something that is happening with apparently no explanation because to insert the data on a period if you have seen my solution is based on the timestamp the packet was sent by the NAS and not by the server time.

Thank you for paying attention to me! :)

Gabriel Trabanco

> El 4 sept 2019, a las 17:45, Alan DeKok <[hidden email]> escribió:
>
> On Sep 4, 2019, at 10:43 AM, Gabriel Trabanco Llano <[hidden email]> wrote:
>> I do not manipulate the original table, it has the original information from the FR.
>
>  That's good.
>
>> 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.
>
>  RADIUS packets can come out of order.
>
>  My $0.02 is to skip using a trigger.  Just run a stored procedure at the end of the billing cycle to copy the data to another table.  Then, run another stored procedure to do the "diffs" in usage between billing cycles.
>
>  That way you're operating on the *data* and not on the *RADIUS packets*.  It's much safer.
>
>  Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html