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.