Freeradius-Server Abfrage

[disem]

Viele Jahre gearbeitet!

Jetzt fehlt der zweite "JOIN"? und das Insert, oder?

Gruß

[thomas_w]

Zitat von disem

Jetzt fehlt der zweite "JOIN"? und das Insert, oder?

Gruß

Sorry, mein Notebook ist leider gestern gestorben und ich habe einen alten Win2000 PC aktiviert, jetzt kann ich hier nicht direkt was ausprobieren...


Was fehlt Dir jetzt nochmal genau?

Grüße
Thomas

[disem]

Wieso gestorben?

# -*- 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:
#		%k	'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
	query = "SELECT SUM(acctsessiontime - \
                 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
                 FROM radacct WHERE username = '%{%k}' AND \
                 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"

	# 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 = '%{%k}' 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 = '%{%k}' 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
	query = "SELECT SUM(acctsessiontime - \
                 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
                 FROM radacct WHERE username='%{%k}' AND \
                 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"

	# 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='%{%k}' 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='%{%k}' 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 
        query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{%k}'" 
}

Das sind Beispiele vom Radiusserver, die kommen ohne Join/Insert/Updates zurecht. Ich kann einige nicht richtig deuten. Es werden variablen wie %b / %e benutzt. Die Inserts macht der SQL Server. Ich frag mich blos wie das funktionieren soll.


//////EDIT

Ich glaub ich habs! Ich werde mich später nach einem Test nochmal melden!

[disem]

SELECT TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime)) FROM radacct WHERE UserName='%{%k}' ORDER BY acctstarttime LIMIT 1;"


Das ist alles! Nur für die vollständigkeit! Ich danke Dir vielmals für die Hilfe. So eine nette Hilfe hatte ich ewig nicht in einem Forum.