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
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html