Subversion Repositories ALCASAR

Rev

Rev 1279 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log

Rev 1279 Rev 1289
Line 32... Line 32...
32 
#  counter records (usually 'User-Name').
 32 
#  counter records (usually 'User-Name').
33 
#
 33 
#
34 
#  The 'query' parameter specifies the SQL query used to get
 34 
#  The 'query' parameter specifies the SQL query used to get
35 
#  the current Counter value from the database. There are 3
 35 
#  the current Counter value from the database. There are 3
36 
#  parameters that can be used in the query:
 36 
#  parameters that can be used in the query:
37 
#		%k	'key' parameter
 37 
#		${key}	'key' parameter
38 
#		%b	unix time value of beginning of reset period
 38 
#		%b	unix time value of beginning of reset period
39 
#		%e	unix time value of end of reset period
 39 
#		%e	unix time value of end of reset period
40 
#
 40 
#
41 
#  The 'check-name' parameter is the name of the 'check'
 41 
#  The 'check-name' parameter is the name of the 'check'
42 
#  attribute to use to access the counter in the 'users' file
 42 
#  attribute to use to access the counter in the 'users' file
Line 57... Line 57...
57 
	# previous reset period into the current period but
 57 
	# previous reset period into the current period but
58 
	# involves more work for the SQL server than those
 58 
	# involves more work for the SQL server than those
59 
	# below
 59 
	# below
60 
	query = "SELECT SUM(acctsessiontime - \
 60 
	query = "SELECT SUM(acctsessiontime - \
61 
                 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
 61 
                 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
62 
                 FROM radacct WHERE username = '%{%k}' AND \
 62 
                 FROM radacct WHERE username = '%{${key}}' AND \
63 
                 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"
 63 
                 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"
64 
 
 64 
 
65 
	# This query ignores calls that started in a previous
 65 
	# This query ignores calls that started in a previous
66 
	# reset period and continue into into this one. But it
 66 
	# reset period and continue into into this one. But it
67 
	# is a little easier on the SQL server
 67 
	# is a little easier on the SQL server
68 
#	query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \
 68 
#	query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \
69 
#                username = '%{%k}' AND acctstarttime > FROM_UNIXTIME('%b')"
 69 
#                username = '%{${key}}' AND acctstarttime > FROM_UNIXTIME('%b')"
70 
 
 70 
 
71 
	# This query is the same as above, but demonstrates an
 71 
	# This query is the same as above, but demonstrates an
72 
	# additional counter parameter '%e' which is the
 72 
	# additional counter parameter '%e' which is the
73 
	# timestamp for the end of the period
 73 
	# timestamp for the end of the period
74 
#	query = "SELECT SUM(acctsessiontime) FROM radacct \
 74 
#	query = "SELECT SUM(acctsessiontime) FROM radacct \
75 
#                WHERE username = '%{%k}' AND acctstarttime BETWEEN \
 75 
#                WHERE username = '%{${key}}' AND acctstarttime BETWEEN \
76 
#                FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
 76 
#                FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
77 
}
 77 
}
78 
 
 78 
 
79 
sqlcounter monthlycounter {
 79 
sqlcounter monthlycounter {
80 
	counter-name = Monthly-Session-Time
 80 
	counter-name = Monthly-Session-Time
Line 88... Line 88...
88 
	# previous reset period into the current period but
 88 
	# previous reset period into the current period but
89 
	# involves more work for the SQL server than those
 89 
	# involves more work for the SQL server than those
90 
	# below
 90 
	# below
91 
	query = "SELECT SUM(acctsessiontime - \
 91 
	query = "SELECT SUM(acctsessiontime - \
92 
                 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
 92 
                 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
93 
                 FROM radacct WHERE username='%{%k}' AND \
 93 
                 FROM radacct WHERE username='%{${key}}' AND \
94 
                 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"
 94 
                 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"
95 
 
 95 
 
96 
	# This query ignores calls that started in a previous
 96 
	# This query ignores calls that started in a previous
97 
	# reset period and continue into into this one. But it
 97 
	# reset period and continue into into this one. But it
98 
	# is a little easier on the SQL server
 98 
	# is a little easier on the SQL server
99 
#	query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \
 99 
#	query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \
100 
#                username='%{%k}' AND acctstarttime > FROM_UNIXTIME('%b')"
 100 
#                username='%{${key}}' AND acctstarttime > FROM_UNIXTIME('%b')"
101 
 
 101 
 
102 
	# This query is the same as above, but demonstrates an
 102 
	# This query is the same as above, but demonstrates an
103 
	# additional counter parameter '%e' which is the
 103 
	# additional counter parameter '%e' which is the
104 
	# timestamp for the end of the period
 104 
	# timestamp for the end of the period
105 
#	query = "SELECT SUM(acctsessiontime) FROM radacct \
 105 
#	query = "SELECT SUM(acctsessiontime) FROM radacct \
106 
#                WHERE username='%{%k}' AND acctstarttime BETWEEN \
 106 
#                WHERE username='%{${key}}' AND acctstarttime BETWEEN \
107 
#                FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
 107 
#                FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
108 
}
 108 
}
109 
 
 109 
 
110 
sqlcounter noresetcounter {
 110 
sqlcounter noresetcounter {
111 
        counter-name = Max-All-Session-Time
111 
        counter-name = Max-All-Session-Time
112 
                check-name = Max-All-Session
112 
                check-name = Max-All-Session
113 
                sqlmod-inst = sql
113 
                sqlmod-inst = sql
114 
                key = User-Name
114 
                key = User-Name
115 
                reset = never
115 
                reset = never
116 
	# This is the original query
 116 
	# This is the original query
117 
#       query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{%k}'"
117 
#       query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{${key}}'"
118 
	# This is the query modified for ALCASAR needs (thanks to Daniel Laliberte --> authorized period after the first connection)
 118 
	# This is the query modified for ALCASAR needs (thanks to Daniel Laliberte --> authorized period after the first connection)
119 
	query = "SELECT IFNULL((SELECT TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime)) \
 119 
	query = "SELECT IFNULL((SELECT TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime)) \
120 
                 FROM radacct \
 120 
                 FROM radacct \
121 
                 WHERE UserName='%{%k}' \
 121 
                 WHERE UserName='%{${key}}' \
122 
                 ORDER BY acctstarttime \
 122 
                 ORDER BY acctstarttime \
123 
                 LIMIT 1),0)"
 123 
                 LIMIT 1),0)"
124 
}
 124 
}