MySQL accounting issue
tnt at kalik.co.yu
tnt at kalik.co.yu
Mon Jan 21 14:22:38 CET 2008
And what accounting is it suposed to do with the failed packet???
That value (15) most likely isn't defined in the freeradius dictionary.
And even if it was no sql query is defined in sql.conf for the failed
packet. And most likely no logic in rlm_sql.
Ivan Kalik
Kalik Informatika ISP
Dana 21/1/2008, "A.smith" <a.smith at ukgrid.net> piše:
>Hi,
>
> ok this is an example of when its meant to do some accounting, in this
>example I have both detail and sql set for accounting in radiusd.conf.
>
>Nothing to do. Sleeping until we see a request.
>rad_recv: Accounting-Request packet from host 89.107.16.10:35377, id=82,
>length=145
> Acct-Status-Type = Failed
> Service-Type = IAPP-Register
> Attr-102 = 0x00000194
> Error-Cause = 32
> Event-Timestamp = "Jan 21 2008 13:05:11 GMT"
> Attr-105 = 0x61733738333035366464
> Acct-Session-Id = "7f61550435f965005994af4a0b8687ad at 89.107.16.9"
> Attr-108 = 0x38392e3130372e31362e39
> Attr-109 = 0x35303630
> NAS-Port = 5060
> Acct-Delay-Time = 0
> NAS-IP-Address = 89.107.16.10
> Processing the preacct section of radiusd.conf
>modcall: entering group preacct for request 85
> modcall[preacct]: module "preprocess" returns noop for request 85
> rlm_realm: Proxy reply, or no User-Name. Ignoring.
> modcall[preacct]: module "suffix" returns noop for request 85
> modcall[preacct]: module "files" returns noop for request 85
>modcall: leaving group preacct (returns noop) for request 85
> Processing the accounting section of radiusd.conf
>modcall: entering group accounting for request 85
>radius_xlat:
>'/usr/local/var/log/radius/radacct/89.107.16.10/detail-20080121'
>rlm_detail:
>/usr/local/var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands
>to /usr/local/var/log/radius/radacct/89.107.16.10/detail-20080121
> modcall[accounting]: module "detail" returns ok for request 85
>rlm_sql (sql): Unsupported Acct-Status-Type = 15
> modcall[accounting]: module "sql" returns noop for request 85
>modcall: leaving group accounting (returns ok) for request 85
>Sending Accounting-Response of id 82 to 89.107.16.10 port 35377
>Finished request 85
>Going to the next request
>
>
>At startup I see this from radiusd
>
>Starting - reading configuration files ...
>reread_config: reading radiusd.conf
>Config: including file: /usr/local/etc/raddb/proxy.conf
>Config: including file: /usr/local/etc/raddb/clients.conf
>Config: including file: /usr/local/etc/raddb/snmp.conf
>Config: including file: /usr/local/etc/raddb/sql.conf
> main: prefix = "/usr/local"
> main: localstatedir = "/usr/local/var"
> main: logdir = "/usr/local/var/log/radius"
> main: libdir = "/usr/local/lib"
> main: radacctdir = "/usr/local/var/log/radius/radacct"
> main: hostname_lookups = no
> main: max_request_time = 30
> main: cleanup_delay = 5
> main: max_requests = 1024
> main: delete_blocked_requests = 0
> main: port = 0
> main: allow_core_dumps = no
> main: log_stripped_names = no
> main: log_file = "/usr/local/var/log/radius/radius.log"
> main: log_auth = no
> main: log_auth_badpass = no
> main: log_auth_goodpass = no
> main: pidfile = "/usr/local/var/run/radiusd/radiusd.pid"
> main: user = "(null)"
> main: group = "(null)"
> main: usercollide = no
> main: lower_user = "no"
> main: lower_pass = "no"
> main: nospace_user = "no"
> main: nospace_pass = "no"
> main: checkrad = "/usr/local/sbin/checkrad"
> main: proxy_requests = yes
> proxy: retry_delay = 5
> proxy: retry_count = 3
> proxy: synchronous = no
> proxy: default_fallback = yes
> proxy: dead_time = 120
> proxy: post_proxy_authorize = no
> proxy: wake_all_if_all_dead = no
> security: max_attributes = 200
> security: reject_delay = 1
> security: status_server = no
> main: debug_level = 0
>read_config_files: reading dictionary
>read_config_files: reading naslist
>Using deprecated naslist file. Support for this will go away soon.
>read_config_files: reading clients
>read_config_files: reading realms
>radiusd: entering modules setup
>Module: Library search path is /usr/local/lib
>Module: Loaded expr
>Module: Instantiated expr (expr)
>Module: Loaded PAP
> pap: encryption_scheme = "crypt"
> pap: auto_header = no
>Module: Instantiated pap (pap)
>Module: Loaded CHAP
>Module: Instantiated chap (chap)
>Module: Loaded MS-CHAP
> mschap: use_mppe = yes
> mschap: require_encryption = no
> mschap: require_strong = no
> mschap: with_ntdomain_hack = no
> mschap: passwd = "(null)"
> mschap: ntlm_auth = "(null)"
>Module: Instantiated mschap (mschap)
>Module: Loaded System
> unix: cache = no
> unix: passwd = "/etc/passwd"
> unix: shadow = "/etc/shadow"
> unix: group = "/etc/group"
> unix: radwtmp = "/usr/local/var/log/radius/radwtmp"
> unix: usegroup = no
> unix: cache_reload = 600
>Module: Instantiated unix (unix)
>Module: Loaded preprocess
> preprocess: huntgroups = "/usr/local/etc/raddb/huntgroups"
> preprocess: hints = "/usr/local/etc/raddb/hints"
> preprocess: with_ascend_hack = no
> preprocess: ascend_channels_per_line = 23
> preprocess: with_ntdomain_hack = no
> preprocess: with_specialix_jetstream_hack = no
> preprocess: with_cisco_vsa_hack = no
> preprocess: with_alvarion_vsa_hack = no
>Module: Instantiated preprocess (preprocess)
>Module: Loaded SQL
> sql: driver = "rlm_sql_mysql"
> sql: server = "localhost"
> sql: port = ""
> sql: login = "root"
> sql: password = "password"
> sql: radius_db = "radius"
> sql: nas_table = "nas"
> sql: sqltrace = yes
> sql: sqltracefile = "/usr/local/var/log/radius/sqltrace.sql"
> sql: readclients = yes
> sql: deletestalesessions = yes
> sql: num_sql_socks = 5
> sql: sql_user_name = "%{User-Name}"
> sql: default_user_profile = ""
> sql: query_on_not_found = no
> sql: authorize_check_query = "SELECT id, UserName, Attribute, Value, op
> FROM radcheck WHERE Username = '%{SQL-User-Name}'
>ORDER BY id"
> sql: authorize_reply_query = "SELECT id, UserName, Attribute, Value, op
> FROM radreply WHERE Username = '%{SQL-User-Name}'
>ORDER BY id"
> sql: authorize_group_check_query = "SELECT
>radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
> FROM radgroupcheck,usergroup WHERE usergroup.Username = '%{SQL-User-Name}'
>AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id"
> sql: authorize_group_reply_query = "SELECT
>radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
> FROM radgroupreply,usergroup WHERE usergroup.Username = '%{SQL-User-Name}'
>AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id"
> sql: accounting_onoff_query = "UPDATE radacct 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'"
> sql: accounting_update_query = " UPDATE radacct SET
> FramedIPAddress = '%{Framed-IP-Address}',
>AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets
> = '%{Acct-Input-Gigawords:-0}' << 32 |
>'%{Acct-Input-Octets:-0}', AcctOutputOctets =
>'%{Acct-Output-Gigawords:-0}' << 32 |
>'%{Acct-Output-Octets:-0}' WHERE AcctSessionId =
>'%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}'
> AND NASIPAddress = '%{NAS-IP-Address}'"
> sql: accounting_update_query_alt = " INSERT INTO radacct
> (AcctSessionId, AcctUniqueId, UserName, Realm,
> NASIPAddress, NASPortId, NASPortType,
>AcctStartTime, AcctSessionTime, AcctAuthentic,
>ConnectInfo_start, AcctInputOctets, AcctOutputOctets,
>CalledStationId, CallingStationId, ServiceType,
>FramedProtocol, FramedIPAddress, AcctStartDelay,
>XAscendSessionSvrKey) 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-Gigawords:-0}' << 32 | '%{Acct-Input-Octets:-0}',
> '%{Acct-Output-Gigawords:-0}' << 32 |
>'%{Acct-Output-Octets:-0}', '%{Called-Station-Id}',
>'%{Calling-Station-Id}', '%{Service-Type}',
>'%{Framed-Protocol}', '%{Framed-IP-Address}', '0',
>'%{X-Ascend-Session-Svr-Key}')"
> sql: accounting_start_query = " INSERT INTO radacct
>(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,
>XAscendSessionSvrKey) 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}', '0', '%{X-Ascend-Session-Svr-Key}')"
> sql: accounting_start_query_alt = "UPDATE radacct 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}'"
> sql: accounting_stop_query = " UPDATE radacct SET
>AcctStopTime = '%S', AcctSessionTime =
>'%{Acct-Session-Time}', AcctInputOctets =
>'%{Acct-Input-Gigawords:-0}' << 32 |
>'%{Acct-Input-Octets:-0}', AcctOutputOctets =
>'%{Acct-Output-Gigawords:-0}' << 32 |
>'%{Acct-Output-Octets:-0}', AcctTerminateCause =
>'%{Acct-Terminate-Cause}', AcctStopDelay =
>'%{Acct-Delay-Time:-0}', ConnectInfo_stop = '%{Connect-Info}'
> WHERE AcctSessionId = '%{Acct-Session-Id}' AND
>UserName = '%{SQL-User-Name}' AND NASIPAddress =
>'%{NAS-IP-Address}'"
> sql: accounting_stop_query_alt = " INSERT INTO radacct
> (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-Gigawords:-0}' << 32 |
> '%{Acct-Input-Octets:-0}',
>'%{Acct-Output-Gigawords:-0}' << 32 |
>'%{Acct-Output-Octets:-0}', '%{Called-Station-Id}',
>'%{Calling-Station-Id}', '%{Acct-Terminate-Cause}',
> '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}',
> '0', '%{Acct-Delay-Time:-0}')"
> sql: group_membership_query = "SELECT GroupName FROM usergroup WHERE
>UserName='%{SQL-User-Name}'"
> sql: connect_failure_retry_delay = 60
> sql: simul_count_query = ""
> sql: simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName,
> NASIPAddress, NASPortId, FramedIPAddress,
> CallingStationId, FramedProtocol
> FROM radacct WHERE
>UserName='%{SQL-User-Name}' AND AcctStopTime
>= 0"
> sql: postauth_query = "INSERT into radpostauth (user, pass, reply, date)
>values ('%{User-Name}', '%{User-Password:-Chap-Password}',
>'%{reply:Packet-Type}', NOW())"
> sql: safe-characters =
>"@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
>rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
>rlm_sql (sql): Attempting to connect to root at localhost:/radius
>rlm_sql (sql): starting 0
>rlm_sql (sql): Attempting to connect rlm_sql_mysql #0
>rlm_sql_mysql: Starting connect to MySQL server for #0
>rlm_sql (sql): Connected new DB handle, #0
>rlm_sql (sql): starting 1
>rlm_sql (sql): Attempting to connect rlm_sql_mysql #1
>rlm_sql_mysql: Starting connect to MySQL server for #1
>rlm_sql (sql): Connected new DB handle, #1
>rlm_sql (sql): starting 2
>rlm_sql (sql): Attempting to connect rlm_sql_mysql #2
>rlm_sql_mysql: Starting connect to MySQL server for #2
>rlm_sql (sql): Connected new DB handle, #2
>rlm_sql (sql): starting 3
>rlm_sql (sql): Attempting to connect rlm_sql_mysql #3
>rlm_sql_mysql: Starting connect to MySQL server for #3
>rlm_sql (sql): Connected new DB handle, #3
>rlm_sql (sql): starting 4
>rlm_sql (sql): Attempting to connect rlm_sql_mysql #4
>rlm_sql_mysql: Starting connect to MySQL server for #4
>rlm_sql (sql): Connected new DB handle, #4
>rlm_sql (sql): - generate_sql_clients
>rlm_sql (sql): Query: SELECT * FROM nas
>rlm_sql (sql): Reserving sql socket id: 4
>rlm_sql_mysql: query: SELECT * FROM nas
>rlm_sql (sql): Read entry
>nasname=89.107.16.10,shortname=OpenSER,secret=password
>rlm_sql (sql): Adding client 89.107.16.10 (OpenSER) to clients list
>rlm_sql (sql): Released sql socket id: 4
>Module: Instantiated sql (sql)
>Module: Loaded realm
> realm: format = "suffix"
> realm: delimiter = "@"
> realm: ignore_default = no
> realm: ignore_null = no
>Module: Instantiated realm (suffix)
>Module: Loaded files
> files: usersfile = "/usr/local/etc/raddb/users"
> files: acctusersfile = "/usr/local/etc/raddb/acct_users"
> files: preproxy_usersfile = "/usr/local/etc/raddb/preproxy_users"
> files: compat = "no"
>Module: Instantiated files (files)
>Module: Loaded detail
> detail: detailfile =
>"/usr/local/var/log/radius/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 = "/usr/local/var/log/radius/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.
>
>
>________________________________________________
>Message sent using UK Grid Webmail 2.7.9
>
>
>-
>List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>
More information about the Freeradius-Users
mailing list