Good evening users, I am currently trying to resolve an issue with calculating the difference between the value of acctinputoctets and acctoutputoctets from a MySQL lookup and the latest interim update received from the carrier. My calculation is currently in the preacct section which looks like this:- preacct { preprocess if("%{sql:SELECT count(*) from radacct where acctsessionid = '%{Acct-Session-Id}'}" >= 1) { update request { Acct-Diff = "%{sql:SELECT greatest(0, ('%{Acct-Input-Octets}' + '%{Acct-Output-Octets}') - (acctinputoctets + acctoutputoctets)) as diff from radacct where acctsessionid = '%{Acct-Session-Id}'}" } } The query works fine, until the values from the radacct table (acctinputoctets + acctoutputoctets) are greater than 4 294 967 296 for the particular acctsessionid. When the value is larger than that we get 0. Under that we get the actual difference. *Above 4 294 967 296 Wed Jun 10 15:27:17 2020 : Debug: (12) Acct-Diff = "0" *Below 4 294 967 296 Tue Jun 9 15:10:42 2020 : Debug: (9) Acct-Diff += "254631" I changed the dictionary entry for Acct-Diff from Integer to String. The calculation works again until the number is greater than 4294967296. I suspect what might be happening is the sum (acctinputoctets + acctoutputoctets) is getting treated as two integer values. So when they reach a value higher than 4294967296 the calculation does not work and sends 0 due to the SQL SELECT greatest(0, I was hoping someone on the list would be able to point me in the right direction... Many thanks, Gabriel
On Jun 10, 2020, at 3:22 PM, Gabriel Marais <gabriel.j.marais@gmail.com> wrote,
I am currently trying to resolve an issue with calculating the difference between the value of acctinputoctets and acctoutputoctets from a MySQL lookup and the latest interim update received from the carrier.
My calculation is currently in the preacct section which looks like this:-
preacct { preprocess if("%{sql:SELECT count(*) from radacct where acctsessionid = '%{Acct-Session-Id}'}" >= 1) {
update request {
Acct-Diff = "%{sql:SELECT greatest(0, ('%{Acct-Input-Octets}' + '%{Acct-Output-Octets}') - (acctinputoctets + acctoutputoctets)) as diff from radacct where acctsessionid = '%{Acct-Session-Id}'}"
You're doing the math in an SQL statement which is fine, but it's likely to use 32-bit integers by default.
The query works fine, until the values from the radacct table (acctinputoctets + acctoutputoctets) are greater than 4 294 967 296 for the particular acctsessionid. When the value is larger than that we get 0. Under that we get the actual difference.
Yes. The solution is to ensure that the integers are cast to 64 bits when doing the math. You'll have to check your SQL documentation to see how to do that.
I changed the dictionary entry for Acct-Diff from Integer to String. The calculation works again until the number is greater than 4294967296.
Then the 4B limit is due to SQL. If you want Acct-Diff to *also* store more than 4G of counters, make it a "integer64" data type. That's 64 bits, and should be more than large enough. Alan DeKok.
Hi Alan Thanks for the feedback. We tried all the suggestions below but unfortunately still receive a 0 value when the number is larger than 4 294 967 296. We edited our query to cast values as a 64 bit integer. Also edited the dictionary to make Acct-Diff a interger64. Acct-Diff = "%{sql:SELECT greatest(0, ('%{Acct-Input-Octets}' + '%{Acct-Output-Octets}') - (CAST(acctinputoctets AS SIGNED) + CAST(acctoutputoctets AS SIGNED))) as diff from radacct where acctsessionid = '%{Acct-Session-Id}'}" We also tried a UNSIGNED value Acct-Diff = "%{sql:SELECT greatest(0, ('%{Acct-Input-Octets}' + '%{Acct-Output-Octets}') - (CAST(acctinputoctets AS UNSIGNED) + CAST(acctoutputoctets AS UNSIGNED))) as diff from radacct where acctsessionid = '%{Acct-Session-Id}'}" The values in our SQL db are bigint. Still unsure why we're getting a 0 :( On Wed, Jun 10, 2020 at 10:59 PM Alan DeKok <aland@deployingradius.com> wrote:
On Jun 10, 2020, at 3:22 PM, Gabriel Marais <gabriel.j.marais@gmail.com> wrote,
I am currently trying to resolve an issue with calculating the difference between the value of acctinputoctets and acctoutputoctets from a MySQL lookup and the latest interim update received from the carrier.
My calculation is currently in the preacct section which looks like this:-
preacct { preprocess if("%{sql:SELECT count(*) from radacct where acctsessionid = '%{Acct-Session-Id}'}" >= 1) {
update request {
Acct-Diff = "%{sql:SELECT greatest(0, ('%{Acct-Input-Octets}' + '%{Acct-Output-Octets}') - (acctinputoctets + acctoutputoctets)) as diff from radacct where acctsessionid = '%{Acct-Session-Id}'}"
You're doing the math in an SQL statement which is fine, but it's likely to use 32-bit integers by default.
The query works fine, until the values from the radacct table (acctinputoctets + acctoutputoctets) are greater than 4 294 967 296 for the particular acctsessionid. When the value is larger than that we get 0. Under that we get the actual difference.
Yes. The solution is to ensure that the integers are cast to 64 bits when doing the math. You'll have to check your SQL documentation to see how to do that.
I changed the dictionary entry for Acct-Diff from Integer to String. The calculation works again until the number is greater than 4294967296.
Then the 4B limit is due to SQL. If you want Acct-Diff to *also* store more than 4G of counters, make it a "integer64" data type. That's 64 bits, and should be more than large enough.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Jun 15, 2020, at 7:05 AM, Gabriel Marais <gabriel.j.marais@gmail.com> wrote:
We tried all the suggestions below but unfortunately still receive a 0 value when the number is larger than 4 294 967 296.
We edited our query to cast values as a 64 bit integer. Also edited the dictionary to make Acct-Diff a interger64. ...
The values in our SQL db are bigint.
Still unsure why we're getting a 0 :(
If the output of the expansion is "0", then the issue is in your SQL server. There's little we can do to help there. You'll have to read your SQL server documentation to fix the query. Alan DeKok.
participants (2)
-
Alan DeKok -
Gabriel Marais