switches:
  children:                          # 하위 그룹들
    l3_core:                         # 4500 계열 (environment 명령 다름)
      hosts:
        sw_10_20_0_2:  { ansible_host: 10.20.0.2 }
        sw_10_20_0_3:  { ansible_host: 10.20.0.3 }
    l2_access:                       # 3650 계열 (environment all 됨)
      hosts:
        sw_10_20_0_31: { ansible_host: 10.20.0.31 }
        sw_10_20_0_32: { ansible_host: 10.20.0.32 }
        # ... 나머지 L2 장비
  vars:                              # 전체 공통 접속 설정
    ansible_connection: network_cli
    ansible_network_os: cisco.ios.ios
    ansible_port: 2002
    ansible_user: "{{ sw_user }}"
    ansible_password: "{{ sw_pass }}"
    ansible_become: yes
    ansible_become_method: enable
    ansible_become_password: "{{ sw_enable }}"

pip install openpyxl --break-system-packages

- name: 점검 데이터 수집 후 JSON 저장
  hosts: switches
  gather_facts: no
  tasks:
    # L2/L3 공통으로 받는 명령
    - name: 공통 점검 명령
      cisco.ios.ios_command:
        commands:
          - show processes cpu | include CPU
          - show inventory
          - show interfaces status
          - show logging | include %
      register: common
      ignore_errors: yes

    # L2(3650)만 show environment all
    - name: L2 환경 점검
      cisco.ios.ios_command:
        commands: ["show environment all"]
      register: env_l2
      ignore_errors: yes
      when: "'l2_access' in group_names"   # L2 그룹에서만 실행

    # 장비별 결과를 JSON 한 줄로 저장 (파싱용)
    - name: 결과 JSON 저장
      delegate_to: localhost
      copy:
        content: "{{ {'host': inventory_hostname, 'ip': ansible_host, 'common': common.stdout | default([]), 'env': env_l2.stdout | default([])} | to_json }}"
        dest: "./data_{{ inventory_hostname }}.json"

import json, glob, re
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Alignment

# 색상 정의
GREEN  = PatternFill("solid", fgColor="D4EDDA")
YELLOW = PatternFill("solid", fgColor="FFF3CD")
RED    = PatternFill("solid", fgColor="F8D7DA")
HEADER = PatternFill("solid", fgColor="1A3A5C")

wb = Workbook()
ws = wb.active
ws.title = "일일점검"

# 헤더 행
cols = ["장비", "IP", "CPU(5분)", "환경", "이상로그(sev3)", "이상로그(sev4)", "종합"]
ws.append(cols)
for c in range(1, len(cols)+1):
    cell = ws.cell(row=1, column=c)
    cell.fill = HEADER
    cell.font = Font(color="FFFFFF", bold=True)
    cell.alignment = Alignment(horizontal="center")

# 각 장비 JSON 처리
for f in sorted(glob.glob("data_*.json")):
    d = json.load(open(f, encoding="utf-8"))
    common = "\n".join(d.get("common", []))
    env    = "\n".join(d.get("env", []))

    # CPU 5분 평균 최대값
    cpu_vals = [int(x) for x in re.findall(r'five minutes:\s*(\d+)%', common)]
    cpu = max(cpu_vals) if cpu_vals else None

    # 환경 비정상 라인 (L3는 env가 비어있을 수 있음)
    env_bad = len([l for l in env.splitlines()
                   if l.strip() and 'OK' not in l and 'Good' not in l
                   and ('FAN' in l or 'POWER' in l or 'TEMP' in l)])

    # 로그 심각도 카운트
    sev3 = len(re.findall(r'%\w+-3-', common))
    sev4 = len(re.findall(r'%\w+-4-', common))

    # 행 추가
    row = [d["host"], d["ip"],
           f"{cpu}%" if cpu is not None else "-",
           "OK" if env_bad == 0 else f"{env_bad}건 이상",
           sev3, sev4, ""]
    ws.append(row)
    r = ws.max_row

    # CPU 셀 색칠
    if cpu is not None:
        ws.cell(r, 3).fill = RED if cpu >= 90 else YELLOW if cpu >= 70 else GREEN

    # 로그 sev4(MAC flap 등) 다량이면 경고
    ws.cell(r, 6).fill = RED if sev4 >= 50 else YELLOW if sev4 >= 10 else GREEN

    # 종합 판정
    warn = (cpu is not None and cpu >= 70) or env_bad > 0 or sev4 >= 50
    ws.cell(r, 7).value = "⚠ 점검" if warn else "✅ 정상"
    ws.cell(r, 7).fill = YELLOW if warn else GREEN

# 열 너비 자동
for col in ws.columns:
    width = max(len(str(c.value or "")) for c in col) + 4
    ws.column_dimensions[col[0].column_letter].width = width

wb.save("switch_daily_report.xlsx")
print("리포트 생성 완료: switch_daily_report.xlsx")

# 1) 데이터 수집
ansible-playbook -i inventory.yml make_report.yml \
  -e 'sw_user=admin sw_pass=P@ssw0rd!2030 sw_enable=En@bleKey'
# 2) 엑셀 생성
python3 build_excel.py
# 3) Windows 탐색기에서 \\wsl$\Ubuntu\home\사용자\net 으로 접근해 열기