| 0,0 → 1,124 |
|---|
| # -*- text -*- |
| ## |
| ## counter.conf -- PostgreSQL queries for rlm_sqlcounter |
| ## |
| ## $Id$ |
| |
| # 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 |
| # This is the original query |
| # query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{%k}'" |
| # This is the query modified for ALCASAR needs (thanks to Daniel Laliberte --> authorized period after the first connection) |
| query = "SELECT IFNULL((SELECT TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime)) \ |
| FROM radacct \ |
| WHERE UserName='%{%k}' \ |
| ORDER BY acctstarttime \ |
| LIMIT 1),0)" |
| } |