Работа с IP-адресами в SQL

Есть много инструментов, которые позволяют работать с IP адресами, масками и сетям. Так вы можете использовать калькулятор на нашем сайте, а также специализированные библиотеки на Python или JavaScript. А еще у вас есть возможность применять функции и операции прямо базах данных с помощью SQL запросов.

Не все СУБД поддерживают эти операции, но если ваши данные хранятся в PostgreSQL, то вы можете воспользоваться его встроенными средствами для работы с IP-адресами и сетями.

Если вы ранее не работали с языком SQL, то можете пройти курс по основам SQL на Shultais Education.

Ну, а мы, научимся работать с сетями из Postgresql и для рассмотрим несколько несложных примеров. Представим, что у нас есть две таблицы: одна хранит IP-адреса компьютеров, которые обращались по сети к общедоступному принтеру, а вторая список подсетей в офисе. Наша задача – подсчитать количество обращений к принтеру из разных подсетей.

Подсчет адресов в сетях

Сперва создадим сами таблицы. Для хранения данных о сетях можно использовать следующий код:

CREATE TABLE IF NOT EXISTS network (
    id integer NOT NULL DEFAULT nextval('networks_id_seq'::regclass),
    name character varying(50),
    network_ip cidr,
    CONSTRAINT networks_pkey PRIMARY KEY (id)
);

Обратите внимание на столбец network_ip cidr. cidr — это специальный тип данных для хранения информации о сети. Он позволяет записывать как IP адрес, так и маску.

Заполним таблицу двумя сетями с помощью INSERT-запроса:

INSERT INTO networks (name, network_ip)
VALUES
    ('Офис 1', '192.168.13.0/25'),
    ('Офис 2', '192.168.13.128/25');

Это небольшие сети, каждая их которых может содержать до 126 устройств.

Теперь создадим таблицу для хранения IP-адресов, которые обращались к принтеру:

CREATE TABLE IF NOT EXISTS access_log (
    date timestamp with time zone NOT NULL DEFAULT now(),
    ip_address inet NOT NULL
);

На этот раз обратите внимание на тип данных inet – он нужен непосредственно для хранения IP-адресов (без маски). Заполним таблицу тестовыми данными:

INSERT INTO access_log (ip_address) 
VALUES
    ('192.168.13.133'),
    ('192.168.13.16'),
    ('192.168.13.230'),
    ('192.168.13.8'),
    ('192.168.13.109'),
    ('192.168.12.41'),
    ('192.168.13.33'),
    ('192.168.13.208'),
    ('192.168.13.139'),
    ('192.168.13.16'),
    ('192.168.13.57');

А теперь давайте определим к какой сети относится каждый из IP адресов. Нам поможет такой SQL-запрос:

SELECT
    date,
    ip_address,
    networks.name
FROM access_log
LEFT JOIN networks ON networks.network_ip >> ip_address;

Это по-настоящему удивительный JOIN, так как внутри, для соединения записей из двух таблиц, мы используем оператор >> (contains), который сразу проверяет находится ли ip_address в сети networks.network_ip.

После выполнения мы получим такую таблицу:

Обратите внимание, что один из адресов содержит NULL, так как он находится вне заданных сетей.

Далее посчитаем сколько обращений было из каждой сети. Тут уже потребуется группировка с функциями агрегации:

SELECT
     networks.name,
     COUNT(*)
FROM access_log
LEFT JOIN networks ON networks.network_ip >> ip_address
GROUP BY networks.name
ORDER BY networks.name;

Данный SQL запрос вернет следующую таблицу:

С основной задачей мы справились.

Прочие возможности

Теперь кратко рассмотрим, какие еще есть возможности у PostgreSQL для работы с сетями.

Попробуем отправить в сеть первого офиса широковещательное сообщение, чтобы его получили все компьютеры. Для этого достаточно к адресу сети применить функцию broadcast. Напишем такой SQL-запрос:

SELECT broadcast(network_ip) FROM networks WHERE name = 'Офис 1';

Получили 192.168.13.127/25 – теперь можно спокойно отправлять сообщение всем устройствам офиса.

Или давайте получим список всех доступных адресов в сети второго офиса? Тут нам поможет функция generate_series. Она позволяет генерировать последовательности в заданном диапазоне.

Во второй сети 126 доступных адресов, поэтому нам нужна серия чисел от 1 до 126 и оператор сложения, с помощью которого можно без ошибок увеличивать IP-адреса:

SELECT
    host('192.168.13.128/25')::inet + g AS ip_address
FROM generate_series(1, 126) g
ORDER BY ip_address;

После запуска мы получим такую таблицу (первые 6 записей):

Надеемся наша небольшая статья раскрыла вам возможности Postgresql для работы с сетями и если вы используете базы данных в вашей работе, то смело можете применять полученные знания на практике.