DB Value Lookup Issue

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

DB Value Lookup Issue

Gabriel Marais-3
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
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: DB Value Lookup Issue

Alan DeKok-2
On Jun 10, 2020, at 3:22 PM, Gabriel Marais <[hidden email]> 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
| Threaded
Open this post in threaded view
|

Re: DB Value Lookup Issue

Gabriel Marais-3
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 <[hidden email]> wrote:

>
> On Jun 10, 2020, at 3:22 PM, Gabriel Marais <[hidden email]> 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
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: DB Value Lookup Issue

Alan DeKok-2
On Jun 15, 2020, at 7:05 AM, Gabriel Marais <[hidden email]> 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.


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