79
# This module is an SQL enabled version of the counter module.
81
# Rather than maintaining seperate (GDBM) databases of
82
# accounting info for each counter, this module uses the data
83
# stored in the raddacct table by the sql modules. This
84
# module NEVER does any database INSERTs or UPDATEs. It is
85
# totally dependent on the SQL module to process Accounting
88
# The 'sqlmod_inst' parameter holds the instance of the sql
89
# module to use when querying the SQL database. Normally it
90
# is just "sql". If you define more and one SQL module
91
# instance (usually for failover situations), you can
92
# specify which module has access to the Accounting Data
95
# The 'reset' parameter defines when the counters are all
96
# reset to zero. It can be hourly, daily, weekly, monthly or
97
# never. It can also be user defined. It should be of the
100
# h: hours, d: days, w: weeks, m: months
101
# If the letter is ommited days will be assumed. In example:
102
# reset = 10h (reset every 10 hours)
103
# reset = 12 (reset every 12 days)
105
# The 'key' parameter specifies the unique identifier for the
106
# counter records (usually 'User-Name').
108
# The 'query' parameter specifies the SQL query used to get
109
# the current Counter value from the database. There are 3
110
# parameters that can be used in the query:
112
# %b unix time value of beginning of reset period
113
# %e unix time value of end of reset period
116
# The 'check-name' parameter is the name of the 'check'
117
# attribute to use to access the counter in the 'users' file
118
# or SQL radcheck or radcheckgroup tables.
120
# DEFAULT Max-Daily-Session > 3600, Auth-Type = Reject
121
# Reply-Message = "You've used up more than one hour today"
123
sqlcounter dailycounter {
124
counter-name = Daily-Session-Time
125
check-name = Max-Daily-Session
130
# This query properly handles calls that span from the
131
# previous reset period into the current period but
132
# involves more work for the SQL server than those
134
query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
136
# This query ignores calls that started in a previous
137
# reset period and continue into into this one. But it
138
# is a little easier on the SQL server
139
# query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime > FROM_UNIXTIME('%b')"
141
# This query is the same as above, but demonstrates an
142
# additional counter parameter '%e' which is the
143
# timestamp for the end of the period
144
# query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime BETWEEN FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
147
sqlcounter monthlycounter {
148
counter-name = Monthly-Session-Time
149
check-name = Max-Monthly-Session
150
sqlmod-inst = sqlcca3
154
# This query properly handles calls that span from the
155
# previous reset period into the current period but
156
# involves more work for the SQL server than those
158
query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
160
# This query ignores calls that started in a previous
161
# reset period and continue into into this one. But it
162
# is a little easier on the SQL server
163
# query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime > FROM_UNIXTIME('%b')"
165
# This query is the same as above, but demonstrates an
166
# additional counter parameter '%e' which is the
167
# timestamp for the end of the period
168
# query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime BETWEEN FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
171
79
# To create a dbm users file, do:
173
81
# cat test.users | rlm_dbm_parser -f /etc/raddb/users_db