DB Value Lookup Issue

[Gabriel Marais]

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 at deployingradius.com> wrote:
>
> On Jun 10, 2020, at 3:22 PM, Gabriel Marais <gabriel.j.marais at 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