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