Cisco WLC5508、FreeRadius、MySQL、およびEasyhotspotを備えたHotSpot

yum install wget
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 wget http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm rpm -ivh epel-release-6-8.noarch.rpm
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 yum repolist yum update
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 short_open_tag = On
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 EasyHotSpot config->item('EASYHOTSPOT_VERSION');?> load->view($this->config->item('FAL_template_dir').'template/menu');?> EasyHotspot - Hotspot Management System GNU Public License
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 date.timezone = Europe/Moscow
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 chkconfig --level 235 httpd on chkconfig --level 235 mysqld on chkconfig --level 235 dnsmasq on
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 cd /opt git clone https://github.com/rafeequl/EasyHotspot ln -s /opt/EasyHotspot/htdocs /var/www/html/easyhotspot
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 <!-- <li class="chillispot"><?=anchor('admin/chillispot','Chillispot')?></li> --> <!-- <li class="radius"><?=anchor('admin/freeradius','FreeRadius')?></li> -->
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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; ?>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 service httpd restart
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

FreeRadiusをインストールして構成する

 yum install freeradius freeradius-mysql freeradius-utils
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 chkconfig --level 235 radiusd on
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ipaddr = 127.0.0.1 secret = easyhotspot nastype = other
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 $INCLUDE sql.conf $INCLUDE sql/mysql/counter.conf
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 noresetcounter
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 sql noresetcounter
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 sql
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 sql #radutmp
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 sql
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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}}'" }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 simul_count_query = "SELECT COUNT(*) \ FROM ${acct_table1} \ WHERE username = '%{SQL-User-Name}' \ AND acctstoptime IS NULL"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

  database = "mysql" driver = "rlm_sql_${database}" server = "localhost" #port = 3306 login = "easyhotspot" password = "easyhotspot" radius_db = "easyhotspot_opensource"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 service radiusd start
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Cisco WLCを設定して下さい

 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 }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 service radiusd restart
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 radtest zupvez10 palkipud 127.0.0.1 100 easyhotspot
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 [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 ~]#
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 [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 ~]#
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

• WISPr-Session-Terminate-Time = "2016-1-6T24：00：00"-翌日の午前0時にセッションが無効になります
• Session-Timeout = 3600-NASは1時間でユーザーを切断する必要があることを意味します
• アイドルタイムアウト= 300-ユーザーが5分間アクティブでない場合、NASはセッションをドロップする必要があります
• Acct-Interim-Interval = 2分ごとに120、NASはユーザーの現在の状況に関するレポートを含むアカウンティングパッケージ（interim-update）を送信する必要があります

 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"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 [root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f start.txt Received response ID 50, code 5, length = 20
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

• Acct-Session-Time = 11
• Acct-Input-Packets = 15
• Acct-Output-Packets = 3
• Acct-Input-Octets = 1407
• Acct-Output-Octets = 467

 [root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f interim-update.txt Received response ID 226, code 5, length = 20
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 [root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f stop.txt Received response ID 166, code 5, length = 20
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 [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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 [root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f start.txt Received response ID 15, code 5, length = 20
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 [root@FreeRadius ~]# radclient 127.0.0.1 auto easyhotspot -f stop.txt Received response ID 143, code 5, length = 20
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 service radiusd stop
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 [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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 less log.txt
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

• [前処理]-raddb / huntgroupsおよびraddb / hintsで設定可能、使用しない
• [chap] [mschap] [eap] [pap]-認証プロトコル。デフォルトでは、freeRadiusは使用するプロトコルを認識せず、authorizeセクションですべてをチェックします
• [ダイジェスト]-Cisco SIPサーバーに使用されます。このモジュールは不要です
• [接尾辞]-このモジュールはレルムの検索でUser-Name属性を解析するため、プロキシを設定できます
• [files]-ユーザーを検索してファイル/ raddb / usersを解析します。データベースを使用するため、違いはありません
• [sql]-ユーザーとその属性を検索してデータベースをチェックします。クエリ自体はファイル/etc/raddb/sql/mysql/dialup.confに記述されています

• [noresetcounter]-ユーザーの残り時間を計算する同じモジュールは、ファイル/etc/raddb/sql/mysql/counter.confに記述されています
• [expiration]-有効期限属性を確認します。クレジットが有効な時間が経過した場合、ユーザーが費やした割り当てられた時間の長さは関係なく、アクセスは拒否されます。
• [logintime]-木曜日の午後など、クレジットが機能する時間を設定できます
• [pap]-Auth-Typeが見つかりました= PAP認証プロトコルが定義されています

• radcheck
• radreply
• radusergroup
• radgroupcheck
• radgroupreply
• radpostauth

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

• [acct_unique]-パッケージからいくつかの属性を取得してハッシュを計算し、acctuniqueidのように、radacctテーブル内のデータベースに書き込みます
• [接尾辞]-このモジュールはレルムの検索でUser-Name属性を解析するため、プロキシを設定できます
• [ファイル]-ファイル/ etc / raddb / acct_usersで設定された受信属性に応じて、パッケージを異なる方法で処理できます。

• [詳細]-パッケージを/ etc / raddb / modules / detailで設定されたテキストファイルに記録しますこれらのログを表示できます/var/log/radius/radacct/127.0.0.1/
• [sql]-クエリをデータベースのradacctテーブルに記録します。UPDATEクエリが使用されていることがわかります。つまり、新しいレコードは作成されませんが、開始パッケージの処理中に作成された既存のレコードは更新されます。 このアプローチでは、1つのセッションに1つのレコードがあり、その後、[noresetcounter]などのモジュールのセクションとクエリが作成されるため、これは重要ですが、これは/ 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で定義されたプロキシ設定用のモジュール

• radacct

MySQLデータベースのテーブルを検討する

 # Pre-accounting. Decide which accounting type to use. # preacct { if (User-Name=~ /^[A-Fa-f0-9]{12}$/) { reject }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

• 「〜」-条件では正規表現を使用し、スラッシュ「//」の間に記述されることを意味します
• 「^」-正規表現の始まりを意味します
• 「[A-Fa-f0-9]」-角括弧内は、比較が行われる1文字を示します。ここでは、16進数が書き込まれる値の範囲を示します。ポピーアドレス
• 「{12}」-中括弧内に、前の式が繰り返される回数を記述します。MACアドレスには12文字が含まれます。
• 「$」-式の終わり

 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.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 DATE_SUB('%S', \ INTERVAL (%{%{Acct-Session-Time}:-0} + \ %{%{Acct-Delay-Time}:-0}) SECOND)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 <?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'); ?>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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`
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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}}'" }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

• check-name = Session-Timeout-このパラメーターは、対応するテーブル（radcheckおよびradgroupcheck）のfreeRadiusがSession-Timeout属性をチェックすることを示します
• reply-name = Session-Timeout-sqlクエリが返す最初に設定されたSession-Timeoutから減算した後、正の数が残っている場合、同じ属性が返されます。

 query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{${key}}'"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 query = "SELECT SUM(b) FROM (SELECT IFNULL(MAX(AcctSessionTime),0) as b FROM radacct WHERE UserName='%{${key}}'GROUP BY acctuniqueid) as list"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 simul_count_query = "SELECT COUNT(*) \ FROM ${acct_table1} \ WHERE username = '%{SQL-User-Name}' \ AND acctstoptime IS NULL"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 mysql> SELECT COUNT(*) -> FROM radacct -> WHERE username = 'detpis7' -> AND acctstoptime IS NULL; +----------+ | COUNT(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 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> 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)