sqlcounter help
Mordor Networks
mordor.networks at gmail.com
Thu Sep 9 09:47:59 CEST 2010
Dear Sir
this is the log in debugging mode , in query in using
query = "SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct
WHERE UserName='%{%k}'"
it works just fine
Waking up in 5 seconds...
rad_recv: Access-Request packet from host 192.168.22.2:52098, id=20,
length=114
Service-Type = Framed-User
Framed-Protocol = PPP
User-Name = "wassim"
CHAP-Challenge = 0x1d546fdf501eaf2909814861d0b4bd2fa8d51ae91946
RP-HURL = "\225\360[.\303\217v\241Ae\366\373\340\014\025\334G"
Calling-Station-Id = "00:23:CD:10:99:A7"
NAS-IP-Address =
NAS-Port = 25
Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 10
modcall[authorize]: module "preprocess" returns ok for request 10
rlm_chap: Setting 'Auth-Type := CHAP'
modcall[authorize]: module "chap" returns ok for request 10
modcall[authorize]: module "mschap" returns noop for request 10
rlm_realm: No '@' in User-Name = "wassim", looking up realm NULL
rlm_realm: No such realm "NULL"
modcall[authorize]: module "suffix" returns noop for request 10
radius_xlat: 'wassim'
rlm_sql (sql): sql_set_user escaped user --> 'wassim'
radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM
radcheck WHERE Username = 'wassim' ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 1
rlm_sql_mysql: query: SELECT id, UserName, Attribute, Value, op
FROM radcheck WHERE Username = 'wassim' ORDER BY id
radius_xlat: 'SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
FROM radgroupcheck,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
rlm_sql_mysql: query: SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
FROM radgroupcheck,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id
radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM
radreply WHERE Username = 'wassim' ORDER BY id'
rlm_sql_mysql: query: SELECT id, UserName, Attribute, Value, op
FROM radreply WHERE Username = 'wassim' ORDER BY id
radius_xlat: 'SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
FROM radgroupreply,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql_mysql: query: SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
FROM radgroupreply,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id
rlm_sql (sql): Released sql socket id: 1
modcall[authorize]: module "sql" returns ok for request 10
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand: 'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
FROM radacct WHERE UserName='%{User-Name}''
radius_xlat: 'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName='wassim''
sqlcounter_expand: '%{sql:SELECT
(SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='wassim'}'
radius_xlat: Running registered xlat function of module sql for string
'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='wassim''
rlm_sql (sql): - sql_xlat
radius_xlat: 'wassim'
rlm_sql (sql): sql_set_user escaped user --> 'wassim'
radius_xlat: 'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName='wassim''
radius_xlat: '/usr/local/var/log/radius/sqltrace.sql'
rlm_sql (sql): Reserving sql socket id: 0
rlm_sql_mysql: query: SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
FROM radacct WHERE UserName='wassim'
rlm_sql (sql): - sql_xlat finished
rlm_sql (sql): Released sql socket id: 0
radius_xlat: '2080402569'
rlm_sqlcounter: (Check item - counter) is less than zero
rlm_sqlcounter: Rejected user wassim, check_item=1000, counter=2080402569
modcall[authorize]: module "chillispot_max_bytes_daily" returns reject for
request 10
modcall: leaving group authorize (returns reject) for request 10
Delaying request 10 for 5 seconds
Finished request 10
Going to the next request
Now if i replace the query with :
sqlcounter chillispot_max_bytes_daily {
counter-name = ChilliSpot-Max-Total-Octets
check-name = ChilliSpot-Max-Total-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = daily
query = "SELECT SUM(AcctInputOctets - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets -GREATEST
((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE
UserName='%{%k}' AND AcctStartTime > FROM_UNIXTIME('%b')"
#query = "SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct
WHERE UserName='%{%k}'"
}
which should reset daily , it stops working and user can connect normally
and it says i have mysql error, MySQL version 5.0.67
here is the log :
rad_recv: Access-Request packet from host 192.168.22.2:58494, id=78,
length=111
Service-Type = Framed-User
Framed-Protocol = PPP
User-Name = "wassim"
CHAP-Challenge = 0xb358b401d225b450c30961eb7a583fee0788f8
RP-HURL = "{y\311\332n_"\271\345\016|\334`g\2531\326"
Calling-Station-Id = "00:23:CD:10:99:A7"
NAS-IP-Address =
NAS-Port = 43
Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 3
modcall[authorize]: module "preprocess" returns ok for request 3
rlm_chap: Setting 'Auth-Type := CHAP'
modcall[authorize]: module "chap" returns ok for request 3
modcall[authorize]: module "mschap" returns noop for request 3
rlm_realm: No '@' in User-Name = "wassim", looking up realm NULL
rlm_realm: No such realm "NULL"
modcall[authorize]: module "suffix" returns noop for request 3
radius_xlat: 'wassim'
rlm_sql (sql): sql_set_user escaped user --> 'wassim'
radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM
radcheck WHERE Username = 'wassim' ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 3
rlm_sql_mysql: query: SELECT id, UserName, Attribute, Value, op
FROM radcheck WHERE Username = 'wassim' ORDER BY id
radius_xlat: 'SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
FROM radgroupcheck,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
rlm_sql_mysql: query: SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
FROM radgroupcheck,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id
radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM
radreply WHERE Username = 'wassim' ORDER BY id'
rlm_sql_mysql: query: SELECT id, UserName, Attribute, Value, op
FROM radreply WHERE Username = 'wassim' ORDER BY id
radius_xlat: 'SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
FROM radgroupreply,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql_mysql: query: SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
FROM radgroupreply,usergroup WHERE usergroup.Username = 'wassim' 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 3
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand: 'SELECT SUM(AcctInputOctets - GREATEST((1283979600 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets
-GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct
WHERE UserName='%{User-Name}' AND AcctStartTime >
FROM_UNIXTIME('1283979600')'
radius_xlat: 'SELECT SUM(AcctInputOctets - GREATEST((1283979600 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets
-GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct
WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('1283979600')'
sqlcounter_expand: '%{sql:SELECT SUM(AcctInputOctets - GREATEST((1283979600
- UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets
-GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct
WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('1283979600')}'
radius_xlat: Running registered xlat function of module sql for string
'SELECT SUM(AcctInputOctets - GREATEST((1283979600 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets
-GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct
WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('12839796'
rlm_sql (sql): - sql_xlat
radius_xlat: 'wassim'
rlm_sql (sql): sql_set_user escaped user --> 'wassim'
radius_xlat: 'SELECT SUM(AcctInputOctets - GREATEST((1283979600 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets
-GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct
WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('12839796'
radius_xlat: '/usr/local/var/log/radius/sqltrace.sql'
rlm_sql (sql): Reserving sql socket id: 2
rlm_sql_mysql: query: SELECT SUM(AcctInputOctets - GREATEST((1283979600 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets
-GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct
WHERE UserName='wassim' AND AcctStartTime > FROM_UNIXTIME('12839796
rlm_sql_mysql: MYSQL check_error: 1064 received
rlm_sql (sql): database query error, SELECT SUM(AcctInputOctets -
GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)), 0)) +
SUM(AcctOutputOctets -GREATEST((1283979600 - UNIX_TIMESTAMP(AcctStartTime)),
0)) FROM radacct WHERE UserName='wassim' AND AcctStartTime >
FROM_UNIXTIME('12839796: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near ''12839796' at line 1
rlm_sql (sql): Released sql socket id: 2
radius_xlat: '00')'
rlm_sqlcounter: (Check item - counter) is greater than zero
rlm_sqlcounter: Authorized user wassim, check_item=1000, counter=0
rlm_sqlcounter: Sent Reply-Item for user wassim, Type=Session-Timeout,
value=1000
modcall[authorize]: module "chillispot_max_bytes_daily" returns ok for
request 3
modcall: leaving group authorize (returns ok) for request 3
rad_check_password: Found Auth-Type CHAP
auth: type "CHAP"
Processing the authenticate section of radiusd.conf
modcall: entering group CHAP for request 3
rlm_chap: login attempt by "wassim" with CHAP password
rlm_chap: Using clear text password nokia for user wassim authentication.
rlm_chap: chap user wassim authenticated succesfully
modcall[authenticate]: module "chap" returns ok for request 3
modcall: leaving group CHAP (returns ok) for request 3
Processing the session section of radiusd.conf
modcall: entering group session for request 3
radius_xlat: 'wassim'
rlm_sql (sql): sql_set_user escaped user --> 'wassim'
radius_xlat: 'SELECT COUNT(*) FROM radacct WHERE UserName='wassim' AND
AcctStopTime = 0'
rlm_sql (sql): Reserving sql socket id: 1
rlm_sql_mysql: query: SELECT COUNT(*) FROM radacct WHERE UserName='wassim'
AND AcctStopTime = 0
rlm_sql (sql): Released sql socket id: 1
modcall[session]: module "sql" returns ok for request 3
modcall: leaving group session (returns ok) for request 3
Processing the post-auth section of radiusd.conf
modcall: entering group post-auth for request 3
rlm_sql (sql): Processing sql_postauth
radius_xlat: 'wassim'
rlm_sql (sql): sql_set_user escaped user --> 'wassim'
radius_xlat: 'INSERT into radpostauth (id, user, pass, reply, date) values
('', 'wassim', '', 'Access-Accept', NOW())'
radius_xlat: '/usr/local/var/log/radius/sqltrace.sql'
rlm_sql (sql) in sql_postauth: query is INSERT into radpostauth (id, user,
pass, reply, date) values ('', 'wassim', '', 'Access-Accept', NOW())
rlm_sql (sql): Reserving sql socket id: 0
rlm_sql_mysql: query: INSERT into radpostauth (id, user, pass, reply, date)
values ('', 'wassim', '', 'Access-Accept', NOW())
rlm_sql (sql): Released sql socket id: 0
modcall[post-auth]: module "sql" returns ok for request 3
modcall: leaving group post-auth (returns ok) for request 3
Sending Access-Accept of id 78 to 192.168.22.2 port 58494
Session-Timeout = 1000
Framed-IP-Address = 255.255.255.254
Framed-MTU = 1492
Idle-Timeout = 60
RP-Upstream-Speed-Limit := 512
RP-Downstream-Speed-Limit := 2048
ChilliSpot-Max-Total-Octets := 100
Finished request 3
Going to the next request
--- Walking the entire request list ---
Waking up in 1 seconds...
rad_recv: Accounting-Request packet from host 192.168.22.2:39126, id=79,
length=117
Acct-Session-Id = "4C8890FA157000"
User-Name = "wassim"
Acct-Status-Type = Start
Service-Type = Framed-User
Framed-Protocol = PPP
Calling-Station-Id = "00:23:CD:10:99:A7"
Acct-Authentic = RADIUS
NAS-Port-Type = Async
Framed-IP-Address = 192.1.10.228
NAS-IP-Address = 77.246.69.163
NAS-Port = 43
Acct-Delay-Time = 0
Processing the preacct section of radiusd.conf
modcall: entering group preacct for request 4
modcall[preacct]: module "preprocess" returns noop for request 4
rlm_realm: No '@' in User-Name = "wassim", looking up realm NULL
rlm_realm: No such realm "NULL"
modcall[preacct]: module "suffix" returns noop for request 4
radius_xlat: '/usr/bin/php /root/1stlogin.php wassim 00:23:CD:10:99:A7 '
Exec-Program: /usr/bin/php /root/1stlogin.php wassim 00:23:CD:10:99:A7
PHP Warning: Unknown(): Unable to load dynamic library
'/usr/lib/php/extensions/imap.so' - /usr/lib/php/extensions/imap.so: cannot
open shared object file: No such file or directory in Unknown on line 0
PHP Warning: Unknown(): Unable to load dynamic library
'/usr/lib/php/extensions/ldap.so' - libldap-2.2.so.7: cannot open shared
object file: No such file or directory in Unknown on line 0
PHP Warning: Unknown(): Unable to load dynamic library
'/usr/lib/php/extensions/mcrypt.so' - libmcrypt.so.4: cannot open shared
object file: No such file or directory in Unknown on line 0
PHP Warning: Unknown(): Unable to load dynamic library
'/usr/lib/php/extensions/odbc.so' - libodbc.so.1: cannot open shared object
file: No such file or directory in Unknown on line 0
PHP Warning: Unknown(): Unable to load dynamic library
'/usr/lib/php/extensions/pgsql.so' - libpq.so.3: cannot open shared object
file: No such file or directory in Unknown on line 0
PHP Warning: Unknown(): Unable to load dynamic library
'/usr/lib/php/extensions/snmp.so' - libnetsnmp.so.5: cannot open shared
object file: No such file or directory in Unknown on line 0
Exec-Program output: No Mac Binding
Exec-Program-Wait: plaintext: No Mac Binding
Exec-Program: returned: 0
modcall[preacct]: module "1stlogin" returns ok for request 4
modcall: leaving group preacct (returns ok) for request 4
Processing the accounting section of radiusd.conf
modcall: entering group accounting for request 4
rlm_acct_unique: Hashing 'NAS-Port = 43,Client-IP-Address =
192.168.22.2,NAS-IP-Address = 77.246.69.163,Acct-Session-Id =
"4C8890FA157000",User-Name = "wassim"'
rlm_acct_unique: Acct-Unique-Session-ID = "835b98f7bb6d18ff".
modcall[accounting]: module "acct_unique" returns ok for request 4
radius_xlat: '/usr/local/var/log/radius/radacct/
192.168.22.2/detail-20100909'
rlm_detail:
/usr/local/var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands
to /usr/local/var/log/radius/radacct/192.168.22.2/detail-20100909
modcall[accounting]: module "detail" returns ok for request 4
modcall[accounting]: module "unix" returns ok for request 4
radius_xlat: 'wassim'
rlm_sql (sql): sql_set_user escaped user --> 'wassim'
radius_xlat: '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('4C8890FA157000', '835b98f7bb6d18ff',
'wassim', '', '77.246.69.163', '43', 'Async', '2010-09-09 10:42:20', '0',
'0', 'RADIUS', '', '', '0', '0', '', '00:23:CD:10:99:A7', '', 'Framed-User',
'PPP', '192.1.10.228', '0', '0')'
radius_xlat: '/usr/local/var/log/radius/sqltrace.sql'
rlm_sql (sql): Reserving sql socket id: 9
rlm_sql_mysql: 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) values('4C8890FA157000',
'835b98f7bb6d18ff', 'wassim', '', '77.246.69.163', '43', 'Async',
'2010-09-09 10:42:20', '0', '0', 'RADIUS', '', '', '0', '0', '',
'00:23:CD:10:99:A7', '', 'Framed-User', 'PPP', '192.1.10.228', '0', '0')
rlm_sql (sql): Released sql socket id: 9
modcall[accounting]: module "sql" returns ok for request 4
modcall: leaving group accounting (returns ok) for request 4
Sending Accounting-Response of id 79 to 192.168.22.2 port 39126
Finished request 4
Going to the next request
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20100909/8c82fc72/attachment.html>
More information about the Freeradius-Users
mailing list