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

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску
 
(не показано 47 промежуточных версий этого же участника)
Строка 1: Строка 1:
  +
[[Категория:Linux]]
  +
[[Категория:Abills]]
  +
[[Категория:Notes]]
  +
<PRE>
  +
CREATE TABLE `nas_info` (
  +
`nas` int(10) unsigned default NULL,
  +
`info` text
  +
) ENGINE=MyISAM DEFAULT CHARSET=utf8
  +
</PRE>
 
<PRE>
  
<PRE>
 
CREATE TABLE user_switch (
  
CREATE TABLE user_switch (
Строка 97: Строка 106:
 
}
  
}
 
</PRE>
  
</PRE>
  +
===Авторизованные без долгов===
−  
 
<PRE>
  
<PRE>
  +
DROP VIEW IF EXISTS view_dhcp_config_authorized_classes;
  +
CREATE VIEW
  +
view_dhcp_config_authorized_classes
  +
AS
 
SELECT
  
SELECT
 
dhcphosts_networks.id AS network,
  
dhcphosts_networks.id AS network,
Строка 106: Строка 119:
 
dhcphosts_hosts.ports AS port,
  
dhcphosts_hosts.ports AS port,
 
INET_NTOA(dhcphosts_hosts.nas) AS switch,
  
INET_NTOA(dhcphosts_hosts.nas) AS switch,
−
CONCAT("class \"match_vlan__",dhcphosts_networks_vlans.vlan,"__port__",dhcphosts_hosts.ports,"__MAC__",dhcphosts_hosts.mac,"__switch__",INET_NTOA(dhcphosts_hosts.nas),"\""
 +
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
−
"{
  
−
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
  
FROM
 
((
  
((
Строка 141: Строка 144:
 
bills.deposit > 0 ;
  
bills.deposit > 0 ;
   
  +
</PRE>
   
  +
===Авторизованные должники===
  +
<PRE>
  +
DROP VIEW IF EXISTS 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
  +
JOIN
  +
dhcphosts_hosts
  +
ON
  +
dhcphosts_networks.id=dhcphosts_hosts.network
  +
)
  +
JOIN
  +
dhcphosts_networks_vlans
  +
ON
  +
dhcphosts_networks_vlans.id=dhcphosts_networks.id )
  +
JOIN
  +
users
  +
ON
  +
users.uid=dhcphosts_hosts.uid
  +
JOIN
  +
bills
  +
ON
  +
users.bill_id = bills.id
  +
WHERE
  +
bills.deposit <= 0 ;
   
 
</PRE>
  
</PRE>
  +
  +
===Generic===
  +
<PRE>
  +
DROP VIEW IF EXISTS view_dhcp_config_generic_classes;
  +
CREATE VIEW
  +
view_dhcp_config_generic_classes
  +
AS
  +
SELECT
  +
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;
  +
</PRE>
  +
  +
  +
  +
  +
  +
  +
  +
  +
  +
  +
  +
  +
  +
   
   
Строка 153: Строка 222:
 
+----+----------+-----------+------------+---------------+------------+--------------+----------------+-------------+-------+-----------+---------+----------------+---------------+----------+----------------------+---------------+------------+------+------+-----------+----------+---------+------------------+---------+--------+-----------+------------+------+----------+------+------------+-----------+-------+-----------+---------------------+------+------+
  
+----+----------+-----------+------------+---------------+------------+--------------+----------------+-------------+-------+-----------+---------+----------------+---------------+----------+----------------------+---------------+------------+------+------+-----------+----------+---------+------------------+---------+--------+-----------+------------+------+----------+------+------------+-----------+-------+-----------+---------------------+------+------+
 
1 row in set (0.00 sec)
  
1 row in set (0.00 sec)
  +
</PRE>
  +
  +
===Pools===
  +
<PRE>
  +
pool {
  +
range 10.0.0.3; allow members of "match__vlan__2001__port__1"; default-lease-time 30; max-lease-time 30;
  +
}
  +
</PRE>
  +
  +
<PRE>
  +
DROP VIEW IF EXISTS 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
  +
JOIN
  +
dhcphosts_hosts
  +
ON
  +
dhcphosts_networks.id=dhcphosts_hosts.network
  +
)
  +
JOIN
  +
dhcphosts_networks_vlans
  +
ON
  +
dhcphosts_networks_vlans.id=dhcphosts_networks.id )
  +
JOIN
  +
users
  +
ON
  +
users.uid=dhcphosts_hosts.uid
  +
JOIN
  +
bills
  +
ON
  +
users.bill_id = bills.id
  +
WHERE
  +
bills.deposit >= 0 ;
  +
</PRE>
  +
  +
  +
  +
====Вычисление броадкаста====
  +
Тривиальная задача зная ip и маску. Для простоты вынес в отдельную ф-ю.
  +
<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>
  +
  +
  +
<PRE>
  +
DROP VIEW IF EXISTS 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
  +
JOIN
  +
dhcphosts_hosts
  +
ON
  +
dhcphosts_networks.id=dhcphosts_hosts.network
  +
)
  +
JOIN
  +
dhcphosts_networks_vlans
  +
ON
  +
dhcphosts_networks_vlans.id=dhcphosts_networks.id )
  +
JOIN
  +
users
  +
ON
  +
users.uid=dhcphosts_hosts.uid
  +
JOIN
  +
bills
  +
ON
  +
users.bill_id = bills.id
  +
WHERE
  +
bills.deposit < 0 ;
  +
</PRE>
  +
  +
=Шейпинг=
  +
<PRE>
  +
DROP VIEW IF EXISTS 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
  +
JOIN
  +
dhcphosts_hosts
  +
ON
  +
dhcphosts_networks.id=dhcphosts_hosts.network
  +
)
  +
JOIN
  +
dhcphosts_networks_vlans
  +
ON
  +
dhcphosts_networks_vlans.id=dhcphosts_networks.id )
  +
JOIN
  +
users
  +
ON
  +
users.uid=dhcphosts_hosts.uid
  +
JOIN
  +
bills
  +
ON
  +
users.bill_id = bills.id
  +
WHERE
  +
bills.deposit > 0 ;
 
</PRE>
  
</PRE>

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

CREATE TABLE `nas_info` (
  `nas` int(10) unsigned default NULL,
  `info` text
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE user_switch (
    uid int default NULL,
    switch_ip int default NULL,
    port int default NULL,
    vlan int default NULL
) TYPE=InnoDB;

CREATE TABLE dhcplog (
  host varchar(32) default NULL,
  facility varchar(10) default NULL,
  priority varchar(10) default NULL,
  level varchar(10) default NULL,
  tag varchar(10) default NULL,
  date date default NULL,
  time time default NULL,
  program varchar(15) default NULL,
  msg text,
  seq int(10) unsigned NOT NULL auto_increment,
  local_time timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (seq),
  KEY host (host),
  KEY seq (seq),
  KEY program (program),
  KEY time (time),
  KEY date (date),
  KEY priority (priority),
  KEY facility (facility)
) ENGINE=MyISAM AUTO_INCREMENT=72 DEFAULT CHARSET=latin1;



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



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 DEFAULT CHARSET=utf8;



Вместо ID NASa я использую его IP - так проще. И, соответвенно, расширяю поле. 

ALTER TABLE dhcphosts_hosts  change nas nas int unsigned;    

SELECT 
   dhcphosts_networks.id,
   INET_NTOA(dhcphosts_networks.network),
   INET_NTOA(dhcphosts_networks.mask
FROM
   ( dhcphosts_networks JOIN dhcphosts_networks_vlans on dhcphosts_networks.id=dhcphosts_networks_vlans.id );


DHCP VIEW

Пример класса для мака 52:54:0:12:34:56 и порта 4 

class "match_vlan__2001__port__4__MAC__52:54:0:12:34:56__switch__172.31.0.2"
{
    match if (
        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "2001"
        and
        binary-to-ascii(10, 8, "",  substring(option agent.circuit-id, 5, 1)) = "4"
        and
        binary-to-ascii(16,  8, ":", substring(hardware,                1, 6)) = "52:54:0:12:34:56"
        and
        substring(option agent.remote-id, 2, 15) = "172.31.0.2"
    );
}

Авторизованные без долгов

DROP VIEW IF EXISTS 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 
     JOIN 
        dhcphosts_hosts 
     ON 
        dhcphosts_networks.id=dhcphosts_hosts.network
    )
     JOIN 
        dhcphosts_networks_vlans
     ON
       dhcphosts_networks_vlans.id=dhcphosts_networks.id )
     JOIN
        users
     ON
        users.uid=dhcphosts_hosts.uid
     JOIN
        bills
     ON
       users.bill_id = bills.id 
WHERE
       bills.deposit > 0 ;

Авторизованные должники

DROP VIEW IF EXISTS 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 
     JOIN 
        dhcphosts_hosts 
     ON 
        dhcphosts_networks.id=dhcphosts_hosts.network
    )
     JOIN 
        dhcphosts_networks_vlans
     ON
       dhcphosts_networks_vlans.id=dhcphosts_networks.id )
     JOIN
        users
     ON
        users.uid=dhcphosts_hosts.uid
     JOIN
        bills
     ON
       users.bill_id = bills.id 
WHERE
       bills.deposit <= 0 ;

Generic

DROP VIEW IF EXISTS view_dhcp_config_generic_classes;
CREATE   VIEW
view_dhcp_config_generic_classes
AS
SELECT
   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;










+----+----------+-----------+------------+---------------+------------+--------------+----------------+-------------+-------+-----------+---------+----------------+---------------+----------+----------------------+---------------+------------+------+------+-----------+----------+---------+------------------+---------+--------+-----------+------------+------+----------+------+------------+-----------+-------+-----------+---------------------+------+------+
| id | name     | network   | mask       | block_network | block_mask | suffix       | dns            | coordinator | phone | routers   | disable | ip_range_first | ip_range_last | comments | deny_unknown_clients | authoritative | net_parent | id   | uid  | ip        | hostname | network | mac              | disable | forced | blocktime | expire     | seen | comments | vid  | nas        | option_82 | ports | boot_file | changed             | id   | vlan |
+----+----------+-----------+------------+---------------+------------+--------------+----------------+-------------+-------+-----------+---------+----------------+---------------+----------+----------------------+---------------+------------+------+------+-----------+----------+---------+------------------+---------+--------+-----------+------------+------+----------+------+------------+-----------+-------+-----------+---------------------+------+------+
|  9 | VLAN2001 | 167837952 | 4294967040 |             0 |          0 | net_vlan2001 | 172.16.255.254 | sirmax      |       | 167837953 |       0 |      167837954 |     167838206 |          |                    0 |             1 |          0 | 1788 | 1774 | 167837954 | sirmax1  |       9 | 52:54:0:12:34:56 |       0 |      0 |         0 | 0000-00-00 |    0 |          | 2001 | 2887713021 |         0 | 9     |           | 2010-10-19 23:31:57 |    9 | 2001 |
+----+----------+-----------+------------+---------------+------------+--------------+----------------+-------------+-------+-----------+---------+----------------+---------------+----------+----------------------+---------------+------------+------+------+-----------+----------+---------+------------------+---------+--------+-----------+------------+------+----------+------+------------+-----------+-------+-----------+---------------------+------+------+
1 row in set (0.00 sec)

Pools

        pool    {
                        range 10.0.0.3; allow members of "match__vlan__2001__port__1"; default-lease-time 30; max-lease-time 30;
                }

DROP VIEW IF EXISTS 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 
     JOIN 
        dhcphosts_hosts 
     ON 
        dhcphosts_networks.id=dhcphosts_hosts.network
    )
     JOIN 
        dhcphosts_networks_vlans
     ON
       dhcphosts_networks_vlans.id=dhcphosts_networks.id )
     JOIN
        users
     ON
        users.uid=dhcphosts_hosts.uid
     JOIN
        bills
     ON
       users.bill_id = bills.id 
WHERE
       bills.deposit >= 0 ;


Вычисление броадкаста

Тривиальная задача зная ip и маску. Для простоты вынес в отдельную ф-ю. 

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 ;



DROP VIEW IF EXISTS 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 
     JOIN 
        dhcphosts_hosts 
     ON 
        dhcphosts_networks.id=dhcphosts_hosts.network
    )
     JOIN 
        dhcphosts_networks_vlans
     ON
       dhcphosts_networks_vlans.id=dhcphosts_networks.id )
     JOIN
        users
     ON
        users.uid=dhcphosts_hosts.uid
     JOIN
        bills
     ON
       users.bill_id = bills.id 
WHERE
       bills.deposit < 0 ;

Шейпинг

DROP VIEW IF EXISTS 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 
     JOIN 
        dhcphosts_hosts 
     ON 
        dhcphosts_networks.id=dhcphosts_hosts.network
    )
     JOIN 
        dhcphosts_networks_vlans
     ON
       dhcphosts_networks_vlans.id=dhcphosts_networks.id )
     JOIN
        users
     ON
        users.uid=dhcphosts_hosts.uid
     JOIN
        bills
     ON
       users.bill_id = bills.id 
WHERE
       bills.deposit > 0 ;
Источник — https://noname.com.ua/mediawiki/index.php?title=Abills_NMS&oldid=5758