3 ## counter.conf -- PostgreSQL queries for rlm_sqlcounter
7 # Rather than maintaining seperate (GDBM) databases of
8 # accounting info for each counter, this module uses the data
9 # stored in the raddacct table by the sql modules. This
10 # module NEVER does any database INSERTs or UPDATEs. It is
11 # totally dependent on the SQL module to process Accounting
14 # The 'sqlmod_inst' parameter holds the instance of the sql
15 # module to use when querying the SQL database. Normally it
16 # is just "sql". If you define more and one SQL module
17 # instance (usually for failover situations), you can
18 # specify which module has access to the Accounting Data
21 # The 'reset' parameter defines when the counters are all
22 # reset to zero. It can be hourly, daily, weekly, monthly or
23 # never. It can also be user defined. It should be of the
26 # h: hours, d: days, w: weeks, m: months
27 # If the letter is ommited days will be assumed. In example:
28 # reset = 10h (reset every 10 hours)
29 # reset = 12 (reset every 12 days)
31 # The 'key' parameter specifies the unique identifier for the
32 # counter records (usually 'User-Name').
34 # The 'query' parameter specifies the SQL query used to get
35 # the current Counter value from the database. There are 3
36 # parameters that can be used in the query:
38 # %b unix time value of beginning of reset period
39 # %e unix time value of end of reset period
41 # The 'check-name' parameter is the name of the 'check'
42 # attribute to use to access the counter in the 'users' file
43 # or SQL radcheck or radcheckgroup tables.
45 # DEFAULT Max-Daily-Session > 3600, Auth-Type = Reject
46 # Reply-Message = "You've used up more than one hour today"
48 sqlcounter dailycounter {
49 counter-name = Daily-Session-Time
50 check-name = Max-Daily-Session
51 reply-name = Session-Timeout
56 # This query properly handles calls that span from the
57 # previous reset period into the current period but
58 # involves more work for the SQL server than those
60 query = "SELECT SUM(acctsessiontime - \
61 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
62 FROM radacct WHERE username = '%{%k}' AND \
63 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"
65 # This query ignores calls that started in a previous
66 # reset period and continue into into this one. But it
67 # is a little easier on the SQL server
68 # query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \
69 # username = '%{%k}' AND acctstarttime > FROM_UNIXTIME('%b')"
71 # This query is the same as above, but demonstrates an
72 # additional counter parameter '%e' which is the
73 # timestamp for the end of the period
74 # query = "SELECT SUM(acctsessiontime) FROM radacct \
75 # WHERE username = '%{%k}' AND acctstarttime BETWEEN \
76 # FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
79 sqlcounter monthlycounter {
80 counter-name = Monthly-Session-Time
81 check-name = Max-Monthly-Session
82 reply-name = Session-Timeout
87 # This query properly handles calls that span from the
88 # previous reset period into the current period but
89 # involves more work for the SQL server than those
91 query = "SELECT SUM(acctsessiontime - \
92 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
93 FROM radacct WHERE username='%{%k}' AND \
94 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"
96 # This query ignores calls that started in a previous
97 # reset period and continue into into this one. But it
98 # is a little easier on the SQL server
99 # query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \
100 # username='%{%k}' AND acctstarttime > FROM_UNIXTIME('%b')"
102 # This query is the same as above, but demonstrates an
103 # additional counter parameter '%e' which is the
104 # timestamp for the end of the period
105 # query = "SELECT SUM(acctsessiontime) FROM radacct \
106 # WHERE username='%{%k}' AND acctstarttime BETWEEN \
107 # FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
110 sqlcounter noresetcounter {
111 counter-name = Max-All-Session-Time
112 check-name = Max-All-Session
116 query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{%k}'"