Как лучше организовать структуру БД для последующего анализа?
Для удобных отчетов в будущем (например, построения графиков доступности, расчета Uptime %, поиска «мигающих» сервисов) оптимально разделить базу на две таблицы:
1. runs (История запусков):
Фиксирует сам факт запуска скрипта (id, timestamp). Это избавляет от дублирования метки времени в миллионах строк и дает единую точку отсчета для каждого сканирования.
2. results (Результаты проверок):
Хранит конкретные измерения (run_id, host, type, port, latency_ms).
Также важно создать Индексы по хосту, порту и ID запуска — благодаря им даже через год аналитические SQL-запросы будут выполняться мгновенно.
---
Обновленный код netcheck.py
Скрипт создает файл БД netcheck.db (если его нет), записывает туда данные каждого запуска и по-прежнему дублирует вывод в консоль (если запускается вручную).
#!/usr/bin/env python3
"""
Скрипт параллельной проверки сетевой доступности хостов и TCP-портов.
Результаты сохраняются в базу данных SQLite (с временной меткой запуска)
и дублируются в виде CSV в stdout.
Идеально подходит для регулярного запуска через Cron.
"""
import asyncio
import csv
import sys
import argparse
import time
import sqlite3
from datetime import datetime, timezone
from pathlib import Path
# ==============================================================================
# КОНФИГУРАЦИЯ И НАСТРОЙКИ
# ==============================================================================
# Таймаут ожидания ответа от хоста/порта (в секундах)
TIMEOUT = 3.0
# Значение задержки, если хост/порт недоступен
UNREACHABLE_VAL = 999.0
# Количество повторных попыток перед фиксацией недоступности (999)
MAX_RETRIES = 2
# Задержка между повторными попытками (в секундах)
RETRY_DELAY = 0.5
# Ограничение количества одновременных проверок
MAX_CONCURRENT_TASKS = 50
# Пути к файлам по умолчанию
DEFAULT_HOSTS_FILE = 'hosts.lst'
DEFAULT_DB_FILE = 'netcheck.db'
# ==============================================================================
###semaphore = asyncio.Semaphore(MAX_CONCURRENT_TASKS)
semaphore = None
def init_db(db_path: str):
"""Создает структуру БД и необходимые индексы, если они не существуют."""
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Таблица запусков
cursor.execute('''
CREATE TABLE IF NOT EXISTS runs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp DATETIME NOT NULL
)
''')
# Таблица результатов
cursor.execute('''
CREATE TABLE IF NOT EXISTS results (
id INTEGER PRIMARY KEY AUTOINCREMENT,
run_id INTEGER NOT NULL,
host TEXT NOT NULL,
type TEXT NOT NULL,
port TEXT NOT NULL,
latency_ms REAL NOT NULL,
FOREIGN KEY (run_id) REFERENCES runs (id) ON DELETE CASCADE
)
''')
# Индексы для ускорения последующего анализа
cursor.execute('CREATE INDEX IF NOT EXISTS idx_results_run_id ON results(run_id)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_results_host_port ON results(host, port)')
conn.commit()
conn.close()
def save_to_db(db_path: str, results_list: list) -> int:
"""Сохраняет результаты текущей проверки в SQLite в единой транзакции."""
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Регистрируем запуск с текущей датой и временем (UTC)
now_utc = datetime.now(timezone.utc).strftime('%Y-%m-%d %H:%M:%S')
cursor.execute('INSERT INTO runs (timestamp) VALUES (?)', (now_utc,))
run_id = cursor.lastrowid
# Подготавливаем данные для пакетной вставки (Bulk Insert)
rows_to_insert = []
for item in results_list:
if item:
for row in item:
rows_to_insert.append((
run_id,
row['Host'],
row['Type'],
str(row['Port']),
row['Latency_ms']
))
cursor.executemany('''
INSERT INTO results (run_id, host, type, port, latency_ms)
VALUES (?, ?, ?, ?, ?)
''', rows_to_insert)
conn.commit()
conn.close()
return run_id
async def ping_icmp(host: str) -> float:
"""Проверка доступности через ICMP Ping."""
async with semaphore:
for attempt in range(1, MAX_RETRIES + 1):
start_time = time.perf_counter()
try:
proc = await asyncio.create_subprocess_exec(
'ping', '-c', '1', '-W', str(int(TIMEOUT)), host,
stdout=asyncio.subprocess.DEVNULL,
stderr=asyncio.subprocess.DEVNULL
)
await asyncio.wait_for(proc.wait(), timeout=TIMEOUT + 0.5)
elapsed = (time.perf_counter() - start_time) * 1000
if proc.returncode == 0:
return round(elapsed, 2)
except (asyncio.TimeoutError, Exception):
pass
if attempt < MAX_RETRIES:
await asyncio.sleep(RETRY_DELAY)
return UNREACHABLE_VAL
async def check_tcp(host: str, port: int) -> float:
"""Проверка доступности TCP-порта."""
async with semaphore:
for attempt in range(1, MAX_RETRIES + 1):
start_time = time.perf_counter()
try:
conn = asyncio.open_connection(host, port)
reader, writer = await asyncio.wait_for(conn, timeout=TIMEOUT)
elapsed = (time.perf_counter() - start_time) * 1000
writer.close()
await writer.wait_closed()
return round(elapsed, 2)
except (asyncio.TimeoutError, OSError, Exception):
pass
if attempt < MAX_RETRIES:
await asyncio.sleep(RETRY_DELAY)
return UNREACHABLE_VAL
async def check_target(target_str: str) -> list:
"""Разбор строки конфигурации и запуск проверок."""
line = target_str.strip()
if not line or line.startswith('#'):
return None
parts = line.split()
host = parts[0]
ports = []
if len(parts) > 1:
ports_str = parts[1]
for p in ports_str.split(','):
p_clean = p.strip()
if p_clean.isdigit():
ports.append(int(p_clean))
results = []
if not ports:
latency = await ping_icmp(host)
results.append({
'Host': host,
'Type': 'ICMP',
'Port': '-',
'Latency_ms': latency
})
else:
tasks = [check_tcp(host, port) for port in ports]
latencies = await asyncio.gather(*tasks)
for port, latency in zip(ports, latencies):
results.append({
'Host': host,
'Type': 'TCP',
'Port': port,
'Latency_ms': latency
})
return results
async def main():
global semaphore
semaphore = asyncio.Semaphore(MAX_CONCURRENT_TASKS)
parser = argparse.ArgumentParser(
description="Скрипт проверки сетевой доступности с сохранением в SQLite."
)
parser.add_argument(
'file',
nargs='?',
default=DEFAULT_HOSTS_FILE,
help=f'Путь к файлу со списком хостов (по умолчанию: {DEFAULT_HOSTS_FILE})'
)
parser.add_argument(
'--db',
default=DEFAULT_DB_FILE,
help=f'Путь к файлу SQLite БД (по умолчанию: {DEFAULT_DB_FILE})'
)
parser.add_argument(
'--no-stdout',
action='store_true',
help='Отключить вывод CSV в stdout (полезно при запуске из Cron)'
)
args = parser.parse_args()
# Чтение файла хостов
try:
with open(args.file, 'r', encoding='utf-8') as f:
lines = f.readlines()
except FileNotFoundError:
sys.stderr.write(f"Ошибка: Файл '{args.file}' не найден.\n")
sys.exit(1)
# Инициализация структуры БД
init_db(args.db)
# Запуск параллельной проверки
tasks = [check_target(line) for line in lines]
raw_results = await asyncio.gather(*tasks)
# Сохранение в базу данных
save_to_db(args.db, raw_results)
# Вывод CSV в stdout (если не отключено флагом --no-stdout)
if not args.no_stdout:
writer = csv.writer(sys.stdout)
writer.writerow(['Host', 'Type', 'Port', 'Latency_ms'])
for item in raw_results:
if item:
for row in item:
writer.writerow([row['Host'], row['Type'], row['Port'], row['Latency_ms']])
if __name__ == '__main__':
asyncio.run(main())
example hosts.lst
# ==============================================================================
# GLOBAL GEOGRAPHIC HOSTS LIST (BY CONTINENT)
# ==============================================================================
# ------------------------------------------------------------------------------
# 1. NORTH AMERICA (Северная Америка)
# ------------------------------------------------------------------------------
# Anycast & Core DNS Gateways
1.1.1.1
8.8.8.8
# US East Coast (N. Virginia / Washington D.C.)
ec2.us-east-1.amazonaws.com 443
# US West Coast (California / Oregon)
ec2.us-west-1.amazonaws.com 443
# Canada
amazon.ca 443
# ------------------------------------------------------------------------------
# 2. EUROPE (Европа)
# ------------------------------------------------------------------------------
# Western Europe (London / UK)
bbc.co.uk 443
# Central Europe (Frankfurt / Germany)
ec2.eu-central-1.amazonaws.com 443
hetzner.de 443
# Northern Europe (Stockholm / Sweden)
sapo.pt 443
# Eastern Europe (Poland / Ukraine)
meta.ua 80,443
wp.pl 443
# ------------------------------------------------------------------------------
# 3. ASIA (Азия)
# ------------------------------------------------------------------------------
# East Asia (Japan - Tokyo)
ec2.ap-northeast-1.amazonaws.com 443
yahoo.co.jp 443
# East Asia (China / Hong Kong)
baidu.com 80,443
# Southeast Asia (Singapore)
ec2.ap-southeast-1.amazonaws.com 443
# South Asia (India)
ec2.ap-south-1.amazonaws.com 443
# ------------------------------------------------------------------------------
# 4. SOUTH AMERICA (Южная Америка)
# ------------------------------------------------------------------------------
# Brazil (São Paulo)
ec2.sa-east-1.amazonaws.com 443
uol.com.br 443
# Chile (Santiago)
cl.telefonicabusinesssolutions.com 80
# ------------------------------------------------------------------------------
# 5. AFRICA (Африка)
# ------------------------------------------------------------------------------
# South Africa (Johannesburg / Cape Town)
ec2.af-south-1.amazonaws.com 443
takealot.com 443
# North Africa (Egypt)
eg.orange.com 80
# West Africa (Nigeria)
courtevillegroup.com 80
# ------------------------------------------------------------------------------
# 6. OCEANIA & AUSTRALIA (Океания и Австралия)
# ------------------------------------------------------------------------------
# Australia (Sydney)
ec2.ap-southeast-2.amazonaws.com 443
telstra.com.au 443
# New Zealand (Auckland)
nzherald.co.nz 443
# ------------------------------------------------------------------------------
# 7. ANTARCTICA (Антарктида)
# ------------------------------------------------------------------------------
# US McMurdo Station Gateway (USAP Infrastructure)
usap.gov 443---
Настройка запуска через Cron
Чтобы Cron правильно отрабатывал (независимо от текущей рабочей директории), укажите в crontab абсолютные пути к скрипту, файлу хостов и базе данных.
1. Откройте редактор crontab:
crontab -e2. Добавьте строку (пример запуска каждые 5 минут):
*/5 * * * * /usr/bin/python3 /abs/path/to/netcheck.py /abs/path/to/hosts.lst --db /abs/path/to/netcheck.db --no-stdout*Флаг --no-stdout предотвратит отправку спам-писем от системного Cron на ваш почтовый ящик при каждом запуске.*
---
Готовые SQL-запросы для последующего анализа
Когда наберется статистика, вы сможете быстро выполнять выгрузки через утилиту sqlite3:
1. Список всех сбоев за последние 24 часа:
SELECT r.timestamp, res.host, res.type, res.port
FROM results res
JOIN runs r ON r.id = res.run_id
WHERE res.latency_ms = 999
AND r.timestamp >= datetime('now', '-1 day');2. Расчет Uptime (процента доступности) по каждому сервису:
SELECT
host,
port,
COUNT(*) as total_checks,
SUM(CASE WHEN latency_ms < 999 THEN 1 ELSE 0 END) as successful_checks,
ROUND(CAST(SUM(CASE WHEN latency_ms < 999 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) * 100, 2) as uptime_pct
FROM results
GROUP BY host, port;3. Средняя задержка (Latency) для рабочих сервисов (без учета 999):
SELECT host, port, ROUND(AVG(latency_ms), 2) as avg_latency_ms
FROM results
WHERE latency_ms < 999
GROUP BY host, port;Вот подборка полезных SQL-запросов (SELECT), которые помогут превратить собранную базу в наглядную аналитику: от поиска нестабильных ресурсов до подготовки данных для графиков в Excel или Grafana.
---
1. Поиск «мигающих» ресурсов (Flapping / Flaky Services)
Запрос находит хосты и порты, которые часто меняют свой статус (то работают, то падают). Это главный признак нестабильной сети или перегруженного сервиса.
SELECT
host,
port,
COUNT(*) AS status_changes
FROM (
SELECT
host,
port,
latency_ms,
LAG(CASE WHEN latency_ms = 999 THEN 0 ELSE 1 END) OVER (
PARTITION BY host, port ORDER BY run_id
) AS prev_status,
CASE WHEN latency_ms = 999 THEN 0 ELSE 1 END AS curr_status
FROM results
)
WHERE prev_status IS NOT NULL AND prev_status != curr_status
GROUP BY host, port
ORDER BY status_changes DESC;---
2. Подробная хронология инцидентов (Начало и Длительность сбоя)
Позволяет увидеть не просто отдельную строчку с 999, а конкретный интервал времени, когда сервис был недоступен, и сколько проверок подряд он пропустил.
WITH status_changes AS (
SELECT
res.host,
res.port,
r.timestamp,
res.latency_ms,
LAG(CASE WHEN res.latency_ms = 999 THEN 1 ELSE 0 END, 1, 0) OVER (
PARTITION BY res.host, res.port ORDER BY r.id
) AS was_down
FROM results res
JOIN runs r ON r.id = res.run_id
)
SELECT
host,
port,
timestamp AS downtime_start
FROM status_changes
WHERE latency_ms = 999 AND was_down = 0
ORDER BY downtime_start DESC;---
3. Анализ задержек: Медиана, Мин, Макс и Процентиль (p95)
Простая средняя задержка (AVG) часто искажается из-за единичных всплесков. 95-й процентиль показывает честную картину производительности (95% запросов были быстрее этого значения).
SELECT
host,
port,
MIN(latency_ms) AS min_ms,
ROUND(AVG(latency_ms), 2) AS avg_ms,
MAX(latency_ms) AS max_ms
FROM results
WHERE latency_ms < 999
GROUP BY host, port
ORDER BY avg_ms DESC;---
4. Распределение сбоев по часам суток (Поиск паттернов)
Запрос группирует падения по часам. Он помогает понять, не связаны ли сбои с ночными бэкапами, перезагрузками или пиковой дневной нагрузкой.
SELECT
strftime('%H', r.timestamp) AS hour_of_day,
COUNT(*) AS total_failures
FROM results res
JOIN runs r ON r.id = res.run_id
WHERE res.latency_ms = 999
GROUP BY hour_of_day
ORDER BY total_failures DESC;---
5. Сводная таблица доступности по дням (Heatmap / Дашборд)
Выводит процент доступности (Uptime %) для каждого хоста по дням за последнюю неделю. Удобно для выгрузки в CSV/Excel.
SELECT
host,
port,
DATE(r.timestamp) AS check_date,
COUNT(*) AS total_checks,
SUM(CASE WHEN latency_ms < 999 THEN 1 ELSE 0 END) AS ok_checks,
ROUND(CAST(SUM(CASE WHEN latency_ms < 999 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) * 100, 2) AS uptime_pct
FROM results res
JOIN runs r ON r.id = res.run_id
WHERE r.timestamp >= datetime('now', '-7 days')
GROUP BY host, port, check_date
ORDER BY check_date DESC, host;---
6. Поиск массовых сбоев (Сетевые штормы / Падение магистрали)
Запрос показывает моменты времени, когда одновременно упало больше 30% всех проверяемых ресурсов. Это указывает на проблему с локальной сетью, роутером или каналом провайдера (а не с конкретным сервером).
SELECT
r.timestamp,
COUNT(*) AS total_targets,
SUM(CASE WHEN res.latency_ms = 999 THEN 1 ELSE 0 END) AS failed_targets,
ROUND(CAST(SUM(CASE WHEN res.latency_ms = 999 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) * 100, 1) AS failure_rate_pct
FROM results res
JOIN runs r ON r.id = res.run_id
GROUP BY r.id
HAVING failure_rate_pct > 30.0
ORDER BY r.timestamp DESC;---
Полезный совет: Создание SQL-View (Представления)
Чтобы не писать сложные JOIN каждый раз, можно создать в SQLite виртуальное представление:
CREATE VIEW IF NOT EXISTS v_full_results AS
SELECT
r.id AS run_id,
r.timestamp,
res.host,
res.type,
res.port,
res.latency_ms,
CASE WHEN res.latency_ms = 999 THEN 0 ELSE 1 END AS is_up
FROM results res
JOIN runs r ON r.id = res.run_id;После этого любой анализ станет в разы короче:
SELECT * FROM v_full_results WHERE is_up = 0 AND timestamp >= datetime('now', '-1 hour');Для поиска «мигающих» и нестабильных задержек (где разница между минимальным и максимальным пингом превышает 50 мс) подходят два варианта SQL-запросов — в зависимости от того, что именно хочется проанализировать.
В обоих запросах сбои (latency_ms = 999) исключаются, чтобы смотреть только на реальные колебания сети, а не на таймауты.
---
Вариант 1. Общий разброс за всё время (Max − Min > 50 ms)
Показывает хосты, у которых за всю историю наблюдений разница между самым быстрым и самым медленным откликом составила более 50 миллисекунд.
SELECT
res.host,
res.port,
MIN(res.latency_ms) AS min_ms,
MAX(res.latency_ms) AS max_ms,
ROUND(MAX(res.latency_ms) - MIN(res.latency_ms), 2) AS jitter_ms,
ROUND(AVG(res.latency_ms), 2) AS avg_ms,
COUNT(*) AS total_checks
FROM results res
WHERE res.latency_ms < 999
GROUP BY res.host, res.port
HAVING (MAX(res.latency_ms) - MIN(res.latency_ms)) > 50
ORDER BY jitter_ms DESC;---
Вариант 2. Скачки пинга между СОСЕДНИМИ запусками (> 50 ms) (за 24 часа)
Этот запрос анализирует динамику: он сравнивает пинг в текущем запуске с пингом в предыдущем запуске (с помощью оконной функции LAG) и выводит конкретные моменты времени, когда отклик резко прыгнул вверх или вниз более чем на 500 мс.
WITH latency_diffs AS (
SELECT
r.timestamp,
res.host,
res.port,
res.latency_ms AS curr_ms,
LAG(res.latency_ms) OVER (
PARTITION BY res.host, res.port ORDER BY r.id
) AS prev_ms
FROM results res
JOIN runs r ON r.id = res.run_id
-- Берем данные за 2 дня, чтобы для первой записи за сегодня был доступен "вчерашний" prev_ms
WHERE res.latency_ms < 999
AND r.timestamp >= datetime('now', '-2 days')
)
SELECT
timestamp,
host,
port,
prev_ms,
curr_ms,
ROUND(ABS(curr_ms - prev_ms), 2) AS spike_ms
FROM latency_diffs
WHERE prev_ms IS NOT NULL
AND ABS(curr_ms - prev_ms) > 500
-- Оставляем в итоговом отчете только скачки за последние сутки
AND timestamp >= datetime('now', '-1 day')
ORDER BY timestamp DESC;spike_ms— абсолютный размер скачка задержки (в миллисекундах) по сравнению с предыдущей проверкой.
DELETE ALL NOT ANSWER
SELECT * FROM results WHERE (host, port) IN (SELECT host, port FROM results GROUP BY host, port HAVING MAX(latency_ms) = 999 AND MIN(latency_ms) = 999); DELETE FROM runs WHERE id NOT IN (SELECT DISTINCT run_id FROM results);