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)"
}