Monitoring Tool for Freeradius

Julian Stöver julian_st at gmx.de
Fri Feb 1 15:46:41 CET 2008


Hi,
I'm using the sql backend so i decided for getting the informations  
from the database. But freeradius doesn't put any data into the  
'radacct' table? Something is wrong there... The file /var/log/ 
freeradius/radutmp also no exists.

freeradius -X:
> [....]
> Module: Instantiated sql (sql)
> Module: Loaded Acct-Unique-Session-Id
>  acct_unique: key = "User-Name, Acct-Session-Id, NAS-IP-Address,  
> Client-IP-Address, NAS-Port"
> Module: Instantiated acct_unique (acct_unique)
> Module: Loaded files
>  files: usersfile = "/etc/freeradius/users"
>  files: acctusersfile = "/etc/freeradius/acct_users"
>  files: preproxy_usersfile = "/etc/freeradius/preproxy_users"
>  files: compat = "no"
> Module: Instantiated files (files)
> Module: Loaded detail
>  detail: detailfile = "/var/log/freeradius/radacct/%{Client-IP- 
> Address}/detail-%Y%m%d"
>  detail: detailperm = 384
>  detail: dirperm = 493
>  detail: locking = no
> Module: Instantiated detail (detail)
> Module: Loaded radutmp
>  radutmp: filename = "/var/log/freeradius/radutmp"
>  radutmp: username = "%{User-Name}"
>  radutmp: case_sensitive = yes
>  radutmp: check_with_nas = yes
>  radutmp: perm = 384
>  radutmp: callerid = yes
> Module: Instantiated radutmp (radutmp)
> Listening on authentication *:1812
> Listening on accounting *:1813
> Ready to process requests.
> rad_recv: Access-Request packet from host 127.0.0.1:32780, id=232,  
> length=46
> 	User-Name = "julian"
> 	User-Password = "blabla"
> rad_lowerpair:  User-Name now 'julian'
> rad_lowerpair:  User-Password now 'blabla'
>   Processing the authorize section of radiusd.conf
> modcall: entering group authorize for request 0
>   modcall[authorize]: module "preprocess" returns ok for request 0
>   modcall[authorize]: module "chap" returns noop for request 0
>   modcall[authorize]: module "mschap" returns noop for request 0
>     rlm_realm: No '@' in User-Name = "julian", looking up realm NULL
>     rlm_realm: No such realm "NULL"
>   modcall[authorize]: module "suffix" returns noop for request 0
>   rlm_eap: No EAP-Message, not doing EAP
>   modcall[authorize]: module "eap" returns noop for request 0
> radius_xlat:  'julian'
> rlm_sql (sql): sql_set_user escaped user --> 'julian'
> radius_xlat:  'SELECT id, UserName, Attribute, Value, op            
> FROM radcheck           WHERE Username = 'julian'           ORDER BY  
> id'
> rlm_sql (sql): Reserving sql socket id: 3
> radius_xlat:  'SELECT  
> radgroupcheck 
> .id 
> ,radgroupcheck 
> .GroupName 
> ,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  FROM  
> radgroupcheck,usergroup WHERE usergroup.Username = 'julian' AND  
> usergroup.GroupName = radgroupcheck.GroupName ORDER BY  
> radgroupcheck.id'
> radius_xlat:  'SELECT id, UserName, Attribute, Value, op            
> FROM radreply           WHERE Username = 'julian'           ORDER BY  
> id'
> radius_xlat:  'SELECT  
> radgroupreply 
> .id 
> ,radgroupreply 
> .GroupName 
> ,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  FROM  
> radgroupreply,usergroup WHERE usergroup.Username = 'julian' AND  
> usergroup.GroupName = radgroupreply.GroupName ORDER BY  
> radgroupreply.id'
> rlm_sql (sql): Released sql socket id: 3
>   modcall[authorize]: module "sql" returns ok for request 0
> modcall: leaving group authorize (returns ok) for request 0
> auth: type Local
> auth: user supplied User-Password matches local User-Password
> Login OK: [julian] (from client local_access port 0)
>   Processing the post-auth section of radiusd.conf
> modcall: entering group post-auth for request 0
> rlm_sql (sql): Processing sql_postauth
> radius_xlat:  'julian'
> rlm_sql (sql): sql_set_user escaped user --> 'julian'
> radius_xlat:  'INSERT into radpostauth (id, user, pass, reply, date)  
> values ('', 'julian', 'blabla', 'Access-Accept', NOW())'
> rlm_sql (sql) in sql_postauth: query is INSERT into radpostauth (id,  
> user, pass, reply, date) values ('', 'julian', 'blabla', 'Access- 
> Accept', NOW())
> rlm_sql (sql): Reserving sql socket id: 2
> rlm_sql (sql): Released sql socket id: 2
>   modcall[post-auth]: module "sql" returns ok for request 0
> modcall: leaving group post-auth (returns ok) for request 0
> Sending Access-Accept of id 232 to 127.0.0.1 port 32780
> 	Framed-IP-Address := 172.17.8.1
> 	Framed-Protocol := PPP
> 	Framed-Compression := Van-Jacobson-TCP-IP
> 	Framed-MTU := 1500

sql.conf
> sql {
> 	driver = "rlm_sql_mysql"
>
> 	# Connect info
> 	server = "172.19.1.2"
> 	login = "user"
> 	password = "9L2xWq"
>
> 	# Database table configuration
> 	radius_db = "user"
>
> 	acct_table1 = "radacct"
> 	acct_table2 = "radacct"
>
> 	# Allow for storing data after authentication
> 	postauth_table = "radpostauth"
>
> 	authcheck_table = "radcheck"
> 	authreply_table = "radreply"
>
> 	groupcheck_table = "radgroupcheck"
> 	groupreply_table = "radgroupreply"
>
> 	usergroup_table = "usergroup"
>
> 	# Table to keep radius client info
> 	nas_table = "nas"
>
> 	# Remove stale session if checkrad does not see a double login
> 	deletestalesessions = yes
>
> 	# Print all SQL statements when in debug mode (-x)
> 	sqltrace = no
> 	sqltracefile = ${logdir}/sqltrace.sql
>
> 	# number of sql connections to make to server
> 	num_sql_socks = 5
>
> 	connect_failure_retry_delay = 60
>
> 	#safe-characters =  
> "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.- 
> _: /"
>
> 	sql_user_name = "%{User-Name}"

> 	# default the default_user_profile is not set
> 	#default_user_profile = "DEFAULT"
> 	#query_on_not_found = no
>
> #	authorize_check_query = "SELECT id, UserName, Attribute, Value, op \
> #         FROM ${authcheck_table} \
> #         WHERE Username = BINARY '%{SQL-User-Name}' \
> #         ORDER BY id"
> #	authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \
> #         FROM ${authreply_table} \
> #         WHERE Username = BINARY '%{SQL-User-Name}' \
> #         ORDER BY id"
>
> 	# The default queries are case insensitive. (for compatibility with
> 	# older versions of FreeRADIUS)
> 	authorize_check_query = "SELECT id, UserName, Attribute, Value, op \
>           FROM ${authcheck_table} \
>           WHERE Username = '%{SQL-User-Name}' \
>           ORDER BY id"
> 	authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \
>           FROM ${authreply_table} \
>           WHERE Username = '%{SQL-User-Name}' \
>           ORDER BY id"
>
> 	# Use these for case sensitive usernames.
> #	authorize_group_check_query = "SELECT ${groupcheck_table}.id,$ 
> {groupcheck_table}.GroupName,${groupcheck_table}.Attribute,$ 
> {groupcheck_table}.Value,${groupcheck_table}.op FROM $ 
> {groupcheck_table},${usergroup_table} WHERE $ 
> {usergroup_table}.Username = BINARY '%{SQL-User-Name}' AND $ 
> {usergroup_table}.GroupName = ${groupcheck_table}.GroupName ORDER BY  
> ${groupcheck_table}.id"
> #	authorize_group_reply_query = "SELECT ${groupreply_table}.id,$ 
> {groupreply_table}.GroupName,${groupreply_table}.Attribute,$ 
> {groupreply_table}.Value,${groupreply_table}.op  FROM $ 
> {groupreply_table},${usergroup_table} WHERE $ 
> {usergroup_table}.Username = BINARY '%{SQL-User-Name}' AND $ 
> {usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY  
> ${groupreply_table}.id"
>
> 	authorize_group_check_query = "SELECT ${groupcheck_table}.id,$ 
> {groupcheck_table}.GroupName,${groupcheck_table}.Attribute,$ 
> {groupcheck_table}.Value,${groupcheck_table}.op  FROM $ 
> {groupcheck_table},${usergroup_table} WHERE $ 
> {usergroup_table}.Username = '%{SQL-User-Name}' AND $ 
> {usergroup_table}.GroupName = ${groupcheck_table}.GroupName ORDER BY  
> ${groupcheck_table}.id"
> 	authorize_group_reply_query = "SELECT ${groupreply_table}.id,$ 
> {groupreply_table}.GroupName,${groupreply_table}.Attribute,$ 
> {groupreply_table}.Value,${groupreply_table}.op  FROM $ 
> {groupreply_table},${usergroup_table} WHERE $ 
> {usergroup_table}.Username = '%{SQL-User-Name}' AND $ 
> {usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY  
> ${groupreply_table}.id"
>
> 	 
> #######################################################################
> 	#  Accounting Queries
> 	 
> #######################################################################
> 	 
> #######################################################################
> 	accounting_onoff_query = "UPDATE ${acct_table1} SET  
> AcctStopTime='%S', AcctSessionTime=unix_timestamp('%S') -  
> unix_timestamp(AcctStartTime), AcctTerminateCause='%{Acct-Terminate- 
> Cause}', AcctStopDelay = '%{Acct-Delay-Time}' WHERE  
> AcctSessionTime=0 AND AcctStopTime=0 AND NASIPAddress= '%{NAS-IP- 
> Address}' AND AcctStartTime <= '%S'"
>
> 	accounting_update_query = "UPDATE ${acct_table1} \
>           SET FramedIPAddress = '%{Framed-IP-Address}', \
>           AcctSessionTime = '%{Acct-Session-Time}', \
>           AcctInputOctets = '%{Acct-Input-Octets}', \
>           AcctOutputOctets = '%{Acct-Output-Octets}' \
>           WHERE AcctSessionId = '%{Acct-Session-Id}' \
>           AND UserName = '%{SQL-User-Name}' \
>           AND NASIPAddress= '%{NAS-IP-Address}'"
>
> 	accounting_update_query_alt = "INSERT into ${acct_table1}  
> (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress,  
> NASPortId, NASPortType, AcctStartTime, AcctSessionTime,  
> AcctAuthentic, ConnectInfo_start, AcctInputOctets, AcctOutputOctets,  
> CalledStationId, CallingStationId, ServiceType, FramedProtocol,  
> FramedIPAddress, AcctStartDelay) values('%{Acct-Session-Id}', '% 
> {Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP- 
> Address}', '%{NAS-Port}', '%{NAS-Port-Type}', DATE_SUB('%S',INTERVAL  
> (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) SECOND), '%{Acct- 
> Session-Time}', '%{Acct-Authentic}', '', '%{Acct-Input-Octets}', '% 
> {Acct-Output-Octets}', '%{Called-Station-Id}', '%{Calling-Station- 
> Id}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP- 
> Address}', '0')"
>
> 	accounting_start_query = "INSERT into ${acct_table1}  
> (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress,  
> NASPortId, NASPortType, AcctStartTime, AcctStopTime,  
> AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop,  
> AcctInputOctets, AcctOutputOctets, CalledStationId,  
> CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol,  
> FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct- 
> Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '% 
> {Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}',  
> '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0',  
> '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service- 
> Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay- 
> Time}', '0')"
>
> 	accounting_start_query_alt  = "UPDATE ${acct_table1} SET  
> AcctStartTime = '%S', AcctStartDelay = '%{Acct-Delay-Time}',  
> ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct- 
> Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '% 
> {NAS-IP-Address}'"
>
> 	accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime =  
> '%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '% 
> {Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}',  
> AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '% 
> {Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE  
> AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User- 
> Name}' AND NASIPAddress = '%{NAS-IP-Address}'"
>
> 	accounting_stop_query_alt = "INSERT into ${acct_table2}  
> (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress,  
> NASPortId, NASPortType, AcctStartTime, AcctStopTime,  
> AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop,  
> AcctInputOctets, AcctOutputOctets, CalledStationId,  
> CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol,  
> FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct- 
> Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '% 
> {Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}',  
> DATE_SUB('%S', INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay- 
> Time:-0}) SECOND), '%S', '%{Acct-Session-Time}', '%{Acct- 
> Authentic}', '', '%{Connect-Info}', '%{Acct-Input-Octets}', '%{Acct- 
> Output-Octets}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '% 
> {Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', '% 
> {Framed-IP-Address}', '0', '%{Acct-Delay-Time}')"
>
> 	# Uncomment simul_count_query to enable simultaneous use checking
> 	# simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE  
> UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
> 	simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName,  
> NASIPAddress, NASPortId, FramedIPAddress, CallingStationId,  
> FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}'  
> AND AcctStopTime = 0"
>
> 	 
> #######################################################################
> 	# Group Membership Queries
> 	 
> #######################################################################
> 	group_membership_query = "SELECT GroupName FROM ${usergroup_table}  
> WHERE UserName='%{SQL-User-Name}'"
>
> 	 
> #######################################################################
> 	# Authentication Logging Queries
> 	 
> #######################################################################
>
> 	postauth_query = "INSERT into ${postauth_table} (id, user, pass,  
> reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap- 
> Password}', '%{reply:Packet-Type}', NOW())"
>
> 	readclients = yes
> }


Bye
Julian


Am 01.02.2008 um 07:53 schrieb Alan DeKok:

> Julian Stöver wrote:
>> Hello,
>> is there any monitoring tool for freeradius or another possibility to
>> see how many people are logged in and to do some other stuff? like  
>> the
>> monitoring tool for openvpn? Would be nice if there's something  
>> avaible!
>
>  No one is "logged in" to RADIUS.  They are logged in to a NAS, and  
> the
> NAS informs the RADIUS server (usually) that the user is logged in.
>
>  The RADIUS server puts this information into a database such as SQL,
> which can then be qeuried.  Or, you can use the "radwho" command, if
> you've enabled logging to a file in "radwtmp".
>
>  Alan DeKok.
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html





More information about the Freeradius-Users mailing list