331 lines
13 KiB
Python
331 lines
13 KiB
Python
"""
|
||
fw_report.py — генератор Excel-отчёта по правилам firewall.
|
||
Читает данные из fw_settings.py и создаёт файл fw_report.xlsx со страницами:
|
||
- Правила (Rules)
|
||
- Объекты (Objects)
|
||
- Группы объектов (Groups)
|
||
- Сервисы (Services)
|
||
- Группы сервисов (Service Groups)
|
||
"""
|
||
|
||
import sys
|
||
import os
|
||
|
||
# Добавляем текущую директорию в путь, чтобы импортировать fw_settings
|
||
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
|
||
|
||
from fw_settings import servers, nets, groups, services, service_groups, rules
|
||
|
||
from openpyxl import Workbook
|
||
from openpyxl.styles import (
|
||
Font, PatternFill, Alignment, Border, Side, GradientFill
|
||
)
|
||
from openpyxl.utils import get_column_letter
|
||
|
||
# ─── Цветовая палитра ────────────────────────────────────────────────────────
|
||
CLR_HEADER_BG = "1F4E79" # тёмно-синий — заголовки таблиц
|
||
CLR_HEADER_FG = "FFFFFF" # белый текст
|
||
CLR_SPAN_BG = "D6E4F0" # голубой — строки-разделители (span)
|
||
CLR_SPAN_FG = "1F4E79" # тёмно-синий текст
|
||
CLR_ALLOW_BG = "E2EFDA" # светло-зелёный — allow
|
||
CLR_DENY_BG = "FCE4D6" # светло-красный — deny
|
||
CLR_ALT_BG = "F2F2F2" # светло-серый — чётные строки
|
||
CLR_WHITE = "FFFFFF"
|
||
|
||
THIN = Side(style="thin", color="BFBFBF")
|
||
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
|
||
|
||
|
||
def make_fill(hex_color: str) -> PatternFill:
|
||
return PatternFill("solid", fgColor=hex_color)
|
||
|
||
|
||
def header_font(bold=True) -> Font:
|
||
return Font(name="Calibri", bold=bold, color=CLR_HEADER_FG, size=11)
|
||
|
||
|
||
def cell_font(bold=False, color="000000") -> Font:
|
||
return Font(name="Calibri", bold=bold, color=color, size=10)
|
||
|
||
|
||
def apply_header_row(ws, row: int, headers: list[str]):
|
||
"""Записывает строку заголовков с форматированием."""
|
||
for col, text in enumerate(headers, start=1):
|
||
c = ws.cell(row=row, column=col, value=text)
|
||
c.font = header_font()
|
||
c.fill = make_fill(CLR_HEADER_BG)
|
||
c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
|
||
c.border = BORDER
|
||
|
||
|
||
def set_col_widths(ws, widths: list[int]):
|
||
for i, w in enumerate(widths, start=1):
|
||
ws.column_dimensions[get_column_letter(i)].width = w
|
||
|
||
|
||
def fmt_cell(ws, row, col, value, bold=False, bg=None, align="left", wrap=True, color="000000"):
|
||
c = ws.cell(row=row, column=col, value=value)
|
||
c.font = cell_font(bold=bold, color=color)
|
||
c.alignment = Alignment(horizontal=align, vertical="center", wrap_text=wrap)
|
||
c.border = BORDER
|
||
if bg:
|
||
c.fill = make_fill(bg)
|
||
return c
|
||
|
||
|
||
# ─── Вспомогательные функции для извлечения имён ────────────────────────────
|
||
|
||
def obj_display(obj: dict) -> str:
|
||
"""Возвращает строку вида 'hostname (ip/prefix)' или 'name' для группы."""
|
||
if "ip" in obj and "prefix" in obj:
|
||
return f"{obj.get('hostname', '')} ({obj['ip']}/{obj['prefix']})"
|
||
if "hostname" in obj:
|
||
return obj["hostname"]
|
||
if "name" in obj:
|
||
return obj["name"]
|
||
return str(obj)
|
||
|
||
|
||
def group_display(grp: dict) -> str:
|
||
return grp.get("name", "")
|
||
|
||
|
||
def svc_display(svc: dict) -> str:
|
||
return svc.get("name", "")
|
||
|
||
|
||
def list_to_str(items, fn) -> str:
|
||
if not items:
|
||
return "—"
|
||
return "\n".join(fn(i) for i in items)
|
||
|
||
|
||
# ─── Лист: Объекты ───────────────────────────────────────────────────────────
|
||
|
||
def build_objects_sheet(wb: Workbook):
|
||
ws = wb.create_sheet("Объекты")
|
||
ws.freeze_panes = "A2"
|
||
|
||
headers = ["Имя", "Тип", "IP-адрес", "Префикс", "Шлюз", "Домен", "Описание"]
|
||
apply_header_row(ws, 1, headers)
|
||
set_col_widths(ws, [20, 10, 16, 8, 16, 16, 40])
|
||
|
||
all_objects = {}
|
||
for k, v in servers.items():
|
||
all_objects[k] = v
|
||
for k, v in nets.items():
|
||
all_objects[k] = v
|
||
|
||
for row_idx, (key, obj) in enumerate(all_objects.items(), start=2):
|
||
bg = CLR_WHITE if row_idx % 2 == 0 else CLR_ALT_BG
|
||
fmt_cell(ws, row_idx, 1, obj.get("hostname", key), bg=bg)
|
||
fmt_cell(ws, row_idx, 2, obj.get("type", ""), bg=bg, align="center")
|
||
fmt_cell(ws, row_idx, 3, obj.get("ip", ""), bg=bg, align="center")
|
||
fmt_cell(ws, row_idx, 4, str(obj.get("prefix", "")), bg=bg, align="center")
|
||
fmt_cell(ws, row_idx, 5, obj.get("gw", ""), bg=bg, align="center")
|
||
fmt_cell(ws, row_idx, 6, obj.get("domain", ""), bg=bg)
|
||
fmt_cell(ws, row_idx, 7, obj.get("description", ""), bg=bg)
|
||
|
||
ws.row_dimensions[1].height = 30
|
||
|
||
|
||
# ─── Лист: Группы объектов ───────────────────────────────────────────────────
|
||
|
||
def build_groups_sheet(wb: Workbook):
|
||
ws = wb.create_sheet("Группы объектов")
|
||
ws.freeze_panes = "A2"
|
||
|
||
headers = ["Имя группы", "Элементы группы"]
|
||
apply_header_row(ws, 1, headers)
|
||
set_col_widths(ws, [35, 60])
|
||
|
||
for row_idx, (key, grp) in enumerate(groups.items(), start=2):
|
||
bg = CLR_WHITE if row_idx % 2 == 0 else CLR_ALT_BG
|
||
items = grp.get("items", [])
|
||
items_str = "\n".join(i.get("hostname", str(i)) for i in items) if items else "—"
|
||
fmt_cell(ws, row_idx, 1, grp.get("name", key), bold=True, bg=bg)
|
||
c = fmt_cell(ws, row_idx, 2, items_str, bg=bg)
|
||
# авто-высота строки по числу элементов
|
||
ws.row_dimensions[row_idx].height = max(15, 15 * max(1, len(items)))
|
||
|
||
ws.row_dimensions[1].height = 30
|
||
|
||
|
||
# ─── Лист: Сервисы ───────────────────────────────────────────────────────────
|
||
|
||
def build_services_sheet(wb: Workbook):
|
||
ws = wb.create_sheet("Сервисы")
|
||
ws.freeze_panes = "A2"
|
||
|
||
headers = ["Ключ", "Имя сервиса", "Протокол", "Порт источника", "Порт назначения"]
|
||
apply_header_row(ws, 1, headers)
|
||
set_col_widths(ws, [28, 38, 14, 18, 18])
|
||
|
||
for row_idx, (key, svc) in enumerate(services.items(), start=2):
|
||
bg = CLR_WHITE if row_idx % 2 == 0 else CLR_ALT_BG
|
||
fmt_cell(ws, row_idx, 1, key, bg=bg)
|
||
fmt_cell(ws, row_idx, 2, svc.get("name", ""), bg=bg)
|
||
fmt_cell(ws, row_idx, 3, svc.get("proto", ""), bg=bg, align="center")
|
||
fmt_cell(ws, row_idx, 4, svc.get("sport", ""), bg=bg, align="center")
|
||
fmt_cell(ws, row_idx, 5, svc.get("dport", ""), bg=bg, align="center")
|
||
|
||
ws.row_dimensions[1].height = 30
|
||
|
||
|
||
# ─── Лист: Группы сервисов ───────────────────────────────────────────────────
|
||
|
||
def build_service_groups_sheet(wb: Workbook):
|
||
ws = wb.create_sheet("Группы сервисов")
|
||
ws.freeze_panes = "A2"
|
||
|
||
headers = ["Ключ", "Имя группы", "Сервисы (имя | протокол | dport)"]
|
||
apply_header_row(ws, 1, headers)
|
||
set_col_widths(ws, [25, 35, 60])
|
||
|
||
for row_idx, (key, sg) in enumerate(service_groups.items(), start=2):
|
||
bg = CLR_WHITE if row_idx % 2 == 0 else CLR_ALT_BG
|
||
items = sg.get("items", [])
|
||
lines = []
|
||
for svc in items:
|
||
lines.append(
|
||
f"{svc.get('name','')} | {svc.get('proto','')} | {svc.get('dport','')}"
|
||
)
|
||
items_str = "\n".join(lines) if lines else "—"
|
||
fmt_cell(ws, row_idx, 1, key, bg=bg)
|
||
fmt_cell(ws, row_idx, 2, sg.get("name", key), bold=True, bg=bg)
|
||
fmt_cell(ws, row_idx, 3, items_str, bg=bg)
|
||
ws.row_dimensions[row_idx].height = max(15, 15 * max(1, len(items)))
|
||
|
||
ws.row_dimensions[1].height = 30
|
||
|
||
|
||
# ─── Лист: Правила ───────────────────────────────────────────────────────────
|
||
|
||
def _extract_src_dst(lst) -> str:
|
||
"""Преобразует список src/dst (может содержать dict серверов или групп) в строку."""
|
||
if not lst:
|
||
return "—"
|
||
parts = []
|
||
for item in lst:
|
||
if not isinstance(item, dict):
|
||
parts.append(str(item))
|
||
continue
|
||
# Это группа (есть ключ 'items') или сервер/сеть (есть ключ 'ip')
|
||
if "items" in item:
|
||
parts.append(item.get("name", "?"))
|
||
elif "ip" in item:
|
||
hostname = item.get("hostname", "")
|
||
ip = item.get("ip", "")
|
||
prefix = item.get("prefix", "")
|
||
parts.append(f"{hostname} ({ip}/{prefix})")
|
||
else:
|
||
parts.append(item.get("hostname") or item.get("name") or str(item))
|
||
return "\n".join(parts)
|
||
|
||
|
||
def _extract_services(svc_list, sg_list) -> str:
|
||
parts = []
|
||
if svc_list:
|
||
for s in svc_list:
|
||
if isinstance(s, dict):
|
||
parts.append(s.get("name", str(s)))
|
||
if sg_list:
|
||
for sg in sg_list:
|
||
if isinstance(sg, dict):
|
||
parts.append(f"[{sg.get('name', str(sg))}]")
|
||
return "\n".join(parts) if parts else "—"
|
||
|
||
|
||
def build_rules_sheet(wb: Workbook):
|
||
ws = wb.create_sheet("Правила", 0) # первый лист
|
||
ws.freeze_panes = "A3"
|
||
|
||
headers = [
|
||
"№", "Порядок", "Имя правила", "Описание",
|
||
"Источник", "Назначение", "Сервисы",
|
||
"Действие", "Лог", "IDP", "Affinity"
|
||
]
|
||
apply_header_row(ws, 1, headers)
|
||
set_col_widths(ws, [5, 8, 28, 40, 30, 30, 35, 10, 6, 6, 25])
|
||
|
||
rule_num = 0
|
||
for row_idx, rule in enumerate(rules, start=2):
|
||
rtype = rule.get("type", "rule")
|
||
|
||
if rtype == "span":
|
||
# Строка-разделитель (заголовок секции)
|
||
ws.merge_cells(
|
||
start_row=row_idx, start_column=1,
|
||
end_row=row_idx, end_column=len(headers)
|
||
)
|
||
c = ws.cell(row=row_idx, column=1, value=rule.get("name", ""))
|
||
c.font = Font(name="Calibri", bold=True, color=CLR_SPAN_FG, size=11)
|
||
c.fill = make_fill(CLR_SPAN_BG)
|
||
c.alignment = Alignment(horizontal="center", vertical="center")
|
||
c.border = BORDER
|
||
ws.row_dimensions[row_idx].height = 22
|
||
continue
|
||
|
||
# Обычное правило
|
||
rule_num += 1
|
||
action = rule.get("action", "")
|
||
if action == "allow":
|
||
bg = CLR_ALLOW_BG
|
||
elif action in ("deny", "drop", "reject"):
|
||
bg = CLR_DENY_BG
|
||
else:
|
||
bg = CLR_WHITE if row_idx % 2 == 0 else CLR_ALT_BG
|
||
|
||
src_str = _extract_src_dst(rule.get("src_list"))
|
||
dst_str = _extract_src_dst(rule.get("dst_list"))
|
||
svc_str = _extract_services(
|
||
rule.get("service_list"), rule.get("service_group_list")
|
||
)
|
||
affinity_str = ", ".join(rule.get("affinity", []))
|
||
|
||
fmt_cell(ws, row_idx, 1, rule_num, bg=bg, align="center")
|
||
fmt_cell(ws, row_idx, 2, rule.get("order", ""), bg=bg, align="center")
|
||
fmt_cell(ws, row_idx, 3, rule.get("name", ""), bg=bg, bold=True)
|
||
fmt_cell(ws, row_idx, 4, rule.get("description", ""), bg=bg)
|
||
fmt_cell(ws, row_idx, 5, src_str, bg=bg)
|
||
fmt_cell(ws, row_idx, 6, dst_str, bg=bg)
|
||
fmt_cell(ws, row_idx, 7, svc_str, bg=bg)
|
||
fmt_cell(ws, row_idx, 8, action, bg=bg, align="center", bold=True)
|
||
fmt_cell(ws, row_idx, 9, rule.get("log", ""), bg=bg, align="center")
|
||
fmt_cell(ws, row_idx, 10, rule.get("idp", ""), bg=bg, align="center")
|
||
fmt_cell(ws, row_idx, 11, affinity_str, bg=bg)
|
||
|
||
# Высота строки — по числу строк в самом длинном поле
|
||
max_lines = max(
|
||
len(src_str.split("\n")),
|
||
len(dst_str.split("\n")),
|
||
len(svc_str.split("\n")),
|
||
1
|
||
)
|
||
ws.row_dimensions[row_idx].height = max(18, 15 * max_lines)
|
||
|
||
ws.row_dimensions[1].height = 30
|
||
|
||
|
||
# ─── Главная функция ─────────────────────────────────────────────────────────
|
||
|
||
def main():
|
||
output_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), "fw_report.xlsx")
|
||
|
||
wb = Workbook()
|
||
# Удаляем дефолтный лист
|
||
wb.remove(wb.active)
|
||
|
||
build_rules_sheet(wb)
|
||
build_objects_sheet(wb)
|
||
build_groups_sheet(wb)
|
||
build_services_sheet(wb)
|
||
build_service_groups_sheet(wb)
|
||
|
||
wb.save(output_file)
|
||
print(f"Отчёт сохранён: {output_file}")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|