Subversion Repositories ALCASAR

# -*- 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:

#		${key}	'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

		# Ancienne requete

#	query = "SELECT SUM(acctsessiontime - \

#                 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \

#                 FROM radacct WHERE username = '%{${key}}' AND \

#                 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"

		# Requete modifiée

       query = "SELECT IFNULL((SELECT SUM(acctsessiontime -  \

		GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)),0)) \

		FROM radacct WHERE username = '%{${key}}' AND \

		UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'),0)"

	# 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 = '%{${key}}' 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 = '%{${key}}' 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

		# Ancienne requete

#        query = "SELECT SUM(acctsessiontime - \

#                 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \

#                 FROM radacct WHERE username='%{${key}}' AND \

#                 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"

		# Requete modifiée

	query = "SELECT IFNULL((SELECT SUM(acctsessiontime - \

                 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \

                 FROM radacct WHERE username='%{${key}}' AND \

                 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'),0)"

	# 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='%{${key}}' 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='%{${key}}' 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='%{${key}}'" 

	# 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='%{${key}}' \

                 ORDER BY acctstarttime \

                 LIMIT 1),0)"

}