I am trying to replace sqlcounter with Unland expression in Post Auth
Section. The values are successfully called but while storing in
Tmp-Interger those are stripped. Below are the logs .
As you can see from the logs that Mysql returns a value of 20989570594
But it's stored as 3557549056 for Tmp-Integer-0
The same happens to Tmp-Integer-1 due to which the expression output
becomes FALSE instead of TRUE.
Is this the limitation of Tmp-Integer as it is an 32bit int ?
##Post-Auth Section
sql
update control {
Tmp-Integer-0 := "%{sql:SELECT
IFNULL(SUM(AcctInputOctets)+SUM(AcctOutputOctets),0) \
FROM tbl_acct WHERE
UserName='%{User-Name}' \
AND
MONTH(acctstoptime) = MONTH(NOW()) \
AND YEAR(acctstoptime)
= YEAR(NOW())}"
Tmp-Integer-1 := "%{sql:SELECT
tbl_groupcheck.value from tbl_groupcheck \
JOIN tbl_usergroup on
tbl_groupcheck.groupname = tbl_usergroup.groupname \
where
tbl_usergroup.username = '%{User-Name}'}"
}
if ("%{control:Tmp-Integer-1}" > "%{control:Tmp-Integer-0}") {
update reply {
Mikrotik-Recv-Limit :=
"%{control:Tmp-Integer-1}" - "%{control:Tmp-Integer-0}"
}
}
if ("%{control:Tmp-Integer-1}" <= "%{control:Tmp-Integer-0}") {
update reply {
Reply-Message := "Fair Usage
Policy Enforced, Bandwidth Limited"
Mikrotik-Rate-Limit :=
"128K/256K 128K/256K 128K/256K 180/180 8"
}
}
##MySQL Table
mysql> SELECT IFNULL(SUM(AcctInputOctets)+SUM(AcctOutputOctets),0)
-> FROM tbl_acct WHERE UserName='10021'
-> AND MONTH(acctstoptime) = MONTH(NOW())
-> AND YEAR(acctstoptime) = YEAR(NOW());
+------------------------------------------------------+
| IFNULL(SUM(AcctInputOctets)+SUM(AcctOutputOctets),0) |
+------------------------------------------------------+
| 20989570594 |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT tbl_groupcheck.value from tbl_groupcheck
-> JOIN tbl_usergroup on tbl_groupcheck.groupname = tbl_usergroup.groupname
-> where tbl_usergroup.username = '10021';
+-------------+
| value |
+-------------+
| 20737418240 |
+-------------+
1 row in set (0.00 sec)
##RADIUS DEBUG LOG
Finished request 4.
Cleaning up request 4 ID 176 with timestamp +15
Going to the next request
Ready to process requests.
rad_recv: Access-Request packet from host XXX.XX.XX.86 port 44198,
id=236, length=132
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 56
NAS-Port-Type = Ethernet
User-Name = "10021"
Calling-Station-Id = "XX:XX:XX:XX:XX:XX"
Called-Station-Id = "Internet"
NAS-Port-Id = "LAN"
User-Password = "10021"
NAS-Identifier = "XXX.XXXXXXX"
NAS-IP-Address = XXX.XX.XX.86
# Executing section authorize from file /etc/freeradius/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
++[digest] returns noop
[suffix] No '@' in User-Name = "10021", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
[files] users: Matched entry DEFAULT at line 172
++[files] returns ok
[sql] expand: %{User-Name} -> 10021
[sql] sql_set_user escaped user --> '10021'
rlm_sql (sql): Reserving sql socket id: 3
[sql] expand: SELECT id, username, attribute, value, op
FROM tbl_check WHERE username = '%{SQL-User-Name}'
ORDER BY id -> SELECT id, username, attribute, value, op
FROM tbl_check WHERE username = '10021' ORDER BY
id
[sql] User found in radcheck table
[sql] expand: SELECT id, username, attribute, value, op
FROM tbl_reply WHERE username = '%{SQL-User-Name}'
ORDER BY id -> SELECT id, username, attribute, value, op
FROM tbl_reply WHERE username = '10021' ORDER BY
id
[sql] expand: SELECT groupname FROM tbl_usergroup
WHERE username = '%{SQL-User-Name}' ORDER BY priority ->
SELECT groupname FROM tbl_usergroup WHERE username
= '10021' ORDER BY priority
[sql] expand: SELECT id, groupname, attribute, Value, op
FROM tbl_groupcheck WHERE groupname = '%{Sql-Group}'
ORDER BY id -> SELECT id, groupname, attribute,
Value, op FROM tbl_groupcheck WHERE groupname =
'TEST-10G' ORDER BY id
[sql] User found in group TEST-10G
[sql] expand: SELECT id, groupname, attribute, value, op
FROM tbl_groupreply WHERE groupname = '%{Sql-Group}'
ORDER BY id -> SELECT id, groupname, attribute,
value, op FROM tbl_groupreply WHERE groupname =
'TEST-10G' ORDER BY id
rlm_sql (sql): Released sql socket id: 3
++[sql] returns ok
rlm_checkval: Item Name: Calling-Station-Id, Value: XX:XX:XX:BA:8A:3B
rlm_checkval: Value Name: Calling-Station-Id, Value: XX:XX:XX:BA:8A:3B
++[checkval] returns ok
[expiration] Checking Expiration time: '1 Sep 2011'
++[expiration] returns ok
++[logintime] returns noop
++[pap] returns updated
Found Auth-Type = PAP
# Executing group from file /etc/freeradius/sites-enabled/default
+- entering group PAP {...}
[pap] login attempt with password "XXXXX"
[pap] Using CRYPT password "XXXXXXXXXXXXXX"
[pap] User authenticated successfully
++[pap] returns ok
# Executing section session from file /etc/freeradius/sites-enabled/default
+- entering group session {...}
[radutmp] expand: /var/log/freeradius/radutmp ->
/var/log/freeradius/radutmp
[radutmp] expand: %{User-Name} -> 10021
++[radutmp] returns ok
# Executing section post-auth from file /etc/freeradius/sites-enabled/default
+- entering group post-auth {...}
[sql] expand: %{User-Name} -> 10021
[sql] sql_set_user escaped user --> '10021'
[sql] expand: %{User-Password} -> XXXXX
[sql] expand: INSERT INTO tbl_postauth
(username, pass, reply, authdate) VALUES (
'%{User-Name}',
'%{%{User-Password}:-%{Chap-Password}}',
'%{reply:Packet-Type}', '%S') -> INSERT INTO tbl_postauth
(username, pass, reply, authdate)
VALUES ( '10021',
'10021', 'Access-Accept', '2011-08-08
00:27:25')
rlm_sql (sql) in sql_postauth: query is INSERT INTO tbl_postauth
(username, pass, reply, authdate)
VALUES ( '10021',
'10021', 'Access-Accept',
'2011-08-08 00:27:25')
rlm_sql (sql): Reserving sql socket id: 2
rlm_sql (sql): Released sql socket id: 2
++[sql] returns ok
sql_xlat
expand: %{User-Name} -> 10021
sql_set_user escaped user --> '10021'
expand: SELECT
IFNULL(SUM(AcctInputOctets)+SUM(AcctOutputOctets),0)
FROM tbl_acct WHERE UserName='%{User-Name}'
AND MONTH(acctstoptime) = MONTH(NOW())
AND YEAR(acctstoptime) = YEAR(NOW()) -> SELECT
IFNULL(SUM(AcctInputOctets)+SUM(AcctOutputOctets),0)
FROM tbl_acct WHERE UserName='10021'
AND MONTH(acctstoptime) = MONTH(NOW())
AND YEAR(acctstoptime) = YEAR(NOW())
rlm_sql (sql): Reserving sql socket id: 1
sql_xlat finished
rlm_sql (sql): Released sql socket id: 1
expand: %{sql:SELECT
IFNULL(SUM(AcctInputOctets)+SUM(AcctOutputOctets),0)
FROM tbl_acct WHERE UserName='%{User-Name}'
AND MONTH(acctstoptime) = MONTH(NOW())
AND YEAR(acctstoptime) = YEAR(NOW())} -> 20989570594
sql_xlat
expand: %{User-Name} -> 10021
sql_set_user escaped user --> '10021'
expand: SELECT tbl_groupcheck.value from tbl_groupcheck
JOIN tbl_usergroup on tbl_groupcheck.groupname = tbl_usergroup.groupname
where tbl_usergroup.username = '%{User-Name}' -> SELECT
tbl_groupcheck.value from tbl_groupcheck
JOIN tbl_usergroup on tbl_groupcheck.groupname = tbl_usergroup.groupname
where tbl_usergroup.username = '10021'
rlm_sql (sql): Reserving sql socket id: 0
sql_xlat finished
rlm_sql (sql): Released sql socket id: 0
expand: %{sql:SELECT tbl_groupcheck.value from tbl_groupcheck
JOIN tbl_usergroup on tbl_groupcheck.groupname = tbl_usergroup.groupname
where tbl_usergroup.username = '%{User-Name}'} -> 20737418240
++[control] returns ok
++? if ("%{control:Tmp-Integer-1}" > "%{control:Tmp-Integer-0}")
expand: %{control:Tmp-Integer-1} -> 3557549056
expand: %{control:Tmp-Integer-0} -> 3809701410
? Evaluating ("%{control:Tmp-Integer-1}" > "%{control:Tmp-Integer-0}") -> FALSE
++? if ("%{control:Tmp-Integer-1}" > "%{control:Tmp-Integer-0}") -> FALSE
++? if ("%{control:Tmp-Integer-1}" <= "%{control:Tmp-Integer-0}")
expand: %{control:Tmp-Integer-1} -> 3557549056
expand: %{control:Tmp-Integer-0} -> 3809701410
? Evaluating ("%{control:Tmp-Integer-1}" <= "%{control:Tmp-Integer-0}") -> TRUE
++? if ("%{control:Tmp-Integer-1}" <= "%{control:Tmp-Integer-0}") -> TRUE
++- entering if ("%{control:Tmp-Integer-1}" <= "%{control:Tmp-Integer-0}") {...}
+++[reply] returns ok
++- if ("%{control:Tmp-Integer-1}" <= "%{control:Tmp-Integer-0}") returns ok
++[exec] returns noop
Sending Access-Accept of id 236 to XXX.XX.XX.86 port 44198
Framed-Protocol = PPP
Framed-Compression = Van-Jacobson-TCP-IP
Framed-MTU = 1472
Idle-Timeout = 300
Reply-Message = "Fair Usage Policy Enforced, Bandwidth Limited"
Mikrotik-Rate-Limit = "128K/256K 128K/256K 128K/256K 180/180 8"
Framed-Netmask = 255.255.255.0
Session-Timeout = 2071955
Finished request 5.
Going to the next request
Waking up in 4.9 seconds.
rad_recv: Accounting-Request packet from host XXX.XX.XX.86 port 45096,
id=237, length=154
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 56
NAS-Port-Type = Ethernet
User-Name = "10021"
Calling-Station-Id = "XX:XX:XX:BA:8A:3B"
Called-Station-Id = " Internet"
NAS-Port-Id = "LAN"
Acct-Session-Id = "81800034"
Framed-IP-Address = XXX.XX.XX.250
Acct-Authentic = RADIUS
Event-Timestamp = "Aug 8 2011 00:27:23 IST"
Acct-Status-Type = Start
NAS-Identifier = "XXX.XXXXXXX"
NAS-IP-Address = XXX:XX:XX.86
Acct-Delay-Time = 0
# Executing section preacct from file /etc/freeradius/sites-enabled/default
+- entering group preacct {...}
++[preprocess] returns ok
[acct_unique] Hashing 'NAS-Port = 56,Client-IP-Address =
XXX.XX.XX.86,NAS-IP-Address = XXX.XX.XX.86,Acct-Session-Id =
"81800034",User-Name = "10021"'
[acct_unique] Acct-Unique-Session-ID = "e99f1594c7c50876".
++[acct_unique] returns ok
[suffix] No '@' in User-Name = "10021", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
++[files] returns noop
# Executing section accounting from file /etc/freeradius/sites-enabled/default
+- entering group accounting {...}
[detail] expand:
/var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d ->
/var/log/freeradius/radacct/125.20.80.86/detail-20110808
[detail] /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d
expands to /var/log/freeradius/radacct/125.20.80.86/detail-20110808
[detail] expand: %t -> Mon Aug 8 00:27:25 2011
++[detail] returns ok
++[unix] returns ok
[radutmp] expand: /var/log/freeradius/radutmp ->
/var/log/freeradius/radutmp
[radutmp] expand: %{User-Name} -> 10021
++[radutmp] returns ok
[sql] expand: %{User-Name} -> 10021
[sql] sql_set_user escaped user --> '10021'
[sql] expand: %{Acct-Delay-Time} -> 0
[sql] expand: INSERT INTO tbl_acct
(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', NULL,
'0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0',
'0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '',
'%{Service-Type}', '%{Framed-Protocol}',
'%{Framed-IP-Address}',
rlm_sql (sql): Reserving sql socket id: 4
rlm_sql (sql): Released sql socket id: 4
++[sql] returns ok
++[exec] returns noop
[attr_filter.accounting_response] expand: %{User-Name} -> 10021
attr_filter: Matched entry DEFAULT at line 12
++[attr_filter.accounting_response] returns updated
Sending Accounting-Response of id 237 to XXX.XX.XX.86 port 45096
Finished request 6.
Cleaning up request 6 ID 237 with timestamp +18
Going to the next request
Waking up in 4.8 seconds.
