How to use sqlcounter to disconnect a user after reaching the daily quota?

[Houman]

Alan,

It works! Thank you so much for the code snippets.  I wasn't that far off
after all it seems.  Btw I have inserted the integer64 at the end of the
dictionary file.  I think this is the right way judging by the existing
samples in the file.

ATTRIBUTE       My-Daily-Usage 3001 integer64

I'm happy with the solution to disallow the user entirely once the
daily-usage has been reached. My only concern is if there is an easy way to
convey a message back to the client, to inform the user why the VPN
connection is failing?

Maybe something like this?

        update reply {
                Reply-Message := "You have reached your bandwidth limit"
        }


On 30 December 2017 at 14:01, Alan DeKok <aland at deployingradius.com> wrote:

> On Dec 30, 2017, at 8:41 AM, Houman <houmie at gmail.com> wrote:
> > In the meanwhile, I got the book FreeRadius from Dirk Van Der Walt, but
> > it's quite old and uses the old syntax. But most of all it only explains
> > the time session counters. Not data usage. I keep researching to learn
> more
> > about it.
>
>   The counters aren't magic.  They are documented, and they work as
> documented.
>
>   If you want time-based counters, use rlm_sqlcounter.  If you want
> bandwidth-based counters, use something else.
>
> > Your tip with Wireshark is a good one, to sniff the connection and see
> what
> > information the NAS is sending.  The thing is I'm already getting the
> > Octets and am able to count the data usage.  You were right about the
> lack
> > of date range in my previous attempt.  I have improved the query and
> > extended the where-clause by month and year. Here it is:
> > https://stackoverflow.com/questions/48028701/how-to-use-
> sqlcounter-to-disconnect-a-user-after-reaching-the-monthly-quota
>
>   Why stack overflow?  Why not this list?
>
>   When you make it harder for us to help you, we're less inclined to help
> you.
>
> > When I run the query manually in the database it is giving me a higher
> > number than the limit specified in the radcheck.  And yet it still allows
> > me to connect, even though I was expecting a Session-Timeout to be
> raised.
>
>   The debug output should say why.  And it's likely because sqlcounter
> does time-based counting.  It doesn't do quota-based accounting.
>
>   The two are entirely separate.
>
>   For quota-based accounting, you can just run an SQL query manually.  Get
> the current bandwidth used from SQL, via an SQL query.  Then, use radcheck
> to check it...
>
>   In raddb/dictionary, add an attribute:
>
> ATTRIBUTE       My-Daily-Usage integer64 3001
>
> authorize {
>         ...
>
>         # get the users current usage from SQL
>         update request {
>                 My-Daily-Usage = "%{sql:SELECT....}"
>         }
>         ...
>         sql
>         ...
> }
>
>  And in radcheck, put "My-Daily-Usage < 1000000"
>
>   If the usage is under that, they will be allowed in.  If it's over that,
> they will be rejected.
>
>   It won't automatically limit the usage, because that's unfortunately not
> part of RADIUS.  But the above changes *should* work, and should get you a
> step forward.
>
>   Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/
> list/users.html
>