Rlm sqlcounter -- Data Based Counter

Marcel Grandemange thavinci at thavinci.za.net
Tue Dec 2 21:32:27 CET 2008


Good Day.

Im wondering is someone could assist me here.
I currently have freeradius setup and working somewhat.

However I have following setup:


sqlcounter monthlytraffic {
                 counter-name = Monthly-Traffic
                 check-name = Max-Monthly-Traffic
                 reply-name = Mikrotik-Xmit-Limit-Gigawords
                 sqlmod-inst = sql
                 key = User-Name
                 reset = monthly
query = "SELECT (sum(AcctInputOctets)+sum(AcctOutputOctets)) FROM radacct
WHERE UserName='%{%k}' AND Month(AcctStopTime) =(Month(NOW()
#query = "SELECT SUM(AcctInputOctets - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b -
UNIX_T
#query = "SELECT SUM(AcctInputOctets + AcctOutputOctets) FROM radacct WHERE
UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) > '%b'"
#query = "SELECT SUM(OctetTotal) FROM thismonthsusage WHERE
UsernameView='%{%k}'"
}

As you can see I have tried numerous queries to get this working correctly
and decided finally to do my own query against a view that does my work for
me.
(Currently commented out because having issues with it)

The view is called thismonthsusage

And it's definition as follows....

select `radacct`.`UserName` AS `UsernameView`,`usergroup`.`GroupName` AS
`GroupNameView`,`radacct`.`AcctStartTime` AS
`AcctStartTimeView`,`radacct`.`AcctStopTime` AS
`AcctStopTimeView`,`radacct`.`AcctInputOctets` AS
`AcctInputOctetsView`,`radacct`.`AcctOutputOctets` AS
`AcctOutputOctetsView`,(sum(`radacct`.`AcctInputOctets`) +
sum(`radacct`.`AcctOutputOctets`)) AS `OctetTotal` from (`radacct` join
`usergroup`) where ((month(`radacct`.`AcctStartTime`) >= month(curdate()))
and (month(`radacct`.`AcctStopTime`) <= month(curdate())) and
(`radacct`.`UserName` = `usergroup`.`UserName`) and (`usergroup`.`GroupName`
<> _latin1'Mac')) group by
`radacct`.`UserName`,`usergroup`.`GroupName`,`radacct`.`AcctStartTime`,`rada
cct`.`AcctStopTime`,`radacct`.`AcctOutputOctets` order by
`radacct`.`AcctStopTime`

As you can see quite simply and efficient however there is one battle I am
facing..
As soon as a new month rolls over there are NO accounting entries for that
user for that month, hence no record and freeradius denies user as he hasn't
got any entries, any body know how I should modify the view to display all
users even if they don't have an entry for current month, but rather display
them with value 0 as apposed to not at all?




More information about the Freeradius-Users mailing list