3 ## counter.conf -- MySQL 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 sql-module-instance' 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:
37 # %b unix time value of beginning of reset period
38 # %e unix time value of end of reset period
40 # The 'check-name' parameter is the name of the 'check'
41 # attribute to use to access the counter in the 'users' file
42 # or SQL radcheck or radcheckgroup tables.
44 # DEFAULT Max-Daily-Session > 3600, Auth-Type = Reject
45 # Reply-Message = "You've used up more than one hour today"
47 sqlcounter dailycounter {
48 sql-module-instance = sql
50 counter-name = Daily-Session-Time
51 check-name = Max-Daily-Session
52 reply-name = Session-Timeout
57 # This query properly handles calls that span from the
58 # previous reset period into the current period but
59 # involves more work for the SQL server than those
61 query = "SELECT SUM(acctsessiontime - \
62 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
63 FROM radacct WHERE username = '%{${key}}' AND \
64 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"
66 # This query ignores calls that started in a previous
67 # reset period and continue into into this one. But it
68 # is a little easier on the SQL server
69 # query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \
70 # username = '%{${key}}' AND acctstarttime > FROM_UNIXTIME('%b')"
72 # This query is the same as above, but demonstrates an
73 # additional counter parameter '%e' which is the
74 # timestamp for the end of the period
75 # query = "SELECT SUM(acctsessiontime) FROM radacct \
76 # WHERE username = '%{${key}}' AND acctstarttime BETWEEN \
77 # FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
80 sqlcounter monthlycounter {
81 sql-module-instance = sql
83 counter-name = Monthly-Session-Time
84 check-name = Max-Monthly-Session
85 reply-name = Session-Timeout
89 # This query properly handles calls that span from the
90 # previous reset period into the current period but
91 # involves more work for the SQL server than those
93 query = "SELECT SUM(acctsessiontime - \
94 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
95 FROM radacct WHERE username='%{${key}}' AND \
96 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"
98 # This query ignores calls that started in a previous
99 # reset period and continue into into this one. But it
100 # is a little easier on the SQL server
101 # query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \
102 # username='%{${key}}' AND acctstarttime > FROM_UNIXTIME('%b')"
104 # This query is the same as above, but demonstrates an
105 # additional counter parameter '%e' which is the
106 # timestamp for the end of the period
107 # query = "SELECT SUM(acctsessiontime) FROM radacct \
108 # WHERE username='%{${key}}' AND acctstarttime BETWEEN \
109 # FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
112 sqlcounter noresetcounter {
113 sql-module-instance = sql
115 counter-name = Max-All-Session-Time
116 check-name = Max-All-Session
119 query = "SELECT IFNULL(SUM(AcctSessionTime),0) \
121 WHERE UserName='%{${key}}'"
125 # Set an account to expire T seconds after first login.
126 # Requires the Expire-After attribute to be set, in seconds.
127 # You may need to edit raddb/dictionary to add the Expire-After
129 sqlcounter expire_on_login {
130 sql-module-instance = sql
132 counter-name = Expire-After-Initial-Login
133 check-name = Expire-After
136 query = "SELECT TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime)) \
138 WHERE UserName='%{${key}}' \
139 ORDER BY acctstarttime \