Mysql запрос для создания ACL для DES-3526

[Makariy]

Задался вопросом модернизации сети:
На доступ D-link 3526
1 абонент – 1 порт
Ip адрес выдается по dhcp opt. 82 (костыль взял тут viewtopic.php?t=7141&postdays=0&postorder=asc&start=0, работает)
Vlan на дом, управление коммутатором в отдельном vlan
На каждый дом своя подсеть, терминация vlan на DGS-3627
Доступ в интернет по PPTP

У пользователей 3 тарифа
1 – помегабайтный ( нужно чтобы было видно только NAS и личный кабинет при положительном балансе)
2 – помегабайтный c предоплаченной локалкой (при положительном балансе видно все, при отрицательном только личный кабинет)
3 – Безлимитный (при положительном балансе видно все, при отрицательном, только личный кабинет)

В UTM заранее завел дома, 2 подсети для каждого дома: одна для сетевухи вторая для PPTP
Создал услугу передачи трафика – Локалка (ее планирую вешать всем)

Абонентов решил заводить так:
Сперва подключаю услугу передачи трафика «Локалка» - тут ip для сетевухи
Вешаю тариф – в тарифе ip для PPTP доступа в инет
В доп. Параметрах выставляю коммутатор и порт абонента

Абон. Плату за локалку планирую брать в тарифе
Услуга Локалка id = 75 постоянная для всех


Хочется услышать критику по данной организации сети

Возникла загвоздка в Mysql запросе:
Мне нужно вывести из базы ip абонентов из услуги id=75 , баланс положительный, статус интернета включен, ip коммутатора логин коммутатора, пароль коммутатора, порт коммутатора

По форуму искал, нашел только пример отключения порта абонента:

Код: Выделить всё

SELECT 
         inet_ntoa(4294967295 & r.router_bin_ip)  AS rip, 
         r.login, 
         r.password, 
         u.id, 
         u.port_number,
         acc.balance,
         atlink.tariff_id 
     FROM accounts as acc, users as u, routers_info as r, account_tariff_link as atlink 
     WHERE 
          not atlink.is_deleted 
          and atlink.account_id = acc.id 
          and u.basic_account = acc.id 
          and r.id = u.remote_switch_id 
          and not acc.is_deleted
          and acc.int_status = 0 
          and (    (acc.balance > '0' and atlink.tariff_id = '47' ) 
             )
order by rip;

результат выводит

Код: Выделить всё

+--------------+-------+----------+------+-------------+---------+-----------+
| rip          | login | password | id   | port_number | balance | tariff_id |
+--------------+-------+----------+------+-------------+---------+-----------+
| 10.10.11.254 | admin | 12345    | 3739 |           8 |     12 |        47 |
| 10.10.12.254 | admin | 12345    | 3740 |           7 |     20 |        47 |
+--------------+-------+----------+------+-------------+---------+-----------+

Как к этому запросу добавить ip абонента из услуги передачи трафика id=75 ? чтобы по нему написать скрипт который будет рассылать acl по свитчам

[dwemer]

А зачем отдельную услугу передачи трафика для Локалки , если трафик по ней не будет считаться?
Только чтоб айпишник хранить? Лишняя нагрузка на биллинг и базу.

У нас локальный айпишник хранится в allowed_cid, через свои костыли все заполняется при подключение абонента. А можно и свой столбец добавить.

[Makariy]

сам себе и отвечаю:

Код: Выделить всё

SELECT a.id, u.login, INET_NTOA(ip & 4294967295) as ip, a.int_status, i.mac, inet_ntoa(4294967295 & r.router_bin_ip)  AS rip, r.login, r.password, u.port_number
FROM ip_groups i, iptraffic_service_links isl, service_links sl, users u, accounts a, routers_info r
WHERE i.is_deleted= 0
AND isl.is_deleted= 0
AND sl.is_deleted= 0
AND i.ip_group_id=isl.ip_group_id
AND isl.id=sl.id
and r.id = u.remote_switch_id
AND sl.user_id=u.id 
and sl.service_id= 75
AND a.id = u.basic_account 
and a.is_deleted = 0 
and a.int_status = 1
order by a.id;

результат:

Код: Выделить всё

+------+-------+------------+------------+-------------------+---------------+-------+----------+-------------+
| id   | login | ip         | int_status | mac               | rip           | login | password | port_number |
+------+-------+------------+------------+-------------------+---------------+-------+----------+-------------+
| 3746 | mk836 | 10.10.11.2 |          1 | 00:12:C9:33:17:1C | 172.16.11.254 | admin | 12345    |           8 |
| 3747 | mk828 | 10.10.12.2 |          1 | 00:14:04:2c:3a:01 | 172.16.12.254 | admin | 12345    |           7 |
+------+-------+------------+------------+-------------------+---------------+-------+----------+-------------+