Query help

[Fajar A. Nugraha]

On Fri, Oct 12, 2012 at 3:16 PM, Jonathan Bastin
<jonathan.bastin at peerpointinternet.co.uk> wrote:
> Issue with doing it that way is you would get decimal values returned which
> freeradius can't deal with.
>
> I am posting a bounty of $200

Good to hear that. Hopefuly someone will be able to help you.

> to someone that writes this in Perl

Why perl?

> and show
> instructions on how to install. It must update the replay with Cisco AV pair
> and be able to work with large values.

A quick glance shows you ONLY care whether the user is over limit or
not. Is that correct?

If yes, it should be MUCH quicker to simply do the comparison INSIDE
the sql statement. Something like (untested, should work for mysql):

if ("%{sql: ( (SELECT radgroupcheck.value FROM radusergroup Inner Join
radgroupcheck ON radusergroup.groupname = radgroupcheck.groupname
WHERE radusergroup.username = '%{User-Name}' AND
radgroupcheck.attribute = 'CS-Total-Octets-Monthly' LIMIT 1) <=
(SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct WHERE
UserName='%{User-Name}' AND AcctStartTime >
(DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY))) ) as
overlimit" } == 1 ){

update reply {
                        Reply-Message := "You have reached your
transfer limit. Limited bandwitch"
                                Cisco-AVPair :=
"lcp:interface-config#1=rate-limit output 524288 98304 196608
conform-action transmit exceed-action drop"
                                Cisco-AVPair :=
"lcp:interface-config#2=rate-limit input 524288 98304 196608
conform-action transmit exceed-action drop"
                }
}

Basically mysql supports subselect, and on a TRUE comparison test (e.g
"select ((select 2) > (select 1)) as result") it will return "1". So
you only need to test in FR whether the return value is 1 or not.
Other db should have similar feature.

-- 
Fajar