PART 02 — システム構成図
ハードウェア・ソフトウェア構成で定義すべきこと

"""
SSH 経由でサーバーのHW・SW情報を自動収集するスクリプト。
pip install paramiko pandas openpyxl
"""
import paramiko
import pandas as pd
from typing import Optional

# ── 収集コマンド定義 ──────────────────────────────────────────
COMMANDS = {
    "hostname":      "hostname",
    "os_release":    "cat /etc/os-release | grep -E '^(NAME|VERSION)=' | tr '\n' ' '",
    "kernel":        "uname -r",
    "cpu_cores":     "nproc",
    "memory_gb":     "awk '/MemTotal/{printf \"%.1f\", $2/1024/1024}' /proc/meminfo",
    "disk_root_gb":  "df -BG / | awk 'NR==2{print $2}' | tr -d G",
    "java_version":  "java -version 2>&1 | head -1",
    "nginx_version": "nginx -v 2>&1",
    "tomcat_version":"find /opt /usr/local -name catalina.sh 2>/dev/null | head -1 | xargs -I{} sh -c 'dirname {} | xargs dirname | xargs basename'",
}


def collect_server_info(host: str, user: str, key_path: str,
                        port: int = 22) -> dict:
    """
    1台のサーバーに接続して情報を収集する。
    key_path: SSH秘密鍵のパス（~/.ssh/id_rsa など）
    """
    result = {"host": host}
    try:
        client = paramiko.SSHClient()
        client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        client.connect(host, port=port, username=user,
                       key_filename=key_path, timeout=15)

        for key, cmd in COMMANDS.items():
            stdin, stdout, stderr = client.exec_command(cmd, timeout=10)
            output = stdout.read().decode("utf-8").strip()
            result[key] = output if output else "N/A"

        client.close()
        result["status"] = "OK"

    except Exception as e:
        result["status"] = f"ERROR: {e}"

    return result


def collect_all_servers(server_list: list[dict], key_path: str) -> pd.DataFrame:
    """
    複数サーバーの情報を収集してDataFrameに変換する。
    server_list: [{"host": "192.168.10.11", "user": "ansible"}, ...]
    """
    records = []
    for srv in server_list:
        print(f"Collecting: {srv['host']} ...")
        info = collect_server_info(
            host=srv["host"],
            user=srv.get("user", "root"),
            key_path=key_path,
        )
        records.append(info)
    return pd.DataFrame(records)


if __name__ == "__main__":
    # ── 対象サーバーリスト ────────────────────────────────────
    servers = [
        {"host": "192.168.10.11", "user": "ec2-user"},
        {"host": "192.168.10.12", "user": "ec2-user"},
        {"host": "192.168.10.21", "user": "ec2-user"},
    ]
    KEY_PATH = "~/.ssh/id_rsa"

    df = collect_all_servers(servers, KEY_PATH)
    print(df.to_string(index=False))

    # Excel に出力（設計書の素材として活用）
    df.to_excel("server_inventory.xlsx", index=False)
    print("\nserver_inventory.xlsx に出力しました")

import subprocess
import platform

def get_installed_packages() -> list[dict]:
    """
    ローカルサーバーのインストール済みパッケージ一覧を取得する。
    RHEL/CentOS系: rpm -qa  / Ubuntu/Debian系: dpkg -l
    """
    system = platform.system()
    if system != "Linux":
        raise RuntimeError(f"Unsupported OS: {system}")

    # パッケージマネージャーを自動判別
    try:
        # RHEL / Rocky / Alma
        result = subprocess.run(
            ["rpm", "-qa", "--queryformat",
             "%{NAME}\t%{VERSION}-%{RELEASE}\t%{ARCH}\n"],
            capture_output=True, text=True, timeout=30
        )
        lines = result.stdout.strip().split("\n")
        return [
            {"name": p[0], "version": p[1], "arch": p[2]}
            for line in lines if (p := line.split("\t")) and len(p) == 3
        ]
    except FileNotFoundError:
        pass

    # Ubuntu / Debian
    result = subprocess.run(
        ["dpkg-query", "-W", "-f=${Package}\t${Version}\t${Architecture}\n"],
        capture_output=True, text=True, timeout=30
    )
    lines = result.stdout.strip().split("\n")
    return [
        {"name": p[0], "version": p[1], "arch": p[2]}
        for line in lines if (p := line.split("\t")) and len(p) == 3
    ]


if __name__ == "__main__":
    pkgs = get_installed_packages()
    # MW関連だけ抽出
    mw_keywords = ["nginx", "tomcat", "java", "postgresql", "mysql",
                   "redis", "httpd", "openssl", "openssh"]
    mw_pkgs = [p for p in pkgs
               if any(kw in p["name"].lower() for kw in mw_keywords)]
    for p in mw_pkgs:
        print(f"{p['name']:40s} {p['version']}")