Syed Jahanzaib Personal Blog to Share Knowledge !

February 19, 2018

FREERADIUS WITH MIKROTIK – Part #12 – Happy Hours !

Filed under: freeradius — Tags: , — Syed Jahanzaib / Pinochio~:) @ 11:40 AM

fre2

happy hours

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …


* SCENARIO:

We have a full working freeradius based billing system which have multiple Quota base services assigned to users. All users accounting is stored in radacct table as usual, but we have a separate column named qt_used in users table which is updates via radacct trigger when accounting updates arrives from the NAS. This column is checked by an external script every 10 minutes & if it finds qt_used value above then qt_total then it simply change user group to EXPIRED group & disconnects users from NAS by sending COA / POD.

* TASK:

Our bandwidth remains empty in night therefore we want to provide some extra benefits to quota base users by BYPASSING quota limit & counting in specific late night hours.

Example: There should be no QUOTA counting for all quota base users from 00:00 (midnight) till 8am (morning).

* Possible SOLUTION !

Like always, this may not be best optimal solution to achieve the task, but still it may work. Be sure took for other better approach rather then this sol.

Following is our USERS table

mysql> describe users;
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | UNI | NULL | |
| password | varchar(32) | NO | | NULL | |
| firstname | text | NO | | NULL | |
| lastname | text | NO | | NULL | |
| email | text | NO | | NULL | |
| mobile | text | NO | | NULL | |
| cnic | text | NO | | NULL | |
| srvname | text | NO | | NULL | |
| srvid | int(3) | NO | | NULL | |
| expiration | date | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| bwpkg | varchar(256) | NO | | NULL | |
| is_enabled | int(1) | NO | | NULL | |
| is_days_expired | int(1) | NO | | NULL | |
| is_qt_expired | int(1) | NO | | NULL | |
| is_uptime_expired | int(1) | NO | | NULL | |
| qt_used | varchar(20) | NO | | NULL | |
| uptime_used | varchar(32) | NO | | NULL | |
| owner | text | NO | | NULL | |
| vlanid | varchar(32) | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
22 rows in set (0.00 sec)

& following is our services table

mysql> describe services;
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| srvid | int(10) | NO | PRI | NULL | auto_increment |
| srvname | varchar(128) | NO | MUL | NULL | |
| descr | varchar(128) | YES | | other | |
| enabled | varchar(1) | NO | | NULL | |
| expdays | varchar(3) | NO | | NULL | |
| dlimit | varchar(30) | NO | | NULL | |
| ulimit | varchar(30) | NO | | NULL | |
| qt_enabled | tinyint(1) | NO | | NULL | |
| tot_qt | int(128) | NO | | NULL | |
| free_quota_enabled | tinyint(4) | NO | | NULL | |
| free_qt_start_time | time | NO | | NULL | |
| free_qt_end_time | time | NO | | NULL | |
| ippool | varchar(30) | NO | | NULL | |
| createdon | datetime | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
14 rows in set (0.00 sec)

* MYSQL TRIGGER on RADACCT table for AFTER UPDATE ACTION. [ This is our magical code 😛 ]

Ok, now we will create a TRIGGER on radacct table for AFTER UPDATE action.
[apply it in mysql shell on radius table]

BEGIN
SET @ctime = (select current_time());
SET @srvid = (select srvid from users where username =New.Username);
SET @st = (select free_qt_start_time from services where srvid=@srvid);
SET @et = (select free_qt_end_time from services where srvid=@srvid);
if (@ctime > @st)
OR (@ctime < @et)
then
UPDATE users set qt_used = qt_used + NEW.acctoutputoctets WHERE username = New.username;
# below section ELSE is not required, I just added it for ON THE FLY troubleshooting]
else
insert into log (data,msg) values ('zaib','happy_hours_time_not_matched');
END IF;
END
# Syed Jahanzaib / aacable at hotmail dot com

Current time is 11:00{am}, inspect the mysql.log & you will find following (quota will not update because time doesnt matches)

2018-02-19T06:18:26.797015Z 51 Query UPDATE radacct SET framedipaddress = '192.168.50.255', acctsessiontime = '1841', acctinputoctets = '0' > 32 | '122655', acctoutputoctets = '0' > 32 | '145069' WHERE acctsessionid = '8100000e' AND username = 'zaib' AND nasipaddress = '101.11.11.253'
2018-02-19T16:18:26.797819Z 51 Query SET @ctime = (select current_time())
2018-02-19T16:18:26.797889Z 51 Query SET @srvid = (select srvid from users where username =New.Username)
2018-02-19T16:18:26.798009Z 51 Query SET @st = (select free_qt_start_time from services where srvid=@srvid)
2018-02-19T16:18:26.798092Z 51 Query SET @et = (select free_qt_end_time from services where srvid=@srvid)
2018-02-19T16:18:26.798165Z 51 Query insert into log (data,msg) values ('zaib','happy_hours_time_not_matched)

.

& if the radacct updates came in between happy hours then we will observe following (Quota will update accordingly)

2018-02-19T06:19:31.818111Z 98 Query UPDATE radacct SET framedipaddress = '192.168.50.255', acctsessiontime = '1906', acctinputoctets = '0' << 32 | '130274', acctoutputoctets = '0' << 32 | '161901' WHERE acctsessionid = '8100000e' AND username = 'zaib' AND nasipaddress = '101.11.11.253'
2018-02-19T06:19:31.818500Z 98 Query SET @ctime = (select current_time())
2018-02-19T06:19:31.818574Z 98 Query SET @srvid = (select srvid from users where username =New.Username)
2018-02-19T06:19:31.818680Z 98 Query SET @st = (select free_qt_start_time from services where srvid=@srvid)
2018-02-19T06:19:31.818758Z 98 Query SET @et = (select free_qt_end_time from services where srvid=@srvid)
2018-02-19T06:19:31.818833Z 98 Query UPDATE users set qt_used = qt_used + NEW.acctoutputoctets WHERE username = New.username

 


Regard's
Syed Jahanzaib ~

.

1 Comment »

  1. […] FREERADIUS WITH MIKROTIK – Part #12 – Happy Hours for Quota based Users […]

    Like

    Pingback by Mikrotik with Freeradius/mySQL # Part-1 | Syed Jahanzaib Personal Blog to Share Knowledge ! — February 19, 2018 @ 11:42 AM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: