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