# -*- text -*- ## ## counter.conf -- PostgreSQL queries for rlm_sqlcounter ## ## $Id: a327819efb27c5342579ebb310aa47e9c4ade5d6 $ # Rather than maintaining seperate (GDBM) databases of # accounting info for each counter, this module uses the data # stored in the raddacct table by the sql modules. This # module NEVER does any database INSERTs or UPDATEs. It is # totally dependent on the SQL module to process Accounting # packets. # # The 'sqlmod_inst' parameter holds the instance of the sql # module to use when querying the SQL database. Normally it # is just "sql". If you define more and one SQL module # instance (usually for failover situations), you can # specify which module has access to the Accounting Data # (radacct table). # # The 'reset' parameter defines when the counters are all # reset to zero. It can be hourly, daily, weekly, monthly or # never. It can also be user defined. It should be of the # form: # num[hdwm] where: # h: hours, d: days, w: weeks, m: months # If the letter is ommited days will be assumed. In example: # reset = 10h (reset every 10 hours) # reset = 12 (reset every 12 days) # # The 'key' parameter specifies the unique identifier for the # counter records (usually 'User-Name'). # # The 'query' parameter specifies the SQL query used to get # the current Counter value from the database. There are 3 # parameters that can be used in the query: # %k 'key' parameter # %b unix time value of beginning of reset period # %e unix time value of end of reset period # # The 'check-name' parameter is the name of the 'check' # attribute to use to access the counter in the 'users' file # or SQL radcheck or radcheckgroup tables. # # DEFAULT Max-Daily-Session > 3600, Auth-Type = Reject # Reply-Message = "You've used up more than one hour today" # sqlcounter dailycounter { counter-name = Daily-Session-Time check-name = Max-Daily-Session reply-name = Session-Timeout sqlmod-inst = sql key = User-Name reset = daily # This query properly handles calls that span from the # previous reset period into the current period but # involves more work for the SQL server than those # below query = "SELECT SUM(acctsessiontime - \ GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \ FROM radacct WHERE username = '%{%k}' AND \ UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'" # This query ignores calls that started in a previous # reset period and continue into into this one. But it # is a little easier on the SQL server # query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \ # username = '%{%k}' AND acctstarttime > FROM_UNIXTIME('%b')" # This query is the same as above, but demonstrates an # additional counter parameter '%e' which is the # timestamp for the end of the period # query = "SELECT SUM(acctsessiontime) FROM radacct \ # WHERE username = '%{%k}' AND acctstarttime BETWEEN \ # FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')" } sqlcounter monthlycounter { counter-name = Monthly-Session-Time check-name = Max-Monthly-Session reply-name = Session-Timeout sqlmod-inst = sql key = User-Name reset = monthly # This query properly handles calls that span from the # previous reset period into the current period but # involves more work for the SQL server than those # below query = "SELECT SUM(acctsessiontime - \ GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \ FROM radacct WHERE username='%{%k}' AND \ UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'" # This query ignores calls that started in a previous # reset period and continue into into this one. But it # is a little easier on the SQL server # query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \ # username='%{%k}' AND acctstarttime > FROM_UNIXTIME('%b')" # This query is the same as above, but demonstrates an # additional counter parameter '%e' which is the # timestamp for the end of the period # query = "SELECT SUM(acctsessiontime) FROM radacct \ # WHERE username='%{%k}' AND acctstarttime BETWEEN \ # FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')" } sqlcounter noresetcounter { counter-name = Max-All-Session-Time check-name = Max-All-Session sqlmod-inst = sql key = User-Name reset = never query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{%k}'" } sqlcounter chillispot_max_bytes { counter-name = Max-Total-Octets check-name = ChilliSpot-Max-Total-Octets reply-name = ChilliSpot-Max-Total-Octets reply-message = "You have reached your bandwidth limit" sqlmod-inst = sql key = User-Name reset = never query = "SELECT IFNULL((SUM(AcctInputOctets + AcctOutputOctets)),0) FROM radacct WHERE username = '%{${key}}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%%b'" }