SuperVlan: различия между версиями

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску
 
(не показана 61 промежуточная версия этого же участника)
Строка 1: Строка 1:
  +
[[Категория:Networking]]
  +
[[Категория:Dlink]]
  +
[[Категория:Linux]]
  +
[[Категория:DHCP]]
 
=SuperVlan на DGS3612G=
  
=SuperVlan на DGS3612G=
 
==Постановка задачи==
  
==Постановка задачи==
Строка 171: Строка 175:
 
bills on ((users.bill_id = bills.id))
 
bills on ((users.bill_id = bills.id))
 
WHERE ( bills.deposit > 0 );
  
WHERE ( bills.deposit > 0 );
  +
</PRE>
  +
  +
====view_active_users_ext (по дополнительному_счету)====
  +
<PRE>
  +
DROP VIEW view_active_users_ext;
  +
CREATE VIEW view_active_users_ext AS
  +
SELECT
  +
users.uid AS uid,
  +
users.id AS user,
  +
inet_ntoa(dhcphosts_hosts.ip) AS ip,
  +
dhcphosts_networks_vlans.vlan AS vlan
  +
FROM
  +
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid))
  +
join
  +
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))
  +
join
  +
users on((users.uid = dhcphosts_hosts.uid))
  +
join
  +
bills on ((users.ext_bill_id = bills.id))
  +
WHERE ( bills.deposit >= 0 );
 
</PRE>
  
</PRE>
   
Строка 176: Строка 200:
 
<PRE>
  
<PRE>
 
DROP VIEW view_dhcp_config_authorized_classes;
  
DROP VIEW view_dhcp_config_authorized_classes;
−  
−  
 
CREATE VIEW view_dhcp_config_authorized_classes AS
 
CREATE VIEW view_dhcp_config_authorized_classes AS
 
select dhcphosts_networks.id AS network,
  
select dhcphosts_networks.id AS network,
Строка 214: Строка 236:
 
bills on ((users.bill_id = bills.id))
 
bills on ((users.bill_id = bills.id))
 
WHERE ( bills.deposit > 0 );
  
WHERE ( bills.deposit > 0 );
  +
</PRE>
   
  +
====view_dhcp_config_authorized_classes_ext (по дополнительному счету)====
  +
<PRE>
  +
DROP VIEW view_dhcp_config_authorized_classes_ext;
  +
CREATE VIEW view_dhcp_config_authorized_classes_ext AS
  +
select dhcphosts_networks.id AS network,
  +
inet_ntoa(dhcphosts_hosts.ip) AS ip,
  +
dhcphosts_networks_vlans.vlan AS vlan,
  +
dhcphosts_hosts.mac AS mac,
  +
dhcphosts_hosts.ports AS port,
  +
inet_ntoa(dhcphosts_hosts.nas) AS switch,
  +
concat('match_vlan__',dhcphosts_networks_vlans.vlan,'__port__',dhcphosts_hosts.ports,'__MAC__',dhcphosts_hosts.mac,'__switch__',inet_ntoa(dhcphosts_hosts.nas)) AS classname,
  +
concat(
  +
'class "match_vlan__',
  +
dhcphosts_networks_vlans.vlan,
  +
'__port__',
  +
dhcphosts_hosts.ports,
  +
'__MAC__',
  +
dhcphosts_hosts.mac,
  +
'__switch__',
  +
inet_ntoa(dhcphosts_hosts.nas),
  +
'" { match if ( binary-to-ascii(10, 16, "", substring(option agent.circuit-id, 2, 2)) = "',
  +
dhcphosts_networks_vlans.vlan,
  +
'" and binary-to-ascii(10, 8, "", substring(option agent.circuit-id, 5, 1)) = "',
  +
dhcphosts_hosts.ports,
  +
'" and binary-to-ascii(16, 8, ":", substring(hardware, 1, 6)) = "',
  +
dhcphosts_hosts.mac,
  +
'" and substring(option agent.remote-id, 2, 15) = "'
  +
,inet_ntoa(dhcphosts_hosts.nas),
  +
'" );}'
  +
) AS class
  +
from
  +
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid))
  +
join
  +
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))
  +
join
  +
users on((users.uid = dhcphosts_hosts.uid))
  +
join
  +
bills on ((users.ext_bill_id = bills.id))
  +
WHERE ( bills.deposit >= 0 );
  +
</PRE>
  +
  +
====view_dhcp_config_authorized_classes_mac====
  +
В случае когда в поле nas содержиться не ip адрес а мак вьюха отличается. А такое, блять, бывает.
  +
<PRE>
  +
DROP VIEW view_dhcp_config_authorized_classes_mac;
  +
  +
  +
CREATE VIEW view_dhcp_config_authorized_classes_mac AS
  +
select dhcphosts_networks.id AS network,
  +
inet_ntoa(dhcphosts_hosts.ip) AS ip,
  +
dhcphosts_networks_vlans.vlan AS vlan,
  +
dhcphosts_hosts.mac AS mac,
  +
dhcphosts_hosts.ports AS port,
  +
mac_ntoa(dhcphosts_hosts.nas) AS switch,
  +
concat('match_vlan__',dhcphosts_networks_vlans.vlan,'__port__',dhcphosts_hosts.ports,'__MAC__',dhcphosts_hosts.mac,'__switch__',mac_ntoa(dhcphosts_hosts.nas)) AS classname,
  +
concat(
  +
'class "match_vlan__',
  +
dhcphosts_networks_vlans.vlan,
  +
'__port__',
  +
dhcphosts_hosts.ports,
  +
'__MAC__',
  +
dhcphosts_hosts.mac,
  +
'__switch__',
  +
mac_ntoa(dhcphosts_hosts.nas),
  +
'" { match if ( binary-to-ascii(10, 16, "", substring(option agent.circuit-id, 2, 2)) = "',
  +
dhcphosts_networks_vlans.vlan,
  +
'" and binary-to-ascii(10, 8, "", substring(option agent.circuit-id, 5, 1)) = "',
  +
dhcphosts_hosts.ports,
  +
'" and binary-to-ascii(16, 8, ":", substring(hardware, 1, 6)) = "',
  +
dhcphosts_hosts.mac,
  +
'" and binary-to-ascii(16, 8, ":", substring(option agent.remote-id, 2, 15)) = "'
  +
,mac_ntoa(dhcphosts_hosts.nas),
  +
'" );}'
  +
) AS class
  +
from
  +
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid))
  +
join
  +
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))
  +
join
  +
users on((users.uid = dhcphosts_hosts.uid))
  +
join
  +
bills on ((users.bill_id = bills.id))
  +
WHERE ( bills.deposit >= 0 );
  +
  +
</PRE>
  +
  +
====view_dhcp_config_authorized_classes_mac_ext (дополнительный счет)====
  +
<PRE>
  +
DROP VIEW view_dhcp_config_authorized_classes_mac_ext;
  +
CREATE VIEW view_dhcp_config_authorized_classes_mac_ext AS
  +
select dhcphosts_networks.id AS network,
  +
inet_ntoa(dhcphosts_hosts.ip) AS ip,
  +
dhcphosts_networks_vlans.vlan AS vlan,
  +
dhcphosts_hosts.mac AS mac,
  +
dhcphosts_hosts.ports AS port,
  +
mac_ntoa(dhcphosts_hosts.nas) AS switch,
  +
concat('match_vlan__',dhcphosts_networks_vlans.vlan,'__port__',dhcphosts_hosts.ports,'__MAC__',dhcphosts_hosts.mac,'__switch__',mac_ntoa(dhcphosts_hosts.nas)) AS classname,
  +
concat(
  +
'class "match_vlan__',
  +
dhcphosts_networks_vlans.vlan,
  +
'__port__',
  +
dhcphosts_hosts.ports,
  +
'__MAC__',
  +
dhcphosts_hosts.mac,
  +
'__switch__',
  +
mac_ntoa(dhcphosts_hosts.nas),
  +
'" { match if ( binary-to-ascii(10, 16, "", substring(option agent.circuit-id, 2, 2)) = "',
  +
dhcphosts_networks_vlans.vlan,
  +
'" and binary-to-ascii(10, 8, "", substring(option agent.circuit-id, 5, 1)) = "',
  +
dhcphosts_hosts.ports,
  +
'" and binary-to-ascii(16, 8, ":", substring(hardware, 1, 6)) = "',
  +
dhcphosts_hosts.mac,
  +
'" and binary-to-ascii(16, 8, ":", substring(option agent.remote-id, 2, 15)) = "'
  +
,mac_ntoa(dhcphosts_hosts.nas),
  +
'" );}'
  +
) AS class
  +
from
  +
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid))
  +
join
  +
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))
  +
join
  +
users on((users.uid = dhcphosts_hosts.uid))
  +
join
  +
bills on ((users.ext_bill_id = bills.id))
  +
WHERE ( bills.deposit >= 0 );
 
</PRE>
  
</PRE>
   
Строка 250: Строка 398:
 
</PRE>
  
</PRE>
   
  +
====view_dhcp_config_authorized_pools_ext (используется дополнительный счет)====
  +
<PRE>
  +
DROP VIEW view_dhcp_config_authorized_pools_ext;
  +
CREATE VIEW view_dhcp_config_authorized_pools_ext
  +
AS
  +
select
  +
dhcphosts_networks.id AS network,
  +
inet_ntoa(dhcphosts_hosts.ip) AS ip,
  +
dhcphosts_networks_vlans.vlan AS vlan,
  +
dhcphosts_hosts.mac AS mac,
  +
dhcphosts_hosts.ports AS port,
  +
inet_ntoa(dhcphosts_hosts.nas) AS switch,
  +
concat(
  +
'pool { range ',
  +
inet_ntoa(dhcphosts_hosts.ip),
  +
'; allow members of "match_vlan__',
  +
dhcphosts_networks_vlans.vlan,
  +
'__port__',
  +
dhcphosts_hosts.ports,
  +
'__MAC__',
  +
dhcphosts_hosts.mac,
  +
'__switch__',inet_ntoa(dhcphosts_hosts.nas),
  +
'"; default-lease-time 600; max-lease-time 1200; }') AS pool
  +
FROM
  +
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid))
  +
join
  +
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))
  +
join
  +
users on((users.uid = dhcphosts_hosts.uid))
  +
join
  +
bills on ((users.ext_bill_id = bills.id))
  +
WHERE ( bills.deposit >= 0 );
  +
</PRE>
  +
  +
====view_dhcp_config_authorized_pools_mac====
  +
<PRE>
  +
DROP VIEW view_dhcp_config_authorized_pools_mac;
  +
CREATE VIEW view_dhcp_config_authorized_pools_mac
  +
AS
  +
select
  +
dhcphosts_networks.id AS network,
  +
inet_ntoa(dhcphosts_hosts.ip) AS ip,
  +
dhcphosts_networks_vlans.vlan AS vlan,
  +
dhcphosts_hosts.mac AS mac,
  +
dhcphosts_hosts.ports AS port,
  +
mac_ntoa(dhcphosts_hosts.nas) AS switch,
  +
concat(
  +
'pool { range ',
  +
inet_ntoa(dhcphosts_hosts.ip),
  +
'; allow members of "match_vlan__',
  +
dhcphosts_networks_vlans.vlan,
  +
'__port__',
  +
dhcphosts_hosts.ports,
  +
'__MAC__',
  +
dhcphosts_hosts.mac,
  +
'__switch__',
  +
mac_ntoa(dhcphosts_hosts.nas),
  +
'"; default-lease-time 300; max-lease-time 300; }') AS pool
  +
FROM
  +
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid))
  +
join
  +
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))
  +
join
  +
users on((users.uid = dhcphosts_hosts.uid))
  +
join
  +
bills on ((users.bill_id = bills.id))
  +
WHERE ( bills.deposit > 0 );
  +
</PRE>
  +
  +
====view_dhcp_config_authorized_pools_mac_ext====
  +
<PRE>
  +
DROP VIEW view_dhcp_config_authorized_pools_mac_ext;
  +
CREATE VIEW view_dhcp_config_authorized_pools_mac_ext
  +
AS
  +
select
  +
dhcphosts_networks.id AS network,
  +
inet_ntoa(dhcphosts_hosts.ip) AS ip,
  +
dhcphosts_networks_vlans.vlan AS vlan,
  +
dhcphosts_hosts.mac AS mac,
  +
dhcphosts_hosts.ports AS port,
  +
mac_ntoa(dhcphosts_hosts.nas) AS switch,
  +
concat(
  +
'pool { range ',
  +
inet_ntoa(dhcphosts_hosts.ip),
  +
'; allow members of "match_vlan__',
  +
dhcphosts_networks_vlans.vlan,
  +
'__port__',
  +
dhcphosts_hosts.ports,
  +
'__MAC__',
  +
dhcphosts_hosts.mac,
  +
'__switch__',
  +
mac_ntoa(dhcphosts_hosts.nas),
  +
'"; default-lease-time 300; max-lease-time 300; }') AS pool
  +
FROM
  +
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid))
  +
join
  +
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))
  +
join
  +
users on((users.uid = dhcphosts_hosts.uid))
  +
join
  +
bills on ((users.ext_bill_id = bills.id))
  +
WHERE ( bills.deposit >= 0 );
  +
</PRE>
   
 
====view_dhcp_config_authorized_nopay_classes====
  
====view_dhcp_config_authorized_nopay_classes====
 
<PRE>
  
<PRE>
 
DROP VIEW view_dhcp_config_authorized_nopay_classes;
  
DROP VIEW view_dhcp_config_authorized_nopay_classes;
−  
 
CREATE VIEW view_dhcp_config_authorized_nopay_classes
  
CREATE VIEW view_dhcp_config_authorized_nopay_classes
 
AS
  
AS
Строка 286: Строка 536:
 
'" and binary-to-ascii(16, 8, ":", substring(hardware, 1, 6)) = "',
  
'" and binary-to-ascii(16, 8, ":", substring(hardware, 1, 6)) = "',
 
dhcphosts_hosts.mac,
  
dhcphosts_hosts.mac,
−
'" and substring(option agent.remote-id, 2, 15) = "',inet_ntoa(`dhcphosts_hosts`.`nas`),
 +
'" and substring(option agent.remote-id, 2, 15) = "'
  +
inet_ntoa(`dhcphosts_hosts`.`nas`),
  +
'" );}')
  +
AS class
  +
FROM
  +
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid))
  +
join
  +
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))
  +
join
  +
users on((users.uid = dhcphosts_hosts.uid))
  +
join
  +
bills on ((users.bill_id = bills.id))
  +
WHERE ( bills.deposit <= 0 );
  +
</PRE>
  +
  +
====view_dhcp_config_authorized_nopay_classes_mac====
  +
<PRE>
  +
DROP VIEW view_dhcp_config_authorized_nopay_classes_mac;
  +
CREATE VIEW view_dhcp_config_authorized_nopay_classes_mac
  +
AS
  +
select
  +
dhcphosts_networks.id AS network,
  +
inet_ntoa(dhcphosts_hosts.ip) AS ip,
  +
dhcphosts_networks_vlans.vlan AS vlan,
  +
dhcphosts_hosts.mac AS mac,
  +
dhcphosts_hosts.ports AS port,
  +
mac_ntoa(dhcphosts_hosts.nas) AS switch,
  +
concat(
  +
'match_vlan__',
  +
dhcphosts_networks_vlans.vlan,
  +
'__port__',
  +
dhcphosts_hosts.ports,
  +
'__MAC__',
  +
dhcphosts_hosts.mac,
  +
'__switch__',
  +
mac_ntoa(dhcphosts_hosts.nas)) AS classname,
  +
concat(
  +
'class "match_vlan__',
  +
dhcphosts_networks_vlans.vlan,
  +
'__port__',
  +
dhcphosts_hosts.ports,
  +
'__MAC__',dhcphosts_hosts.mac,
  +
'__switch__',
  +
mac_ntoa(dhcphosts_hosts.nas),
  +
'" { match if ( binary-to-ascii(10, 16, "", substring(option agent.circuit-id, 2, 2)) = "',
  +
dhcphosts_networks_vlans.vlan,
  +
'" and binary-to-ascii(10, 8, "", substring(option agent.circuit-id, 5, 1)) = "',
  +
dhcphosts_hosts.ports,
  +
'" and binary-to-ascii(16, 8, ":", substring(hardware, 1, 6)) = "',
  +
dhcphosts_hosts.mac,
  +
'" and binary-to-ascii(16, 8, ":",substring(option agent.remote-id, 2, 15)) = "',
  +
mac_ntoa(`dhcphosts_hosts`.`nas`),
 
_utf8'" );}')
 
_utf8'" );}')
 
AS class
 
AS class
Строка 297: Строка 598:
 
join
  
join
 
bills on ((users.bill_id = bills.id))
 
bills on ((users.bill_id = bills.id))
−
WHERE ( bills.deposit < 0 );
 +
WHERE ( bills.deposit <= 0 );
   
   
 
</PRE>
  
</PRE>
  +
  +
====view_dhcp_config_authorized_nopay_classes_mac_ext====
  +
<PRE>
  +
DROP VIEW view_dhcp_config_authorized_nopay_classes_mac_ext;
  +
CREATE VIEW view_dhcp_config_authorized_nopay_classes_mac_ext
  +
AS
  +
select
  +
dhcphosts_networks.id AS network,
  +
inet_ntoa(dhcphosts_hosts.ip) AS ip,
  +
dhcphosts_networks_vlans.vlan AS vlan,
  +
dhcphosts_hosts.mac AS mac,
  +
dhcphosts_hosts.ports AS port,
  +
mac_ntoa(dhcphosts_hosts.nas) AS switch,
  +
concat(
  +
'match_vlan__',
  +
dhcphosts_networks_vlans.vlan,
  +
'__port__',
  +
dhcphosts_hosts.ports,
  +
'__MAC__',
  +
dhcphosts_hosts.mac,
  +
'__switch__',
  +
mac_ntoa(dhcphosts_hosts.nas)) AS classname,
  +
concat(
  +
'class "match_vlan__',
  +
dhcphosts_networks_vlans.vlan,
  +
'__port__',
  +
dhcphosts_hosts.ports,
  +
'__MAC__',dhcphosts_hosts.mac,
  +
'__switch__',
  +
mac_ntoa(dhcphosts_hosts.nas),
  +
'" { match if ( binary-to-ascii(10, 16, "", substring(option agent.circuit-id, 2, 2)) = "',
  +
dhcphosts_networks_vlans.vlan,
  +
'" and binary-to-ascii(10, 8, "", substring(option agent.circuit-id, 5, 1)) = "',
  +
dhcphosts_hosts.ports,
  +
'" and binary-to-ascii(16, 8, ":", substring(hardware, 1, 6)) = "',
  +
dhcphosts_hosts.mac,
  +
'" and binary-to-ascii(16, 8, ":",substring(option agent.remote-id, 2, 15)) = "',
  +
mac_ntoa(`dhcphosts_hosts`.`nas`),
  +
_utf8'" );}')
  +
AS class
  +
FROM
  +
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid))
  +
join
  +
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))
  +
join
  +
users on((users.uid = dhcphosts_hosts.uid))
  +
join
  +
bills on ((users.ext_bill_id = bills.id))
  +
WHERE ( bills.deposit < 0 );
  +
</PRE>
  +
 
====view_dhcp_config_generic_classes====
  
====view_dhcp_config_generic_classes====
 
<PRE>
  
<PRE>
Строка 316: Строка 668:
 
) AS class
  
) AS class
 
from dhcphosts_networks_vlans;
  
from dhcphosts_networks_vlans;
  +
</PRE>
  +
  +
====FUNCTION GetBroadcast====
  +
<PRE>
  +
DROP FUNCTION GetBroadcast;
  +
delimiter //
  +
CREATE FUNCTION GetBroadcast(n1 int UNSIGNED, m1 INT UNSIGNED) RETURNS INT UNSIGNED
  +
BEGIN
  +
DECLARE b1 INT UNSIGNED;
  +
SELECT n1+POWER(2,32-BIT_COUNT(m1))-1 INTO b1;
  +
RETURN b1;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
====IntToMac====
  +
По аналогии с inet_ntoa преобразование мака из числа к строке (БЕЗ лидирующих нулей!)
  +
<PRE>
  +
DROP FUNCTION mac_ntoa;
  +
  +
delimiter //
  +
CREATE FUNCTION mac_ntoa(mac bigint UNSIGNED) RETURNS char(32)
  +
BEGIN
  +
DECLARE res char(32);
  +
SELECT
  +
concat
  +
(
  +
lower(hex( ( mac >> 40 )& 0x0000000000ff ) ),
  +
":",
  +
lower(hex( ( mac >> 32 )& 0x0000000000ff ) ),
  +
":",
  +
lower(hex( ( mac >> 24 )& 0x0000000000ff ) ),
  +
":",
  +
lower(hex( ( mac >> 16 )& 0x0000000000ff ) ),
  +
":",
  +
lower(hex( ( mac >> 8 )& 0x0000000000ff ) ),
  +
":",
  +
lower(hex( ( mac >> 0 )& 0x0000000000ff ) )
  +
)
  +
INTO res;
  +
RETURN res;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
====nmslog====
  +
<PRE>
  +
CREATE TABLE `nmslog` (
  +
`seq` int(10) unsigned NOT NULL auto_increment,
  +
`local_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  +
`client_ip` varchar(32) default NULL,
  +
`uid` int(11) default NULL,
  +
`VLAN` int(11) default NULL,
  +
`MODULE` int(11) default NULL,
  +
`PORT` int(11) default NULL,
  +
`MAC` varchar(32) default NULL,
  +
`SWITCH` varchar(32) default NULL,
  +
`username` varchar(32) default NULL,
  +
`client_net` int(11) default NULL,
  +
`msg` text,
  +
`log_reason` varchar(255) default 'info',
  +
PRIMARY KEY (`seq`)
  +
) ENGINE=MyISAM
 
</PRE>
  
</PRE>
   
Строка 350: Строка 766:
 
bills on ((users.bill_id = bills.id))
 
bills on ((users.bill_id = bills.id))
 
WHERE ( bills.deposit < 0 );
  
WHERE ( bills.deposit < 0 );
  +
</PRE>
  +
  +
====Проверить-запрос====
  +
<PRE>
  +
SELECT
  +
INET_NTOA(dv_main.ip), value, mac_nes.port, mac_nes.id, dhcphosts_hosts.uid, mac_nes.ip from ( mac_nes JOIN dhcphosts_hosts on mac_nes.IP=INET_NTOA(dhcphosts_hosts.ip) ) JOIN dv_main ON dv_main.uid=dhcphosts_hosts.uid WHERE mac_nes.port=11 and mac_nes.id="172.16.10.11" GROUP BY uid;
 
</PRE>
  
</PRE>
   

Текущая версия на 15:37, 28 сентября 2012

SuperVlan на DGS3612G

Постановка задачи

Хочется странного - есть сеть реальных адресов неизвестной на момент проектирования длинны. требуется спланировать сеть с наиболее разумным распределением адресного пространства для клиентов. Вариант "Плоская одноранговая сеть" исключен т.к. для коммутаторов 3200-26 возможно возникновение проблемы с хешами.

План сети (в работе)

Для экономии адресов хочу использовать фичу SuperVLAN коммутатора DGS3612. Идея в том, что несмотря на то что сеть разбита на вланы, все клиенты будут получать адреса из общего диапазона, иметь один общий шлюз и т.р. Для связи между вланами - использую proxy arp

Схема сети (предварительный план)

вместо реальной сети использую 99.0.0.0/20, на данный момент сети еще нет, в процессе получения 


[DES-3200-26, VLAN2000]---VLAN2000---+
                                     +-----[DGS3627G--SuperVlan3000 (sub-vlans 2000-XXXX), ip 99.0.0.1/22 ]==trunk vlans 2000-XXXX and mgt vlans===[router, dhcp, eth0.2000 - 0.xxxx for unauth users  ]
[DES-3200-26, VLAN2000]---VLANXXXX---+

Все коммутаторы уровня дома включаются в DGS, на DGS настроен SuperVlan для вланов 2000-ХХХХ (ХХХХ - последний клиентский влан, номер не известен).

Конфиг SuperVlan

Пока есть 3 влана - 2001 2002 2003, для них и конфигурируем: 

create vlan VLAN2000 tag 2000
config vlan VLAN2000 add tagged 1-24 advertisement disable                      
create vlan VLAN2001 tag 2001
config vlan VLAN2001 add tagged 1-24 advertisement disable
create vlan VLAN2002 tag 2002
config vlan VLAN2002 add tagged 1-24 advertisement disable
create vlan VLAN2003 tag 2003
config vlan VLAN2003 add tagged 1-24 advertisement disable

create vlan SVL3000 tag 3000
config vlan SVL3000 advertisement disable

# SUPERVLAN
create super_vlan vlanid 3000 
config super_vlan vlanid 3000 add sub_vlan 2000-2003
config sub_vlan vlanid 2000 add ip_range 99.0.0.2 to 99.0.0.12
config sub_vlan vlanid 2000 add ip_range 99.0.0.20 to 99.0.0.29
config sub_vlan vlanid 2001 add ip_range 99.0.0.13 to 99.0.0.19



# IP
config ipif_mac_mapping ipif SVL3000 mac_offset 3
create ipif SVL3000 99.0.0.1/22 SVL3000 state enable 
config ipif SVL3000 proxy_arp enable local disable
config ipif SVL3000 ip_mtu 1500
config ipif SVL3000 dhcpv6_client disable
config ipif SVL3000  ip_directed_broadcast  disable

Логика работы

  • Включить клиента в порт. Клиенту через релей на свитче доступа будет выдан временный неавторизованый адрес (из сеть 10.2.0.0/16, сеть /24 на каждый влан, 3-й октет совпадает с номером влана -2000, например для влана 2010 - 10.2.10.0.24)
  • Клиент (из-за правил файрволла на свитче) может подключиться только к биллингу.
  • Клиент вводит свой логин и пароль, биллиг присваивает ему реальный адрес из пула для этого влана
  • Клиент получает реальный адрес и работает.


Пулы не являются непрерывными, по сути это просто списки адресов. Однако пулы предварительно должны быть привязаны к вланам.

  • Если у клиента отицательный баланс присвоить ему адрес из сети 10.3.0.0/16, по аналогии с неавторизованным, перенаправить на биллинг.

Перенаправление клиентов-должников или неавторизованных

На роутере:

iptables -t nat -A PREROUTING -s 10.2.0.0/16 -m tcp -p tcp --dport 80 -j DNAT --to-destination 172.16.255.4:80 iptables -t nat -A PREROUTING -s 10.3.0.0/16 -m tcp -p tcp --dport 80 -j DNAT --to-destination 172.16.255.6:80

Напомню:

  • 10.2.0.0/16 выдается неавторизованным (неизвестным) и для них страница авторизации - 172.16.255.4:80
  • 10.3.0.0/16 выдается известным должникам и для них страница - 172.16.255.6:80

Распределение реальных адресов по VLAN

Т.к. не смотря на то что фактически клиенты будут находиться в разных сегментах (влан), выдавать адреса им будем из одной сети (99.0.0.0/20)
Однако адреса жестко привязаны к VLAN id (настраивается на коммутаторе). Потому, нам нужно иметь предварительно-сконфигурированные диапазоны. Учитывая что диапазоны адресов описываются как списки, которые никак не привязаны к сетевым маскам (по сути просто список по /32), такой же логике я буду следовать и в биллинге. Создаю таблицу 

CREATE TABLE dhcphosts_hosts_vlans 
(
   ip   int(11) default NULL unique,
   vlan int(11) default NULL,
   is_used int(1) default NULL
);

Для теста - пробую распределить "реальные" адреса по саб-вланам в супервлане: 

DGS-3627G:admin#show sub_vlan 
Command: show sub_vlan

 Sub VID  Status    Super VID  IP Range
 -------  --------  ---------  ---------------------
  2000    Active     3000      99.0.0.2-99.0.0.12 
                               99.0.0.20-99.0.0.29 
  2001    Active     3000      99.0.0.13-99.0.0.19 
Total Entries: 4

2001-му влану достались: 

INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.13'), 2001 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.14'), 2001 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.15'), 2001 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.16'), 2001 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.17'), 2001 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.18'), 2001 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.19'), 2001 ,0);

INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.2'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.3'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.4'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.5'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.6'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.7'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.8'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.9'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.10'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.11'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.12'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.20'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.21'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.22'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.23'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.24'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.25'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.26'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.27'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.28'), 2000 ,0);
INSERT into dhcphosts_hosts_vlans(ip,vlan, is_used) VALUES(INET_ATON('99.0.0.29'), 2000 ,0);

Еще таблички которых не было у Асмодея

Сопоставление сети и влана

CREATE TABLE dhcphosts_networks_vlans (
  id int(11) default NULL,
  vlan int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



DHCP


Т.к. логика присвоения IP  несколько поменялась, некоторые представления данных пришлось изменить ...



view_active_users


DROP VIEW view_active_users;

CREATE VIEW view_active_users AS 
SELECT 
users.uid AS uid,
users.id  AS user,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan 
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.bill_id = bills.id)) 
WHERE ( bills.deposit > 0 );



view_active_users_ext (по дополнительному_счету)


DROP VIEW view_active_users_ext;
CREATE VIEW view_active_users_ext AS 
SELECT 
users.uid AS uid,
users.id  AS user,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan 
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.ext_bill_id = bills.id)) 
WHERE ( bills.deposit >= 0 );



view_dhcp_config_authorized_classes


DROP VIEW view_dhcp_config_authorized_classes;
CREATE VIEW view_dhcp_config_authorized_classes AS 
select dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
inet_ntoa(dhcphosts_hosts.nas) AS switch,
concat('match_vlan__',dhcphosts_networks_vlans.vlan,'__port__',dhcphosts_hosts.ports,'__MAC__',dhcphosts_hosts.mac,'__switch__',inet_ntoa(dhcphosts_hosts.nas)) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',
inet_ntoa(dhcphosts_hosts.nas),
'"  {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"        and        binary-to-ascii(10, 8, "",  substring(option agent.circuit-id, 5, 1)) = "',
dhcphosts_hosts.ports,
'"        and        binary-to-ascii(16,  8, ":", substring(hardware,                1, 6)) = "',
dhcphosts_hosts.mac,
'"        and        substring(option agent.remote-id, 2, 15) = "'
,inet_ntoa(dhcphosts_hosts.nas),
'"    );}'
) AS class 
from 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.bill_id = bills.id)) 
WHERE ( bills.deposit > 0 );



view_dhcp_config_authorized_classes_ext (по дополнительному счету)


DROP VIEW view_dhcp_config_authorized_classes_ext;
CREATE VIEW view_dhcp_config_authorized_classes_ext AS 
select dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
inet_ntoa(dhcphosts_hosts.nas) AS switch,
concat('match_vlan__',dhcphosts_networks_vlans.vlan,'__port__',dhcphosts_hosts.ports,'__MAC__',dhcphosts_hosts.mac,'__switch__',inet_ntoa(dhcphosts_hosts.nas)) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',
inet_ntoa(dhcphosts_hosts.nas),
'"  {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"        and        binary-to-ascii(10, 8, "",  substring(option agent.circuit-id, 5, 1)) = "',
dhcphosts_hosts.ports,
'"        and        binary-to-ascii(16,  8, ":", substring(hardware,                1, 6)) = "',
dhcphosts_hosts.mac,
'"        and        substring(option agent.remote-id, 2, 15) = "'
,inet_ntoa(dhcphosts_hosts.nas),
'"    );}'
) AS class 
from 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.ext_bill_id = bills.id)) 
WHERE ( bills.deposit >= 0 );



view_dhcp_config_authorized_classes_mac


В случае когда в поле nas  содержиться не ip  адрес а мак вьюха отличается. А такое, блять, бывает.



DROP VIEW view_dhcp_config_authorized_classes_mac;


CREATE VIEW view_dhcp_config_authorized_classes_mac AS 
select dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
mac_ntoa(dhcphosts_hosts.nas) AS switch,
concat('match_vlan__',dhcphosts_networks_vlans.vlan,'__port__',dhcphosts_hosts.ports,'__MAC__',dhcphosts_hosts.mac,'__switch__',mac_ntoa(dhcphosts_hosts.nas)) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts.nas),
'"  {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"        and        binary-to-ascii(10, 8, "",  substring(option agent.circuit-id, 5, 1)) = "',
dhcphosts_hosts.ports,
'"        and        binary-to-ascii(16,  8, ":", substring(hardware,                1, 6)) = "',
dhcphosts_hosts.mac,
'"        and        binary-to-ascii(16,  8, ":", substring(option agent.remote-id, 2, 15)) = "'
,mac_ntoa(dhcphosts_hosts.nas),
'"    );}'
) AS class 
from 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.bill_id = bills.id)) 
WHERE ( bills.deposit >= 0 );




view_dhcp_config_authorized_classes_mac_ext (дополнительный счет)


DROP VIEW view_dhcp_config_authorized_classes_mac_ext;
CREATE VIEW view_dhcp_config_authorized_classes_mac_ext AS 
select dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
mac_ntoa(dhcphosts_hosts.nas) AS switch,
concat('match_vlan__',dhcphosts_networks_vlans.vlan,'__port__',dhcphosts_hosts.ports,'__MAC__',dhcphosts_hosts.mac,'__switch__',mac_ntoa(dhcphosts_hosts.nas)) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts.nas),
'"  {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"        and        binary-to-ascii(10, 8, "",  substring(option agent.circuit-id, 5, 1)) = "',
dhcphosts_hosts.ports,
'"        and        binary-to-ascii(16,  8, ":", substring(hardware,                1, 6)) = "',
dhcphosts_hosts.mac,
'"        and        binary-to-ascii(16,  8, ":", substring(option agent.remote-id, 2, 15)) = "'
,mac_ntoa(dhcphosts_hosts.nas),
'"    );}'
) AS class 
from 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.ext_bill_id = bills.id)) 
WHERE ( bills.deposit >= 0 );



view_dhcp_config_authorized_pools


CREATE  VIEW view_dhcp_config_authorized_pools
AS
select 
dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
inet_ntoa(dhcphosts_hosts.nas) AS switch,
concat(
'pool { range ',
inet_ntoa(dhcphosts_hosts.ip),
'; allow members of "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',inet_ntoa(dhcphosts_hosts.nas),
'"; default-lease-time 600; max-lease-time 1200; }') AS pool
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.bill_id = bills.id)) 
WHERE ( bills.deposit > 0 );



view_dhcp_config_authorized_pools_ext (используется дополнительный счет)


DROP VIEW view_dhcp_config_authorized_pools_ext;
CREATE  VIEW view_dhcp_config_authorized_pools_ext
AS
select 
dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
inet_ntoa(dhcphosts_hosts.nas) AS switch,
concat(
'pool { range ',
inet_ntoa(dhcphosts_hosts.ip),
'; allow members of "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',inet_ntoa(dhcphosts_hosts.nas),
'"; default-lease-time 600; max-lease-time 1200; }') AS pool
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.ext_bill_id = bills.id)) 
WHERE ( bills.deposit >= 0 );



view_dhcp_config_authorized_pools_mac


DROP VIEW view_dhcp_config_authorized_pools_mac;
CREATE  VIEW view_dhcp_config_authorized_pools_mac
AS
select 
dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
mac_ntoa(dhcphosts_hosts.nas) AS switch,
concat(
'pool { range ',
inet_ntoa(dhcphosts_hosts.ip),
'; allow members of "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts.nas),
'"; default-lease-time 300; max-lease-time 300; }') AS pool
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.bill_id = bills.id)) 
WHERE ( bills.deposit > 0 );



view_dhcp_config_authorized_pools_mac_ext


DROP VIEW view_dhcp_config_authorized_pools_mac_ext;
CREATE  VIEW view_dhcp_config_authorized_pools_mac_ext
AS
select 
dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
mac_ntoa(dhcphosts_hosts.nas) AS switch,
concat(
'pool { range ',
inet_ntoa(dhcphosts_hosts.ip),
'; allow members of "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts.nas),
'"; default-lease-time 300; max-lease-time 300; }') AS pool
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.ext_bill_id = bills.id)) 
WHERE ( bills.deposit >= 0 );



view_dhcp_config_authorized_nopay_classes


DROP VIEW view_dhcp_config_authorized_nopay_classes;
CREATE VIEW view_dhcp_config_authorized_nopay_classes
AS
select
dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
inet_ntoa(dhcphosts_hosts.nas) AS switch,
concat(
'match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',
inet_ntoa(dhcphosts_hosts.nas)) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',dhcphosts_hosts.mac,
'__switch__',inet_ntoa(dhcphosts_hosts.nas),
'" {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"        and        binary-to-ascii(10, 8, "",  substring(option agent.circuit-id, 5, 1)) = "',
dhcphosts_hosts.ports,
'"        and        binary-to-ascii(16,  8, ":", substring(hardware,                1, 6)) = "',
dhcphosts_hosts.mac,
'"        and        substring(option agent.remote-id, 2, 15) = "'
inet_ntoa(`dhcphosts_hosts`.`nas`),
'"    );}') 
AS class 
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.bill_id = bills.id)) 
WHERE ( bills.deposit <= 0 );



view_dhcp_config_authorized_nopay_classes_mac


DROP VIEW view_dhcp_config_authorized_nopay_classes_mac;
CREATE VIEW view_dhcp_config_authorized_nopay_classes_mac
AS
select
dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
mac_ntoa(dhcphosts_hosts.nas) AS switch,
concat(
'match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts.nas)) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',dhcphosts_hosts.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts.nas),
'" {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"        and        binary-to-ascii(10, 8, "",  substring(option agent.circuit-id, 5, 1)) = "',
dhcphosts_hosts.ports,
'"        and        binary-to-ascii(16,  8, ":", substring(hardware,                1, 6)) = "',
dhcphosts_hosts.mac,
'"        and        binary-to-ascii(16,  8, ":",substring(option agent.remote-id, 2, 15)) = "',
mac_ntoa(`dhcphosts_hosts`.`nas`),
_utf8'"    );}') 
AS class 
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.bill_id = bills.id)) 
WHERE ( bills.deposit <= 0 );





view_dhcp_config_authorized_nopay_classes_mac_ext


DROP VIEW view_dhcp_config_authorized_nopay_classes_mac_ext;
CREATE VIEW view_dhcp_config_authorized_nopay_classes_mac_ext
AS
select
dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
mac_ntoa(dhcphosts_hosts.nas) AS switch,
concat(
'match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts.nas)) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts.ports,
'__MAC__',dhcphosts_hosts.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts.nas),
'" {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"        and        binary-to-ascii(10, 8, "",  substring(option agent.circuit-id, 5, 1)) = "',
dhcphosts_hosts.ports,
'"        and        binary-to-ascii(16,  8, ":", substring(hardware,                1, 6)) = "',
dhcphosts_hosts.mac,
'"        and        binary-to-ascii(16,  8, ":",substring(option agent.remote-id, 2, 15)) = "',
mac_ntoa(`dhcphosts_hosts`.`nas`),
_utf8'"    );}') 
AS class 
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.ext_bill_id = bills.id)) 
WHERE ( bills.deposit < 0 );



view_dhcp_config_generic_classes


CREATE  VIEW  view_dhcp_config_generic_classes 
AS 
select 
dhcphosts_networks_vlans.vlan AS vlan,
concat('match_vlan__',dhcphosts_networks_vlans.vlan) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'"  {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"    ); } '
) AS class
from dhcphosts_networks_vlans;



FUNCTION GetBroadcast


DROP FUNCTION GetBroadcast;
delimiter //
CREATE FUNCTION GetBroadcast(n1 int UNSIGNED, m1 INT UNSIGNED) RETURNS INT UNSIGNED
BEGIN
    DECLARE b1 INT UNSIGNED;
    SELECT  n1+POWER(2,32-BIT_COUNT(m1))-1 INTO b1;
    RETURN b1;
END
//
delimiter ;



IntToMac


По аналогии с inet_ntoa  преобразование мака из числа к строке (БЕЗ лидирующих нулей!) 



DROP FUNCTION mac_ntoa;

delimiter //
CREATE FUNCTION mac_ntoa(mac bigint UNSIGNED) RETURNS char(32)
BEGIN
DECLARE res char(32);
   SELECT 
   concat 
   (
      lower(hex( ( mac  >> 40  )& 0x0000000000ff ) ),
      ":",
      lower(hex( ( mac  >> 32  )& 0x0000000000ff ) ), 
      ":",
      lower(hex( ( mac  >> 24  )& 0x0000000000ff ) ),
      ":",
      lower(hex( ( mac  >> 16  )& 0x0000000000ff ) ), 
      ":",
      lower(hex( ( mac  >> 8  )& 0x0000000000ff ) ), 
      ":",
      lower(hex( ( mac  >> 0  )& 0x0000000000ff ) )
   )
INTO res;
RETURN res;
END
//
delimiter ;



nmslog


CREATE TABLE `nmslog` (
  `seq` int(10) unsigned NOT NULL auto_increment,
  `local_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `client_ip` varchar(32) default NULL,
  `uid` int(11) default NULL,
  `VLAN` int(11) default NULL,
  `MODULE` int(11) default NULL,
  `PORT` int(11) default NULL,
  `MAC` varchar(32) default NULL,
  `SWITCH` varchar(32) default NULL,
  `username` varchar(32) default NULL,
  `client_net` int(11) default NULL,
  `msg` text,
  `log_reason` varchar(255) default 'info',
  PRIMARY KEY  (`seq`)
) ENGINE=MyISAM



view_dhcp_config_nopay_pools


Вообще-то в текущей инсталляции эта вьюха не используется



DROP VIEW view_dhcp_config_nopay_pools;
CREATE VIEW view_dhcp_config_nopay_pools 
AS 
select dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts.mac AS mac,
dhcphosts_hosts.ports AS port,
inet_ntoa(dhcphosts_hosts.nas) AS switch,
concat(
'pool { range ',
inet_ntoa(((dhcphosts_hosts.ip + 65536) + 65536)),
'; allow members of "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',dhcphosts_hosts.ports,
'__MAC__',
dhcphosts_hosts.mac,
'__switch__',
inet_ntoa(dhcphosts_hosts.nas),
'"; default-lease-time 30; max-lease-time 30; }') AS pool
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts.uid))  
join
bills on ((users.bill_id = bills.id)) 
WHERE ( bills.deposit < 0 );



Проверить-запрос


SELECT 
INET_NTOA(dv_main.ip), value, mac_nes.port, mac_nes.id, dhcphosts_hosts.uid, mac_nes.ip from  ( mac_nes JOIN dhcphosts_hosts on mac_nes.IP=INET_NTOA(dhcphosts_hosts.ip) ) JOIN  dv_main ON  dv_main.uid=dhcphosts_hosts.uid  WHERE mac_nes.port=11 and mac_nes.id="172.16.10.11" GROUP BY uid;



Что доделать или проверить


По Свитчам


  • файрволл на домовых свитчах (левые дхцп, самба, что еще?)
    • 

  • проверить STP
    • 

  • включить  loop_detect
    • 

  • проверить port security
    • 

  • config traffic control (storm control)
    • 

  • syslog
    • 

  • ssh
    • 

  • disable web
    • 

  • snmp


По серверу


  • DNS  и все зоны
    • 

  • Файрволл
    • 

  • Шейпер (переделать? или ifb?)
    • 

  • радиус для свитчей
    • 

  • cacti
    • 

  • nagios
    • 

  • postfix
    • 

  • mysql backup
    • 

  • домен сайт сети


Что там с  IGMP


Проверить бы...








Источник — https://noname.com.ua/mediawiki/index.php?title=SuperVlan&oldid=5727