rlm_sqlcounter: Some strangenesses.

[George Koulyabin]

Hi.

I wrote wrong period of reseting (never). I played with 'monthly'. It was ok when no-reset counter was used.

Looking at source of rlm_sqlcounter I understood
a.) this module is designed for time counters;
b.) the returned value is result of more complex calculation when counter has period of reseting;
c.) the rlm_sqlcounter is limited by using of unsigned integers (2^32).

I tried to do some tricks for using this module for counting of rest of output octets.

a.) sqlcounter:
  sqlcounter monthlyoutputoctetscounter {
         counter-name = Max-Monthly-Output-Octets
                 check-name = Max-Monthly-Output      # This attribute I'll use with value of limit
                 reply-name = Cisco-Pre-Output-Octets # I'll not use this attribute into reply-list
                 sqlmod-inst = sql
                 key = User-Name
                 reset = never
 
         query = "This SQL-query calculates amount of eated traffic"
 
  }

b.) Using post-auth:
post-auth {
	...
	if ( "%{reply:Cisco-Pre-Output-Octets}" > 0 ) { 
                   update reply {
                        Cisco-Control-Info += "QV%{reply:Cisco-Pre-Output-Octets}"
                        Cisco-Pre-Output-Octets := 0
                        Cisco-Pre-Output-Octets -= 0  # unusable attribute is removed from reply-list
                   }
                }
	...
}	

I need your opinions.


On Wed, Aug 26, 2009 at 11:08:44AM +0400, George Koulyabin wrote:
> Hi.
> 
> I am running FR v2.1.6.
> 
> I am needing to generate some quota for users. This quota must be replied via attribute Cisco-Control-Info.
> I tryed to do this by different ways. I was interested by rlm_sqlcounter, as simple and powerful method of calculating of counters.
> I built test counter:
>  sqlcounter noresetoutputoctetscounter {
>         counter-name = Max-All-Output-Octets
>                 check-name = Max-All-Output
>                 reply-name = Cisco-Control-Info
>                 #reply-name = Session-Timeout
>                 sqlmod-inst = sql
>                 key = User-Name
>                 reset = never
> 
>         query = "SELECT COALESCE(SUM(AcctOutputOctets), 0) \
>                         FROM radacct \
>                         WHERE UserName = '%{%k}' AND (CiscoServiceInfo = '' OR CiscoServiceInfo IS NULL)"
> 
>  }
> 
> I inserted Max-All-Output attribute/value pair into radcheck and added 'noresetoutputoctetscounter' into authorize section.
> 
> a.) I set Max-All-Output to 100000000 and used Session-Timeout as reply-name when first experiment was. Result of query was 60842094 for all experiments. I had got true result for this case.
> 
> b.) I increased value of Max-All-Output to 700000000 and had got 700486142. But this value is greater then value of check-name (Max-All-Output).
> 
> c.) Reply-name was replaced with Cisco-Control-Info by me. Cisco-Control-Info has type of string and Session-Timeout has type of integer. When I looked into log I found that "rlm_sqlcounter: Sent Reply-Item for user guest, Type=Cisco-Control-Info, value=700486142". But reply-list went with empty Cisco-Control-Info: 'Cisco-Control-Info = ""'.
> 
> Where can I find information about restrictions of rlm_sqlcounter?
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html