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