Hello, This is the debug output when I try to loggin the user abcd with Max-Daily-Session value (120), the user is disconnected after 120s but can reconnecte again on the same day (1) Received Access-Request Id 74 from 154.126.XX.XXX:40633 to 154.126.XX.XXX:1812 length 238 (1) ChilliSpot-Version = "1.3.1.4" (1) User-Name = "abcd" (1) CHAP-Challenge = 0x0a56eeec203ad7528b129d470e4eea6d (1) CHAP-Password = 0x0004dfa56cbffb8ca68189acdf0374e0e1 (1) Service-Type = Login-User (1) Acct-Session-Id = "5c37444000000003" (1) Framed-IP-Address = 192.168.5.2 (1) NAS-Port-Type = Wireless-802.11 (1) NAS-Port = 3 (1) NAS-Port-Id = "00000003" (1) Calling-Station-Id = "D0-FF-98-95-F2-93" (1) Called-Station-Id = "00-19-3B-13-C7-7E" (1) NAS-IP-Address = 10.0.0.0 (1) NAS-Identifier = "YYYYYYYY" (1) WISPr-Logoff-URL = "http://10.0.0.0:3990/logoff" (1) Message-Authenticator = 0x4bad6a09b5afe64bde2447cb7659c04e (1) # Executing section authorize from file /etc/freeradius/3.0/sites-enabled/default (1) authorize { (1) policy filter_username { (1) if (&User-Name) { (1) if (&User-Name) -> TRUE (1) if (&User-Name) { (1) if (&User-Name =~ / /) { (1) if (&User-Name =~ / /) -> FALSE (1) if (&User-Name =~ /@[^@]*@/ ) { (1) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (1) if (&User-Name =~ /\.\./ ) { (1) if (&User-Name =~ /\.\./ ) -> FALSE (1) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (1) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (1) if (&User-Name =~ /\.$/) { (1) if (&User-Name =~ /\.$/) -> FALSE (1) if (&User-Name =~ /@\./) { (1) if (&User-Name =~ /@\./) -> FALSE (1) } # if (&User-Name) = notfound (1) } # policy filter_username = notfound (1) [preprocess] = ok (1) chap: &control:Auth-Type := CHAP (1) [chap] = ok (1) [mschap] = noop (1) [digest] = noop (1) suffix: Checking for suffix after "@" (1) suffix: No '@' in User-Name = "abcd", looking up realm NULL (1) suffix: No such realm "NULL" (1) [suffix] = noop (1) eap: No EAP-Message, not doing EAP (1) [eap] = noop (1) [files] = noop (1) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (1) sql: --> abcd (1) sql: SQL-User-Name set to 'abcd' rlm_sql (sql): Reserved connection (1) (1) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (1) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'abcd' ORDER BY id (1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'abcd' ORDER BY id (1) sql: User found in radcheck table (1) sql: Conditional check items matched, merging assignment check items (1) sql: Cleartext-Password := "1234" (1) sql: Max-Daily-Session := 120 (1) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (1) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'abcd' ORDER BY id (1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'abcd' ORDER BY id (1) sql: User found in radreply table, merging reply items (1) sql: Acct-Interim-Interval := 60 (1) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (1) sql: --> SELECT groupname FROM radusergroup WHERE username = 'abcd' ORDER BY priority (1) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'abcd' ORDER BY priority (1) sql: User not found in any groups rlm_sql (sql): Released connection (1) rlm_sql (sql): Need 4 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (6), 1 of 26 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 10.1.37-MariaDB-0+deb9u1, protocol version 10 (1) [sql] = ok sqlcounter_expand: 'SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'' (1) dailycounter: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (1) dailycounter: --> abcd (1) dailycounter: SQL-User-Name set to 'abcd' rlm_sql (sql): Reserved connection (2) (1) dailycounter: Executing select query: SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = 'abcd' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600' rlm_sql (sql): Released connection (2) (1) dailycounter: EXPAND %{sql:SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'} (1) dailycounter: --> 37 (1) dailycounter: Allowing user, &control:Max-Daily-Session value (120) is greater than counter value (37) (1) dailycounter: Setting &reply:Session-Timeout value to 83 (1) [dailycounter] = ok (1) monthlytrafficcounter: WARNING: Couldn't find check attribute, control:Max-Monthly-Traffic, doing nothing... (1) [monthlytrafficcounter] = noop (1) [expiration] = noop (1) [logintime] = noop (1) pap: WARNING: Auth-Type already set. Not setting to PAP (1) [pap] = noop (1) } # authorize = ok (1) Found Auth-Type = CHAP (1) # Executing group from file /etc/freeradius/3.0/sites-enabled/default (1) Auth-Type CHAP { (1) chap: Comparing with "known good" Cleartext-Password (1) chap: CHAP user "abcd" authenticated successfully (1) [chap] = ok (1) } # Auth-Type CHAP = ok (1) # Executing section post-auth from file /etc/freeradius/3.0/sites-enabled/default (1) post-auth { (1) update { (1) No attributes updated (1) } # update = noop (1) sql: EXPAND .query (1) sql: --> .query (1) sql: Using query template 'query' rlm_sql (sql): Reserved connection (3) (1) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (1) sql: --> abcd (1) sql: SQL-User-Name set to 'abcd' (1) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') (1) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'abcd', '0x0004dfa56cbffb8ca68189acdf0374e0e1', 'Access-Accept', '2019-01-10 16:10:48') (1) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'abcd', '0x0004dfa56cbffb8ca68189acdf0374e0e1', 'Access-Accept', '2019-01-10 16:10:48') (1) sql: SQL query returned: success (1) sql: 1 record(s) updated rlm_sql (sql): Released connection (3) (1) [sql] = ok (1) [exec] = noop (1) policy remove_reply_message_if_eap { (1) if (&reply:EAP-Message && &reply:Reply-Message) { (1) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE (1) else { (1) [noop] = noop (1) } # else = noop (1) } # policy remove_reply_message_if_eap = noop (1) } # post-auth = ok (1) Sent Access-Accept Id 74 from 154.126.XX.XXX:1812 to 154.126.XX.XXX:40633 length 0 (1) Acct-Interim-Interval = 60 (1) Session-Timeout = 83 (1) Finished request Waking up in 4.9 seconds. (2) Received Accounting-Request Id 9 from 154.126.XX.XXX:57186 to 154.126.XX.XXX:1813 length 166 (2) ChilliSpot-Version = "1.3.1.4" (2) Attr-26.14559.10 = 0x00000002 (2) Event-Timestamp = "Jan 10 2019 16:10:48 EAT" (2) User-Name = "abcd" (2) Acct-Status-Type = Start (2) Acct-Session-Id = "5c37444000000003" (2) Framed-IP-Address = 192.168.5.2 (2) NAS-Port-Type = Wireless-802.11 (2) NAS-Port = 3 (2) NAS-Port-Id = "00000003" (2) Calling-Station-Id = "D0-FF-98-95-F2-93" (2) Called-Station-Id = "00-19-3B-13-C7-7E" (2) NAS-IP-Address = 10.0.0.0 (2) NAS-Identifier = "MGHotspot" (2) # Executing section preacct from file /etc/freeradius/3.0/sites-enabled/default (2) preacct { (2) [preprocess] = ok (2) policy acct_unique { (2) update request { (2) Tmp-String-9 := "ai:" (2) } # update request = noop (2) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) { (2) EXPAND %{hex:&Class} (2) --> (2) EXPAND ^%{hex:&Tmp-String-9} (2) --> ^61693a (2) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) -> FALSE (2) else { (2) update request { (2) EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}} (2) --> 7a4ab5beca09585ad042a709184aab98 (2) &Acct-Unique-Session-Id := 7a4ab5beca09585ad042a709184aab98 (2) } # update request = noop (2) } # else = noop (2) } # policy acct_unique = noop (2) suffix: Checking for suffix after "@" (2) suffix: No '@' in User-Name = "abcd", looking up realm NULL (2) suffix: No such realm "NULL" (2) [suffix] = noop (2) [files] = noop (2) } # preacct = ok (2) # Executing section accounting from file /etc/freeradius/3.0/sites-enabled/default (2) accounting { (2) detail: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d (2) detail: --> /var/log/freeradius/radacct/154.126.XX.XXX/detail-20190110 (2) detail: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/freeradius/radacct/154.126.XX.XXX/detail-20190110 (2) detail: EXPAND %t (2) detail: --> Thu Jan 10 16:10:48 2019 (2) [detail] = ok (2) [unix] = ok (2) sql: EXPAND %{tolower:type.%{Acct-Status-Type}.query} (2) sql: --> type.start.query (2) sql: Using query template 'query' rlm_sql (sql): Reserved connection (4) (2) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (2) sql: --> abcd (2) sql: SQL-User-Name set to 'abcd' (2) sql: EXPAND INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}}', '%{NAS-Port-Type}', FROM_UNIXTIME(%{integer:Event-Timestamp}), FROM_UNIXTIME(%{integer:Event-Timestamp}), NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}') (2) sql: --> INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('5c37444000000003', '7a4ab5beca09585ad042a709184aab98', 'abcd', '', '10.0.0.0', '00000003', 'Wireless-802.11', FROM_UNIXTIME(1547125848), FROM_UNIXTIME(1547125848), NULL, '0', '', '', '', '0', '0', '00-19-3B-13-C7-7E', 'D0-FF-98-95-F2-93', '', '', '', '192.168.5.2') (2) sql: Executing query: INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('5c37444000000003', '7a4ab5beca09585ad042a709184aab98', 'abcd', '', '10.0.0.0', '00000003', 'Wireless-802.11', FROM_UNIXTIME(1547125848), FROM_UNIXTIME(1547125848), NULL, '0', '', '', '', '0', '0', '00-19-3B-13-C7-7E', 'D0-FF-98-95-F2-93', '', '', '', '192.168.5.2') (2) sql: SQL query returned: success (2) sql: 1 record(s) updated rlm_sql (sql): Released connection (4) (2) [sql] = ok (2) [exec] = noop (2) attr_filter.accounting_response: EXPAND %{User-Name} (2) attr_filter.accounting_response: --> abcd (2) attr_filter.accounting_response: Matched entry DEFAULT at line 12 (2) [attr_filter.accounting_response] = updated (2) } # accounting = updated (2) Sent Accounting-Response Id 9 from 154.126.XX.XXX:1813 to 154.126.XX.XXX:57186 length 0 (2) Finished request (2) Cleaning up request packet ID 9 with timestamp +43 Waking up in 4.9 seconds. (1) Cleaning up request packet ID 74 with timestamp +43 Ready to process requests (3) Received Accounting-Request Id 10 from 154.126.XX.XXX:57186 to 154.126.XX.XXX:1813 length 214 (3) ChilliSpot-Version = "1.3.1.4" (3) Attr-26.14559.10 = 0x00000002 (3) Event-Timestamp = "Jan 10 2019 16:10:53 EAT" (3) User-Name = "abcd" (3) Acct-Input-Octets = 14548 (3) Acct-Output-Octets = 3737 (3) Acct-Input-Gigawords = 0 (3) Acct-Output-Gigawords = 0 (3) Acct-Input-Packets = 60 (3) Acct-Output-Packets = 27 (3) Acct-Session-Time = 5 (3) Acct-Terminate-Cause = User-Request (3) Acct-Status-Type = Stop (3) Acct-Session-Id = "5c37444000000003" (3) Framed-IP-Address = 192.168.5.2 (3) NAS-Port-Type = Wireless-802.11 (3) NAS-Port = 3 (3) NAS-Port-Id = "00000003" (3) Calling-Station-Id = "D0-FF-98-95-F2-93" (3) Called-Station-Id = "00-19-3B-13-C7-7E" (3) NAS-IP-Address = 10.0.0.0 (3) NAS-Identifier = "MGHotspot" (3) # Executing section preacct from file /etc/freeradius/3.0/sites-enabled/default (3) preacct { (3) [preprocess] = ok (3) policy acct_unique { (3) update request { (3) Tmp-String-9 := "ai:" (3) } # update request = noop (3) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) { (3) EXPAND %{hex:&Class} (3) --> (3) EXPAND ^%{hex:&Tmp-String-9} (3) --> ^61693a (3) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) -> FALSE (3) else { (3) update request { (3) EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}} (3) --> 7a4ab5beca09585ad042a709184aab98 (3) &Acct-Unique-Session-Id := 7a4ab5beca09585ad042a709184aab98 (3) } # update request = noop (3) } # else = noop (3) } # policy acct_unique = noop (3) suffix: Checking for suffix after "@" (3) suffix: No '@' in User-Name = "abcd", looking up realm NULL (3) suffix: No such realm "NULL" (3) [suffix] = noop (3) [files] = noop (3) } # preacct = ok (3) # Executing section accounting from file /etc/freeradius/3.0/sites-enabled/default (3) accounting { (3) detail: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d (3) detail: --> /var/log/freeradius/radacct/154.126.XX.XXX/detail-20190110 (3) detail: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/freeradius/radacct/154.126.XX.XXX/detail-20190110 (3) detail: EXPAND %t (3) detail: --> Thu Jan 10 16:10:54 2019 (3) [detail] = ok (3) [unix] = ok (3) sql: EXPAND %{tolower:type.%{Acct-Status-Type}.query} (3) sql: --> type.stop.query (3) sql: Using query template 'query' rlm_sql (sql): Reserved connection (0) (3) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (3) sql: --> abcd (3) sql: SQL-User-Name set to 'abcd' (3) sql: EXPAND UPDATE radacct SET acctstoptime = FROM_UNIXTIME(%{integer:Event-Timestamp}), acctsessiontime = %{%{Acct-Session-Time}:-NULL}, acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', acctterminatecause = '%{Acct-Terminate-Cause}', connectinfo_stop = '%{Connect-Info}' WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' (3) sql: --> UPDATE radacct SET acctstoptime = FROM_UNIXTIME(1547125853), acctsessiontime = 5, acctinputoctets = '0' << 32 | '14548', acctoutputoctets = '0' << 32 | '3737', acctterminatecause = 'User-Request', connectinfo_stop = '' WHERE AcctUniqueId = '7a4ab5beca09585ad042a709184aab98' (3) sql: Executing query: UPDATE radacct SET acctstoptime = FROM_UNIXTIME(1547125853), acctsessiontime = 5, acctinputoctets = '0' << 32 | '14548', acctoutputoctets = '0' << 32 | '3737', acctterminatecause = 'User-Request', connectinfo_stop = '' WHERE AcctUniqueId = '7a4ab5beca09585ad042a709184aab98' rlm_sql_mysql: Rows matched: 1 Changed: 1 Warnings: 0 (3) sql: SQL query returned: success (3) sql: 1 record(s) updated rlm_sql (sql): Released connection (0) rlm_sql (sql): Need 3 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (7), 1 of 25 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 10.1.37-MariaDB-0+deb9u1, protocol version 10 (3) [sql] = ok (3) [exec] = noop (3) attr_filter.accounting_response: EXPAND %{User-Name} (3) attr_filter.accounting_response: --> abcd (3) attr_filter.accounting_response: Matched entry DEFAULT at line 12 (3) [attr_filter.accounting_response] = updated (3) } # accounting = updated (3) Sent Accounting-Response Id 10 from 154.126.XX.XXX:1813 to 154.126.XX.XXX:57186 length 0 (3) Finished request (3) Cleaning up request packet ID 10 with timestamp +49 Ready to process requests Need your help please, JMLZ -----Message d'origine----- De : Freeradius-Users [mailto:freeradius-users-bounces+philemon.jaomalaza=gmail.com@lists.freeradius.org] De la part de Alan DeKok Envoyé : jeudi 10 janvier 2019 15:03 À : FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Objet : Re: sqlcounter does not work On Jan 10, 2019, at 12:36 AM, Philemon Jaomalaza <philemon.jaomalaza@gmail.com> wrote:
I still have issues on sqlcounter both time counter or data counter using sql , when a user with Max-Daily-Session login, after the time allowed, the user was disconnected but it can reconnect again on the same day.
I see this on debug mode on first login and when the user re-login again : .............. ........................ (0) dailycounter: No integer found in result string "". May be first session, setting counter to 0 (0) dailycounter: Allowing user, &control: Max-Daily-Session value (1048576) is greater than counter value (0) (0) dailycounter: Setting &reply: Session-Timeout value to 600 (0) [dailycounter] = ok ................... .....................
If only you could post the *full* debug log, so we could see what's going wrong...
It does not find the time already used and it set the counter to 0 then the user has again the alowed time. Where is my issues ?
Since you helpfully didn't post any useful information, we don't really know. Is the server receiving accounting packets? If not, that would explain it. The server has *no idea* how long a user is online, unless the NAS *tells it*. This isn't a magic way for the server to know what's going on at the NAS. The NAS has to tell it.
Someone can help me? Say me if I need to post here the full debug
ALL of the documentation says to post this. Just... follow the documentation. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html --- L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast. https://www.avast.com/antivirus