HotSpot using Cisco WLC5508, FreeRadius, MySQL and Easyhotspot
This article is about how to create and configure HotSpot . At the same time, I wanted to describe in detail how it looks from the inside and focus on the work of freeRadius, MySQL and simple billing Easyhotspot.
So, our system will be built on Cisco WLC5508 and CentOS. To understand how it all interacts with each other, let's look at the scheme.
It all starts with the service Easyhotspot , in fact it is billing, which is controlled through a web interface. This is an open-source project and its main advantage is that it is easy to understand and manage.
')
With it, we generate username and password, which are recorded in the MySQL database and transferred to the user for use. Next, the user connects to our access point, which is controlled by the Cisco WLC controller. When you try to open any page in the browser, the user will be transferred to the authorization page, where he will enter the received credits.
After that, the controller will intercept them and, in an access-request message, will send it to the authorization server FreeRadius for checking. He will access the database, find the appropriate username and password, and answer the Wi-Fi controller that everything is OK, and will give you the time during which the user can be active. In the RADIUS terminology, the Wi-Fi controller of the Cisco WLC is a client access point for the Network Access Server ( NAS ) radius server; in the hotspot terminology, this service is also called a captive portal . The task of this service is to show the user an authorization window, transfer creditors to a radius server, get a response with attributes and adequately process them.
And although Easyhotspot was originally designed to work with the captive portal, which is called Chillispot, it doesn't really matter to us.
After the user has been authorized, the NAS must, during the user session, send to the radius server reports on the resources used by the user: the amount of time, traffic, and so on. The whole thing freeRadius will log into the database. And the next time you try to connect, the user will be checked for how much he spent and how much of that is available to him.
Recall what Radius is. This is a protocol that is conditionally divided into two parts, this is authentication / authorization and accounting. The first describes RFC 2865 , the second RFC 2866 . The fact that they work separately says that the server accepts requests for them on different ports of 1812 and 1813.
Next, we will analyze in detail how requests and responses look, and how they are processed by FreeRadius, and now we proceed to the installation. Suppose we have a CentOS 6.5 server with a configured network and internet access. Install the program for downloading files from the network:
Download and install the repository:
Update the repository and installed programs:
Install useful utilities, MySQL, Apache and php:
In the /etc/php.ini file you need to uncomment the line:
If you forget about this parameter, then when you try to open a billing page, the following message will appear:
Also in the /etc/php.ini file:
Let's make MySQL and Apache start automatically after booting the system:
Install Easyhotspot. Download the easyhotspot from here github.com/rafeequl :
Create an easyhotspot_opensource database:
In EasyHotspot there is a Chillispot page in the menu. When we click on it, we will see an error. To avoid this, remove it. To do this, delete or comment out the following lines in the /opt/EasyHotspot/htdocs/system/application/views/admin/header.php file:
Install phpMyAdmin, this is a kind of GUI for MySQL, it will help us in studying the tables:
Create a config.inc.php:
Restart Apache:
Install freerius, mysql support and utilities that will help us test the system:
Let's make freeradiu start automatically after booting the system:
In the /etc/raddb/clients.conf file in the “client localhost“ section, do the following:
In the /etc/raddb/radiusd.conf file in the “module” section, uncomment:
In the “instantiate“ section add:
In the / etc / raddb / sites-enabled / default file in the “authorize” section, uncomment “sql” and add “noresetcounter”:
Then / etc / raddb / sites-enabled / default in the “accounting” section uncomment the “sql”:
Then / etc / raddb / sites-enabled / default in the “session” section uncomment the “sql” and comment out “radutmp”:
And then / etc / raddb / sites-enabled / default in the “post-auth” section uncomment the “sql”:
In the /etc/raddb/sql/mysql/counter.conf file, at the end the “noresetcounter” counter is already defined, we will edit it:
In the /etc/raddb/sql/mysql/dialup.conf file for working with Simultaneous-Use, we uncomment the following:
In the /etc/raddb/sql.conf file in the “sql” section we do this:
Restart freeRadius:
We connect the Radius server to the WLC: SECURITY-> Authentication. SECURITY-> Accounting. When setting up, you need to enter IP and Shared Secret.
We configure the redirect with the help of which an unauthorized user will be redirected to the authorization page when requesting any page, where you need to enter your login and password.
Configure access-list. The fact is that when a user connects to the SSID, all his requests from the web browser will be redirected to the login page, but other than that, in fact, it does not limit anything.
We need to create an ACL that allows the user access only to our login page and nowhere else.
Create an SSID, on the Security-> Layer 2 tab, select None. On the Security-> Layer 3 tab, select Web Policy - Authentication, in the Preauthentication ACL drop-down list, select our previously created ACL.
Select a previously added radius server from the dropdown list.
It remains to tune the radius. Add our WLC IP and secret
vi /etc/raddb/clients.conf
Restart freeRadius:
Now the authorization page is available at the address x.xxx / easyhotspot :
Login: admin
Password: admin123
On the web interface, we have two main menus [Cashier Menu] and [Admin Menu].
In the Admin Menu we can create a Billing plan and Account plan to generate usernames / passwords based on them. Let's create a billing plan for an hour, while the voucher generated on its basis will be valid until the end of the next day from the moment of creation (Valid for = 1), and Idle Timeout will be done 5 minutes.
Go to [Cashier Menu] -> Voucher Management and generate one voucher.
Let's test our system. Using the radtest utility, we will send a request for authorization to the radius server in the same way as the NAS does. Use the generated login / password, 'easyhotspot' is the secret in the /etc/raddb/clients.conf file:
Here you may have an error:
It is connected with the fact that when sending a radius package, the utility tries to resolve the server name in order to add it to the NAS-IP-Address. If the name is not localhost, add it to / etc / hosts. So:
We see that our authorization was successful, and we received a set of attributes in response:
Great, but we did only half the work of the NAS, after authentication and authorization should be accounting. To do this, create three files with attributes. In the field User-Name = "" do not forget to specify your username.
vi start.txt:
In the field User-Name = "" do not forget to specify your username.
vi interim-update.txt:
In the field User-Name = "" do not forget to specify your username.
vi stop.txt:
Using the radclient utility, we will send a Start account package to the radius server.
radclient 127.0.0.1 auto easyhotspot -f start.txt:
An answer was received, code 5 means that everything is in order. Go to [Cashier Menu] -> Online Users.
We see our user and the start time of the session. Now let's send the interim-update account package. This time, new attributes are added to the package:
radclient 127.0.0.1 auto easyhotspot -f interim-update.txt:
Let's see Online Users.
Now we see that the user spent 11 hours and 1874 bytes from the hour that was given to him. Send an account stop package.
radclient 127.0.0.1 auto easyhotspot -f stop.txt:
Session is complete. Go to the [Cashier Menu] -> Voucher Management.
We see that our user has 59 minutes left. Time is rounded, but it is only in billing, freeRadius counts up to a second. Check:
radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot:
As you can guess from the received answer, freeRadius took away from the originally set value 3600 the user spent 30 and returned us 3570. Let's send the package for the beginning of the accounting session now.
radclient 127.0.0.1 auto easyhotspot -f start.txt:
And we will try to log in again, presenting that the user, having received access, has given his creditors to a friend.
radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot:
Bolt! Until the session is completed, no one can log in again. Finish the session.
radclient 127.0.0.1 auto easyhotspot -f stop.txt:
Let's see what happens inside freeRadius when the request is processed. Stop freeRadius:
Run freeRadius in debug mode and write the output to the log.txt file in the background.
radiusd -X> log.txt &
Send an authorization request.
radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot
Request start and end of session:
radclient 127.0.0.1 auto easyhotspot -f start.txt
radclient 127.0.0.1 auto easyhotspot -f interim-update.txt
radclient 127.0.0.1 auto easyhotspot -f stop.txt
Stop debug mode.
kill - you must have your own value, issued after the command radiusd -X> log.txt &
Let's look at the log:
FreeRadius got our package and first of all launched the authorize section described in the / etc / raddb / sites-enabled / default file
In brackets shows the modules that process our request in turn.
From the issuance of debug it is clear that five queries were made to the database to five different tables, we consider them later, but for now we understand that the user was found and everything is OK:
Go to the authentication section and here [pap] determines that User authenticated successfully. Next, the section section is started, in it [sql] it is determined whether the session is currently active, if so, then someone has already logged in and access will be denied. The request itself is described in the /etc/raddb/sql/mysql/dialup.conf file. The post-auth section logs successful authentication to the database, to the radpostauth table. After that the answer goes.
So, we see that when processing the authorization and authentication package, freeRadius works with six tables:
Authentication and authorization passed, let's deal with accounting now:
Consider the process of processing package Interim-Update. The preacct section is started , described in the / etc / raddb / sites-enabled / default file:
The accounting section starts :
. , , freeRadius :
.../easyhotspot/phpmyadmin GUI. easyhotspot_opensource. freeRadius:
, radcheck.
. Cleartext-Password , Expiration .
[pap] authenticate , Expiration [expiration] authorize .
, easyhotspot. radcheck , , radreply , .
WISPr-Session-Terminate-Time := «2016-1-6T24:00:00». , . - , radreply .
radusergroup username , groupname .
radgroupreply groupname , , .
And here we see the remaining three attributes that came to us in response from freeRadius. What if we want to have attributes in the group not only for sending, but also for checking? This is where the radgroupcheck table comes to the rescue.
In it, we see the Session-Timeout, which is checked by the [noresetcounter] module in the authorize and Simultaneous-Use sections, which is processed by the [sql] module in the session section. value = 1 just the same and tells the radius that there can be only one active session at a time, all subsequent attempts to log in will receive a rejection.
easyhotspot. - 'Generate Voucher', easyhotspot . radpostauth [sql] post-auth .
. radacct, freeRadius' . , . freeRadius NAS.
, , NAS-freeRadius-MySQL-Easyhotspot . «Online Users».
-, ? , , / . SSID, , , User-Name username .
? User-Name -. , , SSID , , radacct. , , . freeRadius', unlang .
/etc/raddb/sites-enabled/default preacct :
, User-Name :
«reject», . :
It works, while remembering that when creating creditors, we ourselves should not fall under these conditions, otherwise the user is authorized, but we will not see an account from him. Let's see how our Online Users table now looks.
Mac addresses in it are no longer observed, but it is clear that some sessions are clearly frozen. Check the radacct table, see what happened to the “detpis7” user.
And with his session, everything is just fine. We see that it started 2015-12-24 18:18:00, and ended 2015-12-24 19:11:37
Why then does it hang in our billing with the start time 2015-12-24 18:18:00 ? Let's see how the database query is formed. This happens in the /opt/EasyHotspot/htdocs/system/application/models/onlineusermodel.php file:
, acctstoptime NULL. .
. , . start, . accounting_update_query /etc/raddb/sql/mysql/dialup.conf , freeRadius accounting_update_query_alt , .
Secondly, we see that the session start time within one session changes by one second. How does this work? The NAS only sends Acct-Session-Time, but acctstarttime for the radacct freeRadius table counts itself. Let's look at the accounting_update_query_alt request in the /etc/raddb/sql/mysql/dialup.conf file:
, , Acct-Session-Time (Acct-Delay-Time 0) .
: 12.00.00, 12.00.20 NAS Interim-update, 12.00.20 12.00.00, 20 Acct-Session-Time = 20 freeRadius'. , , MySQL DATE_SUB(date,INTERVAL expr type) 12.00.20 20, 12.00.00 acctstarttime.
20 NAS , Acct-Session-Time = 40 - 12.00.41, 12.00.41 — 40 = 12.00.01 radacct acctstarttime = 12.00.01.
. , MAX(acctstarttime) acctstoptime = NULL .
Duration acctsessiontime , . , , .
:
vi /opt/EasyHotspot/htdocs/system/application/models/onlineusermodel.php
username, acctuniqueid. . MAX(acctstarttime) , radacctid, , acctsessiontime 60, acctoutputoctets acctinputoctets stop = NULL.
, . framedipaddress, IP- . .
vi /opt/EasyHotspot/htdocs/system/application/views/onlineusers_view.php
Consider how data is requested for the [Cashier Menu] -> Voucher Management page.
vi /opt/EasyHotspot/htdocs/system/application/models/vouchermodel.php +22
There is a query to the table voucher_list In fact, this is not a table, but a view . Its description, like for all created tables, can be found in the /opt/EasyHotspot/install/database_with_sample.sql file.
The point is that the view collects data from other tables, as in a normal query, and we refer to the view as a normal table.
If we view our presentation as a normal request, we need to redo it like this:
, acctsessiontime, acctoutputoctets acctinputoctets .
: radacct , . . , , .
, . . voucher_list_0 voucher_list:
:
:
[ Cashier Menu ] -> Postpaid. :
, `postpaid_account_list` :
postpaid_account_list_0 postpaid_account_list:
:
:
, freeRadius radacct. [noresetcounter], /etc/raddb/sql/mysql/counter.conf.
vi /etc/raddb/sql/mysql/counter.conf +110
. noresetcounter, . , 3600 , . , , , .
query. , . , . AcctSessionTime , . mysql IFNULL, , 0.
on
, session [sql] /etc/raddb/sql/mysql/dialup.conf «Simultaneous Use Checking Queries».
. , radgroupcheck Simultaneous-Use := 1, .
detpis7:
simul_count_query? , :
, detpis7 . :
vi /etc/raddb/sql/mysql/dialup.conf +300
mysql:
, hotspot .
So, our system will be built on Cisco WLC5508 and CentOS. To understand how it all interacts with each other, let's look at the scheme.
It all starts with the service Easyhotspot , in fact it is billing, which is controlled through a web interface. This is an open-source project and its main advantage is that it is easy to understand and manage.
')
With it, we generate username and password, which are recorded in the MySQL database and transferred to the user for use. Next, the user connects to our access point, which is controlled by the Cisco WLC controller. When you try to open any page in the browser, the user will be transferred to the authorization page, where he will enter the received credits.
After that, the controller will intercept them and, in an access-request message, will send it to the authorization server FreeRadius for checking. He will access the database, find the appropriate username and password, and answer the Wi-Fi controller that everything is OK, and will give you the time during which the user can be active. In the RADIUS terminology, the Wi-Fi controller of the Cisco WLC is a client access point for the Network Access Server ( NAS ) radius server; in the hotspot terminology, this service is also called a captive portal . The task of this service is to show the user an authorization window, transfer creditors to a radius server, get a response with attributes and adequately process them.
And although Easyhotspot was originally designed to work with the captive portal, which is called Chillispot, it doesn't really matter to us.
After the user has been authorized, the NAS must, during the user session, send to the radius server reports on the resources used by the user: the amount of time, traffic, and so on. The whole thing freeRadius will log into the database. And the next time you try to connect, the user will be checked for how much he spent and how much of that is available to him.
Recall what Radius is. This is a protocol that is conditionally divided into two parts, this is authentication / authorization and accounting. The first describes RFC 2865 , the second RFC 2866 . The fact that they work separately says that the server accepts requests for them on different ports of 1812 and 1813.
Next, we will analyze in detail how requests and responses look, and how they are processed by FreeRadius, and now we proceed to the installation. Suppose we have a CentOS 6.5 server with a configured network and internet access. Install the program for downloading files from the network:
yum install wget Download and install the repository:
wget http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm rpm -ivh epel-release-6-8.noarch.rpm Update the repository and installed programs:
yum repolist yum update Install useful utilities, MySQL, Apache and php:
yum install mc vim unzip gcc gcc-c++ make git svn nano yum install mysql-server php httpd php-mysql php-xml php-gd php-pear php-db yum install patch mod_ssl openssl dnsmasq In the /etc/php.ini file you need to uncomment the line:
short_open_tag = On If you forget about this parameter, then when you try to open a billing page, the following message will appear:
EasyHotSpot config->item('EASYHOTSPOT_VERSION');?> load->view($this->config->item('FAL_template_dir').'template/menu');?> EasyHotspot - Hotspot Management System GNU Public License Also in the /etc/php.ini file:
date.timezone = Europe/Moscow Let's make MySQL and Apache start automatically after booting the system:
chkconfig --level 235 httpd on chkconfig --level 235 mysqld on chkconfig --level 235 dnsmasq on Install Easyhotspot. Download the easyhotspot from here github.com/rafeequl :
cd /opt git clone https://github.com/rafeequl/EasyHotspot ln -s /opt/EasyHotspot/htdocs /var/www/html/easyhotspot Create an easyhotspot_opensource database:
mysql mysql> create database easyhotspot_opensource; mysql> CREATE USER 'easyhotspot'@'localhost'; mysql> SET PASSWORD FOR 'easyhotspot'@'localhost' = PASSWORD('easyhotspot'); mysql> GRANT ALL ON easyhotspot_opensource.* to 'easyhotspot'@'localhost'; mysql> quit mysql -u root easyhotspot_opensource < /opt/EasyHotspot/install/database_with_sample.sql In EasyHotspot there is a Chillispot page in the menu. When we click on it, we will see an error. To avoid this, remove it. To do this, delete or comment out the following lines in the /opt/EasyHotspot/htdocs/system/application/views/admin/header.php file:
<!-- <li class="chillispot"><?=anchor('admin/chillispot','Chillispot')?></li> --> <!-- <li class="radius"><?=anchor('admin/freeradius','FreeRadius')?></li> --> Install phpMyAdmin, this is a kind of GUI for MySQL, it will help us in studying the tables:
wget https://files.phpmyadmin.net/phpMyAdmin/3.5.5/phpMyAdmin-3.5.5-all-languages.zip unzip phpMyAdmin-3.5.5-all-languages.zip cp phpMyAdmin-3.5.5-all-languages EasyHotspot/htdocs/phpmyadmin -rf Create a config.inc.php:
vi EasyHotspot/htdocs/phpmyadmin/config.inc.php <?php $i = 0; $i++; $cfg['ThemeDefault'] = 'original'; $cfg['Servers'][$i]['host'] = 'localhost'; $cfg['Servers'][$i]['extension'] = 'mysqli'; $cfg['Servers'][$i]['connect_type'] = 'tcp'; $cfg['Servers'][$i]['compress'] = false; $cfg['Servers'][$i]['auth_type'] = 'config'; // - phpmyadmin . , , 'config' 'http' $cfg['Servers'][$i]['user'] = 'easyhotspot'; $cfg['Servers'][$i]['password'] = 'easyhotspot'; /* End of servers configuration */ $cfg['UploadDir'] = ''; $cfg['SaveDir'] = ''; $cfg['BZipDump'] = false; $cfg['DefaultLang'] = 'ru'; $cfg['ThemeDefault'] = 'original'; $cfg['ServerDefault'] = 1; $cfg['CompressOnFly'] = false; $cfg['UserprefsDeveloperTab'] = true; $cfg['HideStructureActions'] = false; $cfg['LoginCookieDeleteAll'] = false; $cfg['QueryHistoryDB'] = true; $cfg['RetainQueryBox'] = true; $cfg['blowfish_secret'] = '51a360783193d3.45092927'; $cfg['LeftDefaultTabTable'] = 'tbl_select.php'; $cfg['MaxTableList'] = 500; ?> Restart Apache:
service httpd restart Install and configure FreeRadius
Install freerius, mysql support and utilities that will help us test the system:
yum install freeradius freeradius-mysql freeradius-utils Let's make freeradiu start automatically after booting the system:
chkconfig --level 235 radiusd on In the /etc/raddb/clients.conf file in the “client localhost“ section, do the following:
ipaddr = 127.0.0.1 secret = easyhotspot nastype = other In the /etc/raddb/radiusd.conf file in the “module” section, uncomment:
$INCLUDE sql.conf $INCLUDE sql/mysql/counter.conf In the “instantiate“ section add:
noresetcounter In the / etc / raddb / sites-enabled / default file in the “authorize” section, uncomment “sql” and add “noresetcounter”:
sql noresetcounter Then / etc / raddb / sites-enabled / default in the “accounting” section uncomment the “sql”:
sql Then / etc / raddb / sites-enabled / default in the “session” section uncomment the “sql” and comment out “radutmp”:
sql #radutmp And then / etc / raddb / sites-enabled / default in the “post-auth” section uncomment the “sql”:
sql In the /etc/raddb/sql/mysql/counter.conf file, at the end the “noresetcounter” counter is already defined, we will edit it:
sqlcounter noresetcounter { counter-name = Session-Timeout check-name = Session-Timeout reply-name = Session-Timeout sqlmod-inst = sql key = User-Name reset = never query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{${key}}'" } In the /etc/raddb/sql/mysql/dialup.conf file for working with Simultaneous-Use, we uncomment the following:
simul_count_query = "SELECT COUNT(*) \ FROM ${acct_table1} \ WHERE username = '%{SQL-User-Name}' \ AND acctstoptime IS NULL" In the /etc/raddb/sql.conf file in the “sql” section we do this:
database = "mysql" driver = "rlm_sql_${database}" server = "localhost" #port = 3306 login = "easyhotspot" password = "easyhotspot" radius_db = "easyhotspot_opensource" Restart freeRadius:
service radiusd start Configuring Cisco WLC
We connect the Radius server to the WLC: SECURITY-> Authentication. SECURITY-> Accounting. When setting up, you need to enter IP and Shared Secret.
We configure the redirect with the help of which an unauthorized user will be redirected to the authorization page when requesting any page, where you need to enter your login and password.
Configure access-list. The fact is that when a user connects to the SSID, all his requests from the web browser will be redirected to the login page, but other than that, in fact, it does not limit anything.
We need to create an ACL that allows the user access only to our login page and nowhere else.
Create an SSID, on the Security-> Layer 2 tab, select None. On the Security-> Layer 3 tab, select Web Policy - Authentication, in the Preauthentication ACL drop-down list, select our previously created ACL.
Select a previously added radius server from the dropdown list.
It remains to tune the radius. Add our WLC IP and secret
vi /etc/raddb/clients.conf
client 192.168.0.5 { # # secret and password are mapped through the "secrets" file. secret = ololo,karl! # shortname = liv1 # # the following three fields are optional, but may be used by # # checkrad.pl for simultaneous usage checks nastype = cisco # login = !root # password = someadminpas } Restart freeRadius:
service radiusd restart Now the authorization page is available at the address x.xxx / easyhotspot :
Login: admin
Password: admin123
On the web interface, we have two main menus [Cashier Menu] and [Admin Menu].
In the Admin Menu we can create a Billing plan and Account plan to generate usernames / passwords based on them. Let's create a billing plan for an hour, while the voucher generated on its basis will be valid until the end of the next day from the moment of creation (Valid for = 1), and Idle Timeout will be done 5 minutes.
Go to [Cashier Menu] -> Voucher Management and generate one voucher.
Let's test our system. Using the radtest utility, we will send a request for authorization to the radius server in the same way as the NAS does. Use the generated login / password, 'easyhotspot' is the secret in the /etc/raddb/clients.conf file:
radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot Here you may have an error:
[root@FreeRadius ~]# radtest zupvez10 palkipud 127.0.0.1 100 esyhotspot radclient:: Failed to find IP address for FreeRadius radclient: Nothing to send. [root@FreeRadius ~]# It is connected with the fact that when sending a radius package, the utility tries to resolve the server name in order to add it to the NAS-IP-Address. If the name is not localhost, add it to / etc / hosts. So:
[root@FreeRadius ~]# radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot Sending Access-Request of id 189 to 127.0.0.1 port 1812 User-Name = "zupvez10" User-Password = "palkipud" NAS-IP-Address = 127.0.0.1 NAS-Port = 100 Message-Authenticator = 0x00000000000000000000000000000000 rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=189, length=63 WISPr-Session-Terminate-Time = "2016-1-6T24:00:00" Session-Timeout = 3600 Idle-Timeout = 300 Acct-Interim-Interval = 120 [root@FreeRadius ~]# We see that our authorization was successful, and we received a set of attributes in response:
- WISPr-Session-Terminate-Time = "2016-1-6T24: 00: 00" - at midnight of the next day the session will become invalid
- Session-Timeout = 3600 - means that the NAS will have to disconnect the user in an hour
- Idle-Timeout = 300 - if within 5 minutes the user is not active, the NAS should drop the session
- Acct-Interim-Interval = 120 every two minutes the NAS should send an account package (interim-update) with a report on the current affairs of the user
Great, but we did only half the work of the NAS, after authentication and authorization should be accounting. To do this, create three files with attributes. In the field User-Name = "" do not forget to specify your username.
vi start.txt:
Packet-Type=4 Packet-Dst-Port=1813 Acct-Session-Id = "4D2BB8AC-00000098" Acct-Status-Type = Start Acct-Authentic = RADIUS User-Name = "zupvez10" NAS-Port = 0 Called-Station-Id = "00-02-6F-AA-AA-AA:My Wireless" Calling-Station-Id = "00-1C-B3-AA-AA-AA" NAS-Port-Type = Wireless-802.11 Connect-Info = "CONNECT 48Mbps 802.11b" In the field User-Name = "" do not forget to specify your username.
vi interim-update.txt:
Packet-Type=4 Packet-Dst-Port=1813 Acct-Session-Id = "4D2BB8AC-00000098" Acct-Status-Type = Interim-Update Acct-Authentic = RADIUS User-Name = "zupvez10" NAS-Port = 0 Called-Station-Id = "00-02-6F-AA-AA-AA:My Wireless" Calling-Station-Id = "00-1C-B3-AA-AA-AA" NAS-Port-Type = Wireless-802.11 Connect-Info = "CONNECT 48Mbps 802.11b" Acct-Session-Time = 11 Acct-Input-Packets = 15 Acct-Output-Packets = 3 Acct-Input-Octets = 1407 Acct-Output-Octets = 467 In the field User-Name = "" do not forget to specify your username.
vi stop.txt:
Packet-Type=4 Packet-Dst-Port=1813 Acct-Session-Id = "4D2BB8AC-00000098" Acct-Status-Type = Stop Acct-Authentic = RADIUS User-Name = "zupvez10" NAS-Port = 0 Called-Station-Id = "00-02-6F-AA-AA-AA:My Wireless" Calling-Station-Id = "00-1C-B3-AA-AA-AA" NAS-Port-Type = Wireless-802.11 Connect-Info = "CONNECT 48Mbps 802.11b" Acct-Session-Time = 30 Acct-Input-Packets = 25 Acct-Output-Packets = 7 Acct-Input-Octets = 3407 Acct-Output-Octets = 867 Acct-Terminate-Cause = User-Request Using the radclient utility, we will send a Start account package to the radius server.
radclient 127.0.0.1 auto easyhotspot -f start.txt:
[root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f start.txt Received response ID 50, code 5, length = 20 An answer was received, code 5 means that everything is in order. Go to [Cashier Menu] -> Online Users.
We see our user and the start time of the session. Now let's send the interim-update account package. This time, new attributes are added to the package:
- Acct-Session-Time = 11
- Acct-Input-Packets = 15
- Acct-Output-Packets = 3
- Acct-Input-Octets = 1407
- Acct-Output-Octets = 467
radclient 127.0.0.1 auto easyhotspot -f interim-update.txt:
[root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f interim-update.txt Received response ID 226, code 5, length = 20 Let's see Online Users.
Now we see that the user spent 11 hours and 1874 bytes from the hour that was given to him. Send an account stop package.
radclient 127.0.0.1 auto easyhotspot -f stop.txt:
[root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f stop.txt Received response ID 166, code 5, length = 20 Session is complete. Go to the [Cashier Menu] -> Voucher Management.
We see that our user has 59 minutes left. Time is rounded, but it is only in billing, freeRadius counts up to a second. Check:
radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot:
[root@FreeRadius ~]# radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot Sending Access-Request of id 93 to 127.0.0.1 port 1812 User-Name = "zupvez10" User-Password = "palkipud" NAS-IP-Address = 127.0.0.1 NAS-Port = 100 Message-Authenticator = 0x00000000000000000000000000000000 rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=93, length=63 WISPr-Session-Terminate-Time = "2016-1-6T24:00:00" Session-Timeout = 3570 Idle-Timeout = 300 Acct-Interim-Interval = 120 As you can guess from the received answer, freeRadius took away from the originally set value 3600 the user spent 30 and returned us 3570. Let's send the package for the beginning of the accounting session now.
radclient 127.0.0.1 auto easyhotspot -f start.txt:
[root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f start.txt Received response ID 15, code 5, length = 20 And we will try to log in again, presenting that the user, having received access, has given his creditors to a friend.
radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot:
Sending Access-Request of id 99 to 127.0.0.1 port 1812 User-Name = "zupvez10" User-Password = "palkipud" NAS-IP-Address = 127.0.0.1 NAS-Port = 100 Message-Authenticator = 0x00000000000000000000000000000000 rad_recv: Access-Reject packet from host 127.0.0.1 port 1812, id=99, length=68 Reply-Message = "\r\nYou are already logged in - access denied\r\n\n" Bolt! Until the session is completed, no one can log in again. Finish the session.
radclient 127.0.0.1 auto easyhotspot -f stop.txt:
[root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f stop.txt Received response ID 143, code 5, length = 20 Let's see what happens inside freeRadius when the request is processed. Stop freeRadius:
service radiusd stop Run freeRadius in debug mode and write the output to the log.txt file in the background.
radiusd -X> log.txt &
Send an authorization request.
radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot
Request start and end of session:
radclient 127.0.0.1 auto easyhotspot -f start.txt
radclient 127.0.0.1 auto easyhotspot -f interim-update.txt
radclient 127.0.0.1 auto easyhotspot -f stop.txt
Stop debug mode.
kill - you must have your own value, issued after the command radiusd -X> log.txt &
[root@FreeRadius ~]# service radiusd stop Stopping radiusd: [ OK ] [root@FreeRadius ~]# radiusd -X > log.txt & [1] 4215 [root@FreeRadius ~]# radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot Sending Access-Request of id 200 to 127.0.0.1 port 1812 User-Name = "zupvez10" User-Password = "palkipud" NAS-IP-Address = 127.0.0.1 NAS-Port = 100 Message-Authenticator = 0x00000000000000000000000000000000 rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=200, length=63 WISPr-Session-Terminate-Time = "2016-1-6T24:00:00" Session-Timeout = 3540 Idle-Timeout = 300 Acct-Interim-Interval = 120 [root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f start.txt Received response ID 68, code 5, length = 20 [root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f interim-update.txt Received response ID 142, code 5, length = 20 [root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f stop.txt Received response ID 37, code 5, length = 20 [root@FreeRadius ~]# kill 4215 [root@FreeRadius ~]# kill 4215 bash: kill: (4215) - No such process [1]+ Done radiusd -X > log.txt Let's look at the log:
less log.txt debug_Authentication and authorization
rad_recv: Access-Request packet from host 127.0.0.1 port 50024, id=200, length=78 User-Name = "zupvez10" User-Password = "palkipud" NAS-IP-Address = 127.0.0.1 NAS-Port = 100 Message-Authenticator = 0x9159a59b8e5c58fe44a95a199f84f9cf # Executing section authorize from file /etc/raddb/sites-enabled/default +group authorize { ++[preprocess] = ok ++[chap] = noop ++[mschap] = noop ++[digest] = noop [suffix] No '@' in User-Name = "zupvez10", looking up realm NULL [suffix] No such realm "NULL" ++[suffix] = noop [eap] No EAP-Message, not doing EAP ++[eap] = noop ++[files] = noop [sql] expand: %{User-Name} -> zupvez10 [sql] sql_set_user escaped user --> 'zupvez10' rlm_sql (sql): Reserving sql socket id: 31 [sql] expand: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'zupvez10' ORDER BY id [sql] User found in radcheck table [sql] expand: SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'zupvez10' ORDER BY id [sql] expand: SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM radusergroup WHER E username = 'zupvez10' ORDER BY priority [sql] expand: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute , Value, op FROM radgroupcheck WHERE groupname = '1hour' ORDER BY id [sql] User found in group 1hour [sql] expand: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute , value, op FROM radgroupreply WHERE groupname = '1hour' ORDER BY id rlm_sql (sql): Released sql socket id: 31 ++[sql] = ok rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{User-Name}'' [noresetcounter] expand: SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{User-Name}' -> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='zupvez10' WARNING: Please replace '%S' with '${sqlmod-inst}' sqlcounter_expand: '%{sql:SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='zupvez10'}' [noresetcounter] sql_xlat [noresetcounter] expand: %{User-Name} -> zupvez10 [noresetcounter] sql_set_user escaped user --> 'zupvez10' [noresetcounter] expand: SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='zupvez10' -> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='zupvez10' rlm_sql (sql): Reserving sql socket id: 30 [noresetcounter] sql_xlat finished rlm_sql (sql): Released sql socket id: 30 [noresetcounter] expand: %{sql:SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='zupvez10'} -> 60 rlm_sqlcounter: Check item is greater than query result rlm_sqlcounter: Authorized user zupvez10, check_item=3600, counter=60 rlm_sqlcounter: Sent Reply-Item for user zupvez10, Type=Session-Timeout, value=3540 ++[noresetcounter] = ok [expiration] Checking Expiration time: 'January 6 2016 24:00:00' ++[expiration] = ok ++[logintime] = noop ++[pap] = updated +} # group authorize = updated Found Auth-Type = PAP # Executing group from file /etc/raddb/sites-enabled/default +group PAP { [pap] login attempt with password "palkipud" [pap] Using clear text password "palkipud" [pap] User authenticated successfully ++[pap] = ok +} # group PAP = ok # Executing section session from file /etc/raddb/sites-enabled/default +group session { [sql] expand: %{User-Name} -> zupvez10 [sql] sql_set_user escaped user --> 'zupvez10' [sql] expand: SELECT COUNT(*) FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL -> SELECT COUNT(*) FROM radacct WHERE username = 'zupvez10' AND acctstoptime IS NULL rlm_sql (sql): Reserving sql socket id: 29 rlm_sql (sql): Released sql socket id: 29 ++[sql] = ok +} # group session = ok # Executing section post-auth from file /etc/raddb/sites-enabled/default +group post-auth { [sql] expand: %{User-Name} -> zupvez10 [sql] sql_set_user escaped user --> 'zupvez10' [sql] expand: %{User-Password} -> palkipud [sql] expand: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') -> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'zupvez10', 'palkipud', 'Access-Accept', '2016-01-05 21:20:45') rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'zupvez10', 'palkipud', 'Access-Accept', '2016-01-05 21:20:45') rlm_sql (sql): Reserving sql socket id: 28 rlm_sql (sql): Released sql socket id: 28 ++[sql] = ok ++[exec] = noop +} # group post-auth = ok Sending Access-Accept of id 200 to 127.0.0.1 port 50024 WISPr-Session-Terminate-Time := "2016-1-6T24:00:00" Session-Timeout := 3540 Idle-Timeout := 300 Acct-Interim-Interval := 120 Finished request 0. Going to the next request FreeRadius got our package and first of all launched the authorize section described in the / etc / raddb / sites-enabled / default file
In brackets shows the modules that process our request in turn.
- [preprocess] - configurable in raddb / huntgroups and raddb / hints, we do not use it
- [chap] [mschap] [eap] [pap] - authentication protocols, by default freeRadius does not know which one we will use and in the authorize section checks everything
- [digest] - used for Cisco SIP server, we don’t need this module
- [suffix] - this module parses the User-Name attribute in the realm search, thus proxying can be configured
- [files] - parses the file / raddb / users in search of users, we use the database, so we don’t care
- [sql] - checks the database in search of the user and its attributes, the queries themselves are described in the /etc/raddb/sql/mysql/dialup.conf file
From the issuance of debug it is clear that five queries were made to the database to five different tables, we consider them later, but for now we understand that the user was found and everything is OK:
- [noresetcounter] - the same module that calculates how much time the user has left is described in the /etc/raddb/sql/mysql/counter.conf file
- [expiration] - checks the expiration attribute, if the time during which the creditors are valid has expired, then no matter how much time the user has spent, the access will be denied
- [logintime] - you can set the time when creditors will work, for example, at lunch on Thursdays
- [pap] - Found Auth-Type = PAP authentication protocol defined
Go to the authentication section and here [pap] determines that User authenticated successfully. Next, the section section is started, in it [sql] it is determined whether the session is currently active, if so, then someone has already logged in and access will be denied. The request itself is described in the /etc/raddb/sql/mysql/dialup.conf file. The post-auth section logs successful authentication to the database, to the radpostauth table. After that the answer goes.
So, we see that when processing the authorization and authentication package, freeRadius works with six tables:
- radcheck
- radreply
- radusergroup
- radgroupcheck
- radgroupreply
- radpostauth
Authentication and authorization passed, let's deal with accounting now:
debug_Accounting
rad_recv: Accounting-Request packet from host 127.0.0.1 port 58851, id=142, length=177 Acct-Session-Id = "4D2BB8AC-00000098" Acct-Status-Type = Interim-Update Acct-Authentic = RADIUS User-Name = "zupvez10" NAS-Port = 0 Called-Station-Id = "00-02-6F-AA-AA-AA:My Wireless" Calling-Station-Id = "00-1C-B3-AA-AA-AA" NAS-Port-Type = Wireless-802.11 Connect-Info = "CONNECT 48Mbps 802.11b" Acct-Session-Time = 11 Acct-Input-Packets = 15 Acct-Output-Packets = 3 Acct-Input-Octets = 1407 Acct-Output-Octets = 467 # Executing section preacct from file /etc/raddb/sites-enabled/default +group preacct { ++[preprocess] = ok [acct_unique] WARNING: Attribute NAS-Identifier was not found in request, unique ID MAY be inconsistent [acct_unique] Hashing 'NAS-Port = 0,,NAS-IP-Address = 127.0.0.1,Acct-Session-Id = "4D2BB8AC-00000098",User-Name = "zupvez10"' [acct_unique] Acct-Unique-Session-ID = "55c4c93f54bb88a7". ++[acct_unique] = ok [suffix] No '@' in User-Name = "zupvez10", looking up realm NULL [suffix] No such realm "NULL" ++[suffix] = noop ++[files] = noop +} # group preacct = ok # Executing section accounting from file /etc/raddb/sites-enabled/default +group accounting { [detail] expand: %{Packet-Src-IP-Address} -> 127.0.0.1 [detail] expand: /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d -> /var/log/radius/radacct/127.0.0.1/detail-20160105 [detail] /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radius/radacct/127.0.0.1/detail-20160105 [detail] expand: %t -> Tue Jan 5 21:21:18 2016 ++[detail] = ok [sql] expand: %{User-Name} -> zupvez10 [sql] sql_set_user escaped user --> 'zupvez10' [sql] expand: %{Acct-Session-Time} -> 11 [sql] expand: %{Acct-Input-Gigawords} -> [sql] ... expanding second conditional [sql] expand: %{Acct-Input-Octets} -> 1407 [sql] expand: %{Acct-Output-Gigawords} -> [sql] ... expanding second conditional [sql] expand: %{Acct-Output-Octets} -> 467 [sql] expand: UPDATE radacct SET framedipaddress = '%{Framed-IP-Address}', acctsessiontime = '%{%{Acct-Session-Time}:-0}', acctinpu toctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' WHERE acctsessionid = '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}' AND nasipaddress = '%{NAS-IP-Address}' -> UPDATE radacct SET framedipaddress = '', acctsessiontime = '11', acctinputoctets = '0' << 32 | '1407', acctoutputoctets = '0' << 32 | '467' WHERE acctsessionid = '4D2BB8AC-00000098' AND username = 'zupvez10' AND nas rlm_sql (sql): Reserving sql socket id: 26 rlm_sql (sql): Released sql socket id: 26 ++[sql] = ok ++[exec] = noop [attr_filter.accounting_response] expand: %{User-Name} -> zupvez10 attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] = updated +} # group accounting = updated Sending Accounting-Response of id 142 to 127.0.0.1 port 58851 Finished request 2. Cleaning up request 2 ID 142 with timestamp +40 Going to the next request Consider the process of processing package Interim-Update. The preacct section is started , described in the / etc / raddb / sites-enabled / default file:
- [acct_unique] - takes some attributes from the package and calculates the hash, then writes it to the database in the radacct table, as acctuniqueid
- [suffix] - this module parses the User-Name attribute in the search for realm, so proxying can be configured
- [files] - allows, depending on the received attributes, to process the package in different ways, it is configured in the / etc / raddb / acct_users file
The accounting section starts :
- [detail] - logs the package in a text file, configured in / etc / raddb / modules / detail. We can view these logs /var/log/radius/radacct/127.0.0.1/
- [sql] - logs our query to the database in the radacct table, while we see that the UPDATE query is being used, that is, the new record is not created, but the existing one created during the processing of the start package is updated. , , [noresetcounter] section. , /etc/raddb/sql/mysql/dialup.conf accounting_update_query, , accounting_update_query_alt, accounting_update_query .
- [exec] — /etc/raddb/modules/exec
- [attr_filter.accounting_response] — , /etc/raddb/modules/attr_filter
. , , freeRadius :
- radacct
MySQL
.../easyhotspot/phpmyadmin GUI. easyhotspot_opensource. freeRadius:
, radcheck.
. Cleartext-Password , Expiration .
[pap] authenticate , Expiration [expiration] authorize .
, easyhotspot. radcheck , , radreply , .
WISPr-Session-Terminate-Time := «2016-1-6T24:00:00». , . - , radreply .
radusergroup username , groupname .
radgroupreply groupname , , .
And here we see the remaining three attributes that came to us in response from freeRadius. What if we want to have attributes in the group not only for sending, but also for checking? This is where the radgroupcheck table comes to the rescue.
In it, we see the Session-Timeout, which is checked by the [noresetcounter] module in the authorize and Simultaneous-Use sections, which is processed by the [sql] module in the session section. value = 1 just the same and tells the radius that there can be only one active session at a time, all subsequent attempts to log in will receive a rejection.
easyhotspot. - 'Generate Voucher', easyhotspot . radpostauth [sql] post-auth .
. radacct, freeRadius' . , . freeRadius NAS.
, , NAS-freeRadius-MySQL-Easyhotspot . «Online Users».
-, ? , , / . SSID, , , User-Name username .
? User-Name -. , , SSID , , radacct. , , . freeRadius', unlang .
/etc/raddb/sites-enabled/default preacct :
# Pre-accounting. Decide which accounting type to use. # preacct { if (User-Name=~ /^[A-Fa-f0-9]{12}$/) { reject } , User-Name :
- "~" — , , "//"
- "^" —
- "[A-Fa-f0-9]" — . , hex, .. -
- "{12}" — , , - .
- "$" —
«reject», . :
rad_recv: Accounting-Request packet from host 192.168.80.100 port 32770, id=138, length=246 User-Name = "6c709f251ec4" NAS-Port = 13 NAS-IP-Address = 192.168.0.5 Framed-IP-Address = 192.168.13.80 NAS-Identifier = "5508" Airespace-Wlan-Id = 2 Acct-Session-Id = "567be2a8/6c:70:9f:25:1e:c4/217989" Acct-Authentic = Remote Tunnel-Type:0 = VLAN Tunnel-Medium-Type:0 = IEEE-802 Tunnel-Private-Group-Id:0 = "13" Acct-Status-Type = Interim-Update Acct-Input-Octets = 3566315 Acct-Output-Octets = 99562740 Acct-Input-Packets = 41757 Acct-Output-Packets = 66012 Acct-Session-Time = 8345 Acct-Delay-Time = 0 Calling-Station-Id = "6c-70-9f-25-2-b2" Called-Station-Id = "28-94-0f-ae-be-13" Cisco-AVPair = "nas-update=true" # Executing section preacct from file /etc/raddb/sites-enabled/default +- entering group preacct {...} ++? if (User-Name=~ /^[A-Fa-f0-9]{12}$/) ? Evaluating (User-Name=~ /^[A-Fa-f0-9]{12}$/) -> TRUE ++? if (User-Name=~ /^[A-Fa-f0-9]{12}$/) -> TRUE ++- entering if (User-Name=~ /^[A-Fa-f0-9]{12}$/) {...} +++[reject] returns reject ++- if (User-Name=~ /^[A-Fa-f0-9]{12}$/) returns reject Finished request 7. It works, while remembering that when creating creditors, we ourselves should not fall under these conditions, otherwise the user is authorized, but we will not see an account from him. Let's see how our Online Users table now looks.
Mac addresses in it are no longer observed, but it is clear that some sessions are clearly frozen. Check the radacct table, see what happened to the “detpis7” user.
And with his session, everything is just fine. We see that it started 2015-12-24 18:18:00, and ended 2015-12-24 19:11:37
Why then does it hang in our billing with the start time 2015-12-24 18:18:00 ? Let's see how the database query is formed. This happens in the /opt/EasyHotspot/htdocs/system/application/models/onlineusermodel.php file:
return $this->db->query('select username, MAX(acctstarttime) as start, (acctstoptime) as stop, sum(acctsessiontime) as time,sum(acctoutputoctets)+sum(acctinputoctets) as packet from radacct where (acctstoptime IS NULL) group by username'); , acctstoptime NULL. .
. , . start, . accounting_update_query /etc/raddb/sql/mysql/dialup.conf , freeRadius accounting_update_query_alt , .
Secondly, we see that the session start time within one session changes by one second. How does this work? The NAS only sends Acct-Session-Time, but acctstarttime for the radacct freeRadius table counts itself. Let's look at the accounting_update_query_alt request in the /etc/raddb/sql/mysql/dialup.conf file:
DATE_SUB('%S', \ INTERVAL (%{%{Acct-Session-Time}:-0} + \ %{%{Acct-Delay-Time}:-0}) SECOND) , , Acct-Session-Time (Acct-Delay-Time 0) .
: 12.00.00, 12.00.20 NAS Interim-update, 12.00.20 12.00.00, 20 Acct-Session-Time = 20 freeRadius'. , , MySQL DATE_SUB(date,INTERVAL expr type) 12.00.20 20, 12.00.00 acctstarttime.
20 NAS , Acct-Session-Time = 40 - 12.00.41, 12.00.41 — 40 = 12.00.01 radacct acctstarttime = 12.00.01.
. , MAX(acctstarttime) acctstoptime = NULL .
Duration acctsessiontime , . , , .
:
vi /opt/EasyHotspot/htdocs/system/application/models/onlineusermodel.php
return $this->db->query('SELECT username, MAX(radacctid), MAX(acctstarttime) as start, MAX(acctstoptime) as stop, CAST(max(acctsessiontime)/60 AS UNSIGNED) as time,max(acctoutputoctets)+max(acctinputoctets) as packet, framedipaddress FROM radacct GROUP BY acctuniqueid HAVING stop IS NULL'); username, acctuniqueid. . MAX(acctstarttime) , radacctid, , acctsessiontime 60, acctoutputoctets acctinputoctets stop = NULL.
, . framedipaddress, IP- . .
vi /opt/EasyHotspot/htdocs/system/application/views/onlineusers_view.php
<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); ?> <?php $this->load->view('header') ?> <h1><?=$action?></h1> <table class="stripe"> <tbody> <tr> <th><?=$this->lang->line('username')?></th> <th>Start</th> <th>Duration</th> <th>Packet</th> <th>IP-Address</th> <th>Force Disconnect</th> </tr> <?php foreach ($onlineusers->result() as $row): ?> <tr> <td><?=$row->username;?></td> <td><?=$row->start;?></td> <td><?=$row->time;?></td> <td><?=$row->packet;?></td> <td><?=$row->framedipaddress;?></td> <td><?=anchor('onlineuser/disconnect/'.$row->username,'disconnect','class="disconnect" ')?></td> </tr> <?php endforeach;?> </tbody> </table> <? $this->load->view('footer'); ?> Consider how data is requested for the [Cashier Menu] -> Voucher Management page.
vi /opt/EasyHotspot/htdocs/system/application/models/vouchermodel.php +22
There is a query to the table voucher_list In fact, this is not a table, but a view . Its description, like for all created tables, can be found in the /opt/EasyHotspot/install/database_with_sample.sql file.
VIEW `voucher_list` AS select `v`.`id` AS `id`,`v`.`username` AS `username`,`v`.`password` AS `password`,`v`.`billingplan` AS `billingplan`,`v`.`valid_until` AS `valid_until`,`b`.`type` AS `type`,`b`.`amount` AS `amount`,`b`.`valid_for` AS `valid_for`,`b`.`price` AS `price`,(sum(`ra`.`acctsessiontime`) / 60) AS `time_used`,if((`b`.`type` = _latin1'time'),(`b`.`amount` - (sum(`ra`.`acctsessiontime`) / 60)),_latin1'null') AS `time_remain`,((sum(`ra`.`acctoutputoctets`) + sum(`ra`.`acctinputoctets`)) / 1048576) AS `packet_used`,if((`b`.`type` = _latin1'packet'),(`b`.`amount` - (sum((`ra`.`acctoutputoctets` + `ra`.`acctinputoctets`)) / 1048576)),_latin1'null') AS `packet_remain`,`v`.`isexported` AS `isexported`,`v`.`isprinted` AS `isprinted`,if((`b`.`type` = _latin1'time'),if(((sum(`ra`.`acctsessiontime`) / 60) >= `b`.`amount`),_latin1'exp',_latin1'valid'),if((((sum(`ra`.`acctoutputoctets`) + sum(`ra`.`acctinputoctets`)) / 1048576) >= `b`.`amount`),_latin1'exp',_latin1'valid')) AS `valid` from ((`voucher` `v` left join `radacct` `ra` on((`v`.`username` = `ra`.`username`))) join `billingplan` `b` on((`b`.`name` = `v`.`billingplan`))) group by `v`.`username The point is that the view collects data from other tables, as in a normal query, and we refer to the view as a normal table.
If we view our presentation as a normal request, we need to redo it like this:
select id, username, password, billingplan, valid_until, type, amount, valid_for, price, sum(time_used) AS time_used, if((type = _latin1'time'),(amount - sum(time_used)),_latin1'null') AS time_remain, sum(packet_used) AS packet_used, if((type = _latin1'packet'),((amount - sum(packet_used)) / 1048576),_latin1'null') AS packet_remain, isexported, isprinted, if((type = _latin1'time'),if((sum(time_used) >= amount),_latin1'exp',_latin1'valid'),if((sum(packet_used) >= amount),_latin1'exp',_latin1'valid')) AS valid from (select v.id AS id,v.username AS username,v.password AS password, v.created_by AS created, v.created_time AS created_time, ra.acctuniqueid as acctuniqueid, v.billingplan AS billingplan,v.valid_until AS valid_until,b.type AS type,b.amount AS amount,b.valid_for AS valid_for,b.price AS price,(max(ra.acctsessiontime) / 60) AS time_used,if((b.type = _latin1'time'),(b.amount - (max(ra.acctsessiontime) / 60)),_latin1'null') AS time_remain,((max(ra.acctoutputoctets) + max(ra.acctinputoctets)) / 1048576) AS packet_used,if((b.type = _latin1'packet'),(b.amount - (max((ra.acctoutputoctets + ra.acctinputoctets)) / 1048576)),_latin1'null') AS packet_remain,v.isexported AS isexported,v.isprinted AS isprinted,if((b.type = _latin1'time'),if(((max(ra.acctsessiontime) / 60) >= b.amount),_latin1'exp',_latin1'valid'),if((((max(ra.acctoutputoctets) + max(ra.acctinputoctets)) / 1048576) >= b.amount),_latin1'exp',_latin1'valid')) AS valid from ((voucher v left join radacct ra on((v.username = ra.username))) join billingplan b on((b.name = v.billingplan))) group by v.username, ra.acctuniqueid) as b group by username , acctsessiontime, acctoutputoctets acctinputoctets .
: radacct , . . , , .
, . . voucher_list_0 voucher_list:
mysql -u easyhotspot -p easyhotspot_opensource Enter password: easyhotspot create VIEW voucher_list_0 AS select v.id AS id,v.username AS username,v.password AS password,ra.acctuniqueid as acctuniqueid, v.billingplan AS billingplan, v.valid_until AS valid_until,b.type AS type,b.amount AS amount,b.valid_for AS valid_for,b.price AS price,(max(ra.acctsessiontime) / 60) AS time_used,if((b.type = _latin1'time'),(b.amount - (max(ra.acctsessiontime) / 60)),_latin1'null') AS time_remain,((max(ra.acctoutputoctets) + max(ra.acctinputoctets)) / 1048576) AS packet_used,if((b.type = _latin1'packet'),(b.amount - (max((ra.acctoutputoctets + ra.acctinputoctets)) / 1048576)),_latin1'null') AS packet_remain,v.isexported AS isexported,v.isprinted AS isprinted,if((b.type = _latin1'time'),if(((max(ra.acctsessiontime) / 60) >= b.amount),_latin1'exp',_latin1'valid'),if((((max(ra.acctoutputoctets) + max(ra.acctinputoctets)) / 1048576) >= b.amount),_latin1'exp',_latin1'valid')) AS valid from ((voucher v left join radacct ra on((v.username = ra.username))) join billingplan b on((b.name = v.billingplan))) group by v.username, ra.acctuniqueid; :
drop view voucher_list; :
create VIEW voucher_list AS select id, username, password, billingplan, valid_until, type, amount, valid_for, price, sum(time_used) AS time_used, if((type = _latin1'time'),(amount - sum(time_used)),_latin1'null') AS time_remain, sum(packet_used) AS packet_used, if((type = _latin1'packet'),((amount - sum(packet_used)) / 1048576),_latin1'null') AS packet_remain, isexported, isprinted, if((type = _latin1'time'),if((sum(time_used) >= amount),_latin1'exp',_latin1'valid'),if((sum(packet_used) >= amount),_latin1'exp',_latin1'valid')) AS valid from voucher_list_0 group by username; [ Cashier Menu ] -> Postpaid. :
VIEW `postpaid_account_list` AS select `postpaid_account`.`id` AS `id`,`postpaid_account`.`realname` AS `realname`,`postpaid_account`.`username` AS `username`,`postpaid_account`.`password` AS `password`,(sum(`radacct`.`acctsessiontime`) / 60) AS `time_used`,(sum((`radacct`.`acctoutputoctets` + `radacct`.`acctinputoctets`)) / 1048576) AS `packet_used`,`postpaid_account`.`bill_by` AS `bill_by`,(`postplan`.`price` * (sum(`radacct`.`acctsessiontime`) / 60)) AS `time_price`,(`postplan`.`price` * (sum((`radacct`.`acctoutputoctets` + `radacct`.`acctinputoctets`)) / 1048576)) AS `packet_price`,`postpaid_account`.`valid_until` AS `valid_until` from ((`postpaid_account` left join `radacct` on((`postpaid_account`.`username` = `radacct`.`username`))) join `postplan` on((`postplan`.`name` = `postpaid_account`.`bill_by`))) group by `postpaid_account`.`username` , `postpaid_account_list` :
select id, realname, username, password, sum(time_used) AS time_used, sum(packet_used) AS packet_used, bill_by, time_price, packet_price, valid_until from (select `postpaid_account`.`id` AS `id`,`postpaid_account`.`realname` AS `realname`,`postpaid_account`.`username` AS `username`,`postpaid_account`.`password` AS `password`,(max(`radacct`.`acctsessiontime`)/60 ) AS `time_used`,(max((`radacct`.`acctoutputoctets` + `radacct`.`acctinputoctets`)) / 1048576) AS `packet_used`,`postpaid_account`.`bill_by` AS `bill_by`,(`postplan`.`price` * (max(`radacct`.`acctsessiontime`) / 60)) AS `time_price`,(`postplan`.`price` * (max((`radacct`.`acctoutputoctets` + `radacct`.`acctinputoctets`)) / 1048576)) AS `packet_price`,`postpaid_account`.`valid_until` AS `valid_until` from ((`postpaid_account` left join `radacct` on((`postpaid_account`.`username` = `radacct`.`username`))) join `postplan` on((`postplan`.`name` = `postpaid_account`.`bill_by`))) group by `postpaid_account`.`username`, radacct.acctuniqueid ) list group by username postpaid_account_list_0 postpaid_account_list:
create VIEW postpaid_account_list_0 AS select `postpaid_account`.`id` AS `id`,`postpaid_account`.`realname` AS `realname`,`postpaid_account`.`username` AS `username`,`postpaid_account`.`password` AS `password`,(max(`radacct`.`acctsessiontime`)/60 ) AS `time_used`,(max((`radacct`.`acctoutputoctets` + `radacct`.`acctinputoctets`)) / 1048576) AS `packet_used`,`postpaid_account`.`bill_by` AS `bill_by`,(`postplan`.`price` * (max(`radacct`.`acctsessiontime`) / 60)) AS `time_price`,(`postplan`.`price` * (max((`radacct`.`acctoutputoctets` + `radacct`.`acctinputoctets`)) / 1048576)) AS `packet_price`,`postpaid_account`.`valid_until` AS `valid_until` from ((`postpaid_account` left join `radacct` on((`postpaid_account`.`username` = `radacct`.`username`))) join `postplan` on((`postplan`.`name` = `postpaid_account`.`bill_by`))) group by `postpaid_account`.`username`, radacct.acctuniqueid; :
drop view postpaid_account_list; :
create VIEW postpaid_account_list AS select id, realname, username, password, sum(time_used) AS time_used, sum(packet_used) AS packet_used, bill_by, time_price, packet_price, valid_until from postpaid_account_list_0 group by username; , freeRadius radacct. [noresetcounter], /etc/raddb/sql/mysql/counter.conf.
vi /etc/raddb/sql/mysql/counter.conf +110
sqlcounter noresetcounter { counter-name = Session-Timeout check-name = Session-Timeout reply-name = Session-Timeout sqlmod-inst = sql key = User-Name reset = never query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{${key}}'" } . noresetcounter, . , 3600 , . , , , .
- check-name = Session-Timeout — , freeRadius (radcheck radgroupcheck) Session-Timeout
- reply-name = Session-Timeout — , Session-Timeout sql- .
query. , . , . AcctSessionTime , . mysql IFNULL, , 0.
query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{${key}}'" on
query = "SELECT SUM(b) FROM (SELECT IFNULL(MAX(AcctSessionTime),0) as b FROM radacct WHERE UserName='%{${key}}'GROUP BY acctuniqueid) as list" , session [sql] /etc/raddb/sql/mysql/dialup.conf «Simultaneous Use Checking Queries».
simul_count_query = "SELECT COUNT(*) \ FROM ${acct_table1} \ WHERE username = '%{SQL-User-Name}' \ AND acctstoptime IS NULL" . , radgroupcheck Simultaneous-Use := 1, .
detpis7:
simul_count_query? , :
mysql> SELECT COUNT(*) -> FROM radacct -> WHERE username = 'detpis7' -> AND acctstoptime IS NULL; +----------+ | COUNT(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) , detpis7 . :
vi /etc/raddb/sql/mysql/dialup.conf +300
simul_count_query = "SELECT COUNT(*) FROM (SELECT username, MAX(radacctid), MAX(acctstoptime) as stop, MAX(acctstarttime) FROM ${acct_table1} GROUP BY acctuniqueid HAVING username = '%{SQL-User-Name}' AND stop IS NULL) list" mysql:
mysql> SELECT COUNT(*) FROM (SELECT username, MAX(radacctid), MAX(acctstoptime) as stop, MAX(acctstarttime) FROM radacct GROUP BY acctuniqueid HAVING username = 'detpis7' AND stop IS NULL) list -> ; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) , hotspot .
Source: https://habr.com/ru/post/275155/
All Articles