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