rlm_sqlcounter doesn't increase counter
pamela pomary
ppomary at gmail.com
Mon Feb 27 17:19:08 CET 2012
Hello Alan,
Thanks for your response. Sorry I did not include accounting module of
radiusd.conf. I have sql declared in accounting module. I have followed
examples from http://wiki.freeradius.org/Rlm_sqlcounter, but my counter
doesn't still increase. What am I missing please.
##############################
#
config on Cisco switch C2960
#############################
aaa new-model
!
!
aaa group server radius dot1x
!
aaa authentication login default group radius local
aaa authentication dot1x default group radius
aaa authorization exec default group radius if-authenticated
aaa authorization network default group radius
aaa accounting send stop-record authentication failure
aaa accounting suppress null-username
aaa accounting session-duration ntp-adjusted
aaa accounting update newinfo periodic 1
aaa accounting exec default start-stop group radius
aaa accounting network default start-stop group radius
aaa accounting connection default start-stop group radius
aaa accounting resource default start-stop-failure group radius
interface FastEthernet0/9
switchport access vlan 6
switchport mode access
authentication host-mode multi-auth
authentication port-control auto
authentication periodic
authentication timer inactivity 5
dot1x pae both
dot1x max-req 3
spanning-tree portfast
radius-server host 10.1.7.202 auth-port 1812 acct-port 1813 key "key"
###############################
/etc/raddb/sqlcounter.conf
################################
sqlcounter noresetcounter {
counter-name = Max-All-Session-Time
check-name = Max-All-Session
sqlmod-inst = sql
key = User-Name
reset = never
query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE
UserName='%{%k}'"
}
sqlcounter dailycounter {
filename = ${raddbdir}/db.daily
count-attribute = Acct-Session-Time
driver = "rlm_sqlcounter"
allowed-servicetype = Framed-User
counter-name = Daily-Session-Time
check-name = Max-Daily-Session
sqlmod-inst = sqlcca3
key = User-Name
reset = daily
query = "SELECT SUM(AcctSessionTime - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND
UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
}
sqlcounter monthlycounter {
counter-name = Monthly-Session-Time
check-name = Max-Monthly-Session
sqlmod-inst = sqlcca3
key = User-Name
reset = monthly query = "SELECT
SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0))
FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) +
AcctSessionTime > '%b'"
}
######################################################################
/etc/raddb/radius.conf
######################################################################
modules {
$INCLUDE ${confdir}/sqlcounter.conf
instantiate {
exec
expr
dailycounter
}
authorize {
preprocess
mschap
suffix
eap
files
sql
dailycounter
}
authenticate {
Auth-Type PAP {
pap
}
Auth-Type CHAP {
chap
}
Auth-Type MS-CHAP {
mschap
}
unix
eap
}
accounting {
detail
radutmp
sql
}
#######################################################################
/etc/raddb/users
#######################################################################
clare Max-Daily-Session := 120, User-Password := "password"
Auth-Type := Reject, Reply-Message = "Your time limit is
used",
Simultaneous-Use := 1,
#########################################################################
Dubug output
#########################################################################
Going to the next request
Waking up in 6 seconds...
rad_recv: Access-Request packet from host 10.1.5.4:1645, id=110, length=244
User-Name = "clare"
Service-Type = Framed-User
Framed-MTU = 1500
Called-Station-Id = "EC-30-91-1D-69-89"
Calling-Station-Id = "00-1E-33-D5-7A-68"
EAP-Message =
0x0209002b190017030100206eb3a54de149101a446ac5628da57e59a88d30e6e483ecd1910a62e906bfebc8
Message-Authenticator = 0xd0a6c72ab057beaf9af45de1cc156ad1
Cisco-AVPair = "audit-session-id=0A010504000000DE03269279"
NAS-Port-Type = Ethernet
NAS-Port = 50009
NAS-Port-Id = "FastEthernet0/9"
State = 0xdd9c47cdca777967fd16da5036327708
NAS-IP-Address = 10.1.5.4
Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 8
modcall[authorize]: module "preprocess" returns ok for request 8
modcall[authorize]: module "mschap" returns noop for request 8
rlm_realm: No '@' in User-Name = "clare", looking up realm NULL
rlm_realm: No such realm "NULL"
modcall[authorize]: module "suffix" returns noop for request 8
rlm_eap: EAP packet type response id 9 length 43
rlm_eap: No EAP Start, assuming it's an on-going EAP conversation
modcall[authorize]: module "eap" returns updated for request 8
users: Matched entry clare at line 97
modcall[authorize]: module "files" returns ok for request 8
radius_xlat: 'clare'
rlm_sql (sql): sql_set_user escaped user --> 'clare'
radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM
radcheck WHERE Username = 'clare' ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 3
rlm_sql (sql): User clare not found in radcheck
radius_xlat: 'SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
FROM radgroupcheck,usergroup WHERE usergroup.Username = 'clare' AND
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat: 'SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
FROM radgroupreply,usergroup WHERE usergroup.Username = 'clare' AND
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): User clare not found in radgroupcheck
rlm_sql (sql): Released sql socket id: 3
rlm_sql (sql): User not found
modcall[authorize]: module "sql" returns notfound for request 8
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand: 'SELECT SUM(AcctSessionTime - GREATEST((1330300800 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE
UserName='%{User-Name}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime
> '1330300800''
radius_xlat: 'SELECT SUM(AcctSessionTime - GREATEST((1330300800 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND
UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1330300800''
sqlcounter_expand: '%{sqlcca3:SELECT SUM(AcctSessionTime -
GREATEST((1330300800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct
WHERE UserName='clare' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime
> '1330300800'}'
WARNING: Attempt to use unknown xlat function, or non-existent attribute in
string %{sqlcca3:SELECT SUM(AcctSessionTime - GREATEST((1330300800 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND
UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1330300800'}
radius_xlat: ''
rlm_sqlcounter: (Check item - counter) is greater than zero
rlm_sqlcounter: Authorized user clare, check_item=120, counter=0
rlm_sqlcounter: Sent Reply-Item for user clare, Type=Session-Timeout,
value=120
modcall[authorize]: module "dailycounter" returns ok for request 8
modcall: leaving group authorize (returns updated) for request 8
rad_check_password: Found Auth-Type EAP
auth: type "EAP"
Processing the authenticate section of radiusd.conf
modcall: entering group authenticate for request 8
rlm_eap: Request found, released from the list
rlm_eap: EAP/peap
rlm_eap: processing type peap
rlm_eap_peap: Authenticate
rlm_eap_tls: processing TLS
eaptls_verify returned 7
rlm_eap_tls: Done initial handshake
eaptls_process returned 7
rlm_eap_peap: EAPTLS_OK
rlm_eap_peap: Session established. Decoding tunneled attributes.
rlm_eap_peap: Received EAP-TLV response.
rlm_eap_peap: Tunneled data is valid.
rlm_eap_peap: Success
rlm_eap: Freeing handler
modcall[authenticate]: module "eap" returns ok for request 8
modcall: leaving group authenticate (returns ok) for request 8
radius_xlat: 'Your time limit is used'
Login OK: [clare] (from client C2960_NOC_LAN1 port 50009 cli
00-1E-33-D5-7A-68)
Sending Access-Accept of id 110 to 10.1.5.4 port 1645
Reply-Message = "Your time limit is used"
Session-Timeout = 120
MS-MPPE-Recv-Key =
0xaa033a908b53d8bc3573d3b0273a3feafa798f8a29d09b78e921e86aa1d7f143
MS-MPPE-Send-Key =
0xa08c74c90d15407a2413af4c7f7a6c56fce18da60f5a33d288fbc6470349bed4
EAP-Message = 0x03090004
Message-Authenticator = 0x00000000000000000000000000000000
User-Name = "clare"
Finished request 8
Going to the next request
Waking up in 6 seconds...
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20120227/654e2ca8/attachment.html>
More information about the Freeradius-Users
mailing list