CDP Audit

.
├── cdp_audit/                   # Main Python package
│   ├── __init__.py              # Package initialization and version
│   ├── __main__.py              # Package entry point (python -m cdp_audit)
│   ├── app_config.py            # Centralized configuration access
│   ├── cli.py                   # Command-line interface and main flow
│   ├── credentials.py           # Credential management (Cred Manager integration)
│   ├── discovery.py             # Network discovery engine (threading, CDP crawl)
│   ├── excel_reporter.py        # Excel report generation (template-driven)
│   ├── logging_setup.py         # Logging configuration bootstrap
│   └── validators.py            # Pre-flight validation (templates, config)
├── ProgramFiles/
│   ├── textfsm/
│   │   ├── cisco_ios_show_cdp_neighbors_detail.textfsm
│   │   └── cisco_ios_show_version.textfsm
│   └── config_files/
│       ├── config_loader.py     # YAML config loader class
│       ├── config.py            # Backward compatibility wrapper
│       ├── 1 - CDP Network Audit _ Template.xlsx
│       └── logging.conf         # Optional logging configuration
├── config.yaml                  # Main configuration file (YAML)
├── requirements.txt             # Python dependencies
├── run.bat                      # Windows launcher script
└── portable_env/                # Portable Python environment (optional)

pip install pandas openpyxl textfsm paramiko netmiko pyyaml pywin32

network:
  jump_host: "192.0.2.10"     # Default jump/bastion server (empty = direct)
  device_type: "cisco_ios"    # Netmiko device type
  ssh_port: 22                # SSH port for connections
  default_limit: 10           # Max concurrent worker threads
  default_timeout: 10         # SSH/auth/read timeouts (seconds)
  max_retry_attempts: 3       # Connection retries per device
  dns_max_workers: 32         # Max DNS resolution threads
  dns_min_workers: 4          # Min DNS resolution threads

credentials:
  cred_target: "MyApp/ADM"         # Primary credential target (Windows Credential Manager)
  alt_creds: "MyApp/Answer"        # Fallback credential target
  cdp_fallback_username: "answer"  # Fallback username (customizable)

file_paths:
  base_dir: "."  # Project root
  cdp_template: "ProgramFiles/textfsm/cisco_ios_show_cdp_neighbors_detail.textfsm"
  ver_template: "ProgramFiles/textfsm/cisco_ios_show_version.textfsm"
  excel_template: "ProgramFiles/config_files/1 - CDP Network Audit _ Template.xlsx"
  logging_config: "ProgramFiles/config_files/logging.conf"

excel:
  sheets:
    audit: "Audit"
    dns: "DNS Resolved"
    auth_errors: "Authentication Errors"
    conn_errors: "Connection Errors"
  metadata_cells:
    site_name: "B4"
    date: "B5"
    time: "B6"
    primary_seed: "B7"
    secondary_seed: "B8"
  data_start_rows:
    audit: 12        # Data appends starting at row 12
    other: 2         # DNS/errors start at row 2

# Override YAML defaults for this session
$env:CDP_LIMIT = "20"                   # Use 20 concurrent workers
$env:CDP_TIMEOUT = "15"                 # 15-second timeouts
$env:CDP_JUMP_SERVER = "bastion.corp.local"  # Use this jump server
$env:CDP_PRIMARY_CRED_TARGET = "MyApp/NetworkAdmin"
$env:CDP_ANSWER_CRED_TARGET = "MyApp/LocalAdmin"

from cdp_audit.app_config import config

# Access configuration values
jump_host = config.JUMP_HOST           # "192.0.2.10" or env override
timeout = config.DEFAULT_TIMEOUT        # 10 or env override
cdp_template = config.CDP_TEMPLATE      # Path object, validated

# Config loader has already validated these exist
print(f"Using jump host: {jump_host}")
print(f"Timeout: {timeout} seconds")

python -m cdp_audit

run.bat

python -m cdp_audit.cli

class CredentialManager:
    """Handles credential collection with OS integration and fallback."""

    def __init__(self):
        """Initialize credential manager with config values."""
        from cdp_audit.app_config import config

        # Read from environment variables (override) or config.yaml (default)
        self.primary_target = os.getenv("CDP_PRIMARY_CRED_TARGET", config.CRED_TARGET)
        self.fallback_target = os.getenv("CDP_ANSWER_CRED_TARGET", config.ALT_CREDS)
        self.fallback_username = os.getenv("CDP_FALLBACK_USERNAME", config.CDP_FALLBACK_USERNAME)

        self.logger = logging.getLogger(__name__)

def _read_win_cred(self, target_name: str) -> Tuple[str, str]:
    """Read credentials from Windows Credential Manager."""
    try:
        import win32cred
    except ImportError:
        self.logger.debug("win32cred not available (non-Windows platform)")
        return None, None

    try:
        cred = win32cred.CredRead(target_name, win32cred.CRED_TYPE_GENERIC)
        username = cred['UserName']
        password_bytes = cred['CredentialBlob']

        # Handle both bytes and string returns
        if isinstance(password_bytes, bytes):
            password = password_bytes.decode('utf-16-le')
        else:
            password = str(password_bytes)

        self.logger.debug(f"Retrieved credentials from Credential Manager: {target_name}")
        return username, password

    except Exception as e:
        self.logger.debug(f"Could not read '{target_name}' from Credential Manager: {e}")
        return None, None

def _write_win_cred(self, target: str, username: str, password: str) -> bool:
    """Write credentials to Windows Credential Manager."""
    try:
        import win32cred
    except ImportError:
        self.logger.debug("win32cred not available (non-Windows platform)")
        return False

    try:
        password_bytes = password.encode('utf-16-le')
        cred = {
            'Type': win32cred.CRED_TYPE_GENERIC,
            'TargetName': target,
            'UserName': username,
            'CredentialBlob': password_bytes,
            'Persist': win32cred.CRED_PERSIST_LOCAL_MACHINE,
            'Comment': 'Stored by CDP Network Audit'
        }
        win32cred.CredWrite(cred, 0)
        self.logger.info(f"Stored credentials in Credential Manager: {target}")
        return True

    except Exception as e:
        self.logger.debug(f"Could not save credentials to Credential Manager: {e}")
        return False

def get_secret_with_fallback(
    self,
    prompt_user: str,
    prompt_pass: str,
    target_name: str,
    allow_custom_username: bool = True
) -> Tuple[str, str]:
    """
    Retrieve credentials with Credential Manager fallback.

    1. Try reading from Windows Credential Manager
    2. Fall back to interactive prompt
    3. Optionally save to Credential Manager

    Args:
        prompt_user: Username prompt text
        prompt_pass: Password prompt text
        target_name: Credential Manager target name
        allow_custom_username: Whether to allow username customization

    Returns:
        (username, password) tuple
    """
    # Try Credential Manager first
    username, password = self._read_win_cred(target_name)

    if username and password:
        self.logger.info(f"Using stored credentials from Credential Manager: {target_name}")
        return username, password

    # Fall back to interactive prompt
    self.logger.info("Credentials not found, prompting user...")

    if allow_custom_username:
        username = input(prompt_user).strip()
        if not username:
            raise ValueError("Username cannot be empty")
    else:
        # Use pre-configured fallback username
        username = self.fallback_username
        print(f"Using fallback username from config: {username}")

    password = getpass.getpass(prompt_pass).strip()
    if not password:
        raise ValueError("Password cannot be empty")

    # Ask to save credentials
    save = input(f"Store credentials in Credential Manager as '{target_name}'? [y/N]: ").strip().lower()
    if save == 'y':
        success = self._write_win_cred(target_name, username, password)
        if success:
            print(f"✓ Credentials saved to Credential Manager: {target_name}")
        else:
            print("⚠ Could not save credentials (non-Windows platform or error)")

    return username, password

def collect_all_credentials(self) -> dict:
    """
    Collect both primary and fallback credentials.

    Returns:
        dict with keys: primary_user, primary_pass, fallback_user, fallback_pass
    """
    print("=== Credential Collection ===\n")

    # Collect primary credentials
    print("Collecting primary credentials (used for jump and devices)...")
    primary_user, primary_pass = self.get_secret_with_fallback(
        prompt_user="Enter primary username: ",
        prompt_pass="Enter primary password: ",
        target_name=self.primary_target,
        allow_custom_username=True
    )

    # Collect fallback credentials
    print(f"\nCollecting fallback credentials (username: {self.fallback_username})...")
    fallback_user = self.fallback_username
    _, fallback_pass = self.get_secret_with_fallback(
        prompt_user=f"Enter fallback username (default: {self.fallback_username}): ",
        prompt_pass=f"Enter '{self.fallback_username}' password: ",
        target_name=self.fallback_target,
        allow_custom_username=False
    )

    return {
        'primary_user': primary_user,
        'primary_pass': primary_pass,
        'fallback_user': fallback_user,
        'fallback_pass': fallback_pass
    }

"""Pre-flight validation for templates and configuration."""

import sys
import logging
from pathlib import Path
from openpyxl import load_workbook
from cdp_audit.app_config import config


def validate_textfsm_templates() -> bool:
    """
    Validate TextFSM templates exist and are readable.

    Returns:
        True if all templates valid, False otherwise
    """
    logger = logging.getLogger(__name__)

    templates = {
        'CDP Template': config.CDP_TEMPLATE,
        'Version Template': config.VER_TEMPLATE
    }

    all_valid = True
    for name, path in templates.items():
        if not path.exists():
            logger.error(f"❌ {name} not found: {path}")
            all_valid = False
        elif not path.is_file():
            logger.error(f"❌ {name} is not a file: {path}")
            all_valid = False
        else:
            logger.info(f"✓ {name} found: {path}")

    return all_valid


def validate_excel_template() -> bool:
    """
    Validate Excel template exists and is loadable.

    Returns:
        True if template valid, False otherwise
    """
    logger = logging.getLogger(__name__)
    template_path = config.EXCEL_TEMPLATE

    if not template_path.exists():
        logger.error(f"❌ Excel template not found: {template_path}")
        return False

    if not template_path.is_file():
        logger.error(f"❌ Excel template is not a file: {template_path}")
        return False

    # Try loading the template
    try:
        wb = load_workbook(template_path) wb.close()
        logger.info(f"✓ Excel template valid: {template_path}")
        return True
    except Exception as e:
        logger.error(f"❌ Excel template is corrupt or invalid: {e}")
        return False


def validate_all() -> bool:
    """
    Run all validation checks.

    Returns:
        True if all validations pass, False otherwise
    """
    logger = logging.getLogger(__name__)
    logger.info("=== Running Pre-Flight Validation ===\n")

    textfsm_valid = validate_textfsm_templates()
    excel_valid = validate_excel_template()

    if textfsm_valid and excel_valid:
        logger.info("\n✓ All validation checks passed")
        return True
    else:
        logger.error("\n❌ Validation failed - please fix the above errors")
        return False

from cdp_audit import validators

if not validators.validate_all():
    sys.exit(1)

class NetworkDiscoverer:
    """Multi-threaded network discovery engine using CDP."""

    def __init__(self, credentials: dict, jump_host: str = ""):
        """
        Initialize discovery engine.

        Args:
            credentials: Dict with primary_user, primary_pass, fallback_user, fallback_pass
            jump_host: Optional jump/bastion host IP or hostname
        """
        from cdp_audit.app_config import config

        # Configuration
        self.config = config
        self.credentials = credentials
        self.jump_host = jump_host

        # Thread-safe data structures
        self.cdp_neighbor_details = []   # Parsed CDP entries (list, protected by data_lock)
        self.hostnames = set()            # Discovered hostnames for DNS (set, protected by data_lock)
        self.visited = set()              # IPs we've already audited (set, protected by visited_lock)
        self.authentication_errors = set()  # Auth failures (set, protected by data_lock)
        self.connection_errors = {}       # {IP: error_message} (dict, protected by data_lock)
        self.dns_ip = {}                  # {hostname: resolved_ip} (dict, protected by data_lock)

        # Thread synchronization
        self.visited_lock = threading.Lock()  # Protects visited set and queue operations
        self.data_lock = threading.Lock()     # Protects result accumulators

        # Discovery queue
        self.queue = queue.Queue()

        # Logging
        self.logger = logging.getLogger(__name__)

def _paramiko_jump_client(self, host: str, username: str, password: str) -> paramiko.SSHClient:
    """
    Create a Paramiko SSH client for jump host connection.

    Args:
        host: Jump host IP or hostname
        username: SSH username
        password: SSH password

    Returns:
        Connected paramiko.SSHClient

    Raises:
        paramiko.AuthenticationException: If authentication fails
        paramiko.SSHException: If SSH connection fails
    """
    client = paramiko.SSHClient()
    client.set_missing_host_key_policy(paramiko.WarningPolicy())

    try:
        client.connect(
            hostname=host,
            username=username,
            password=password,
            port=self.config.SSH_PORT,
            timeout=self.config.DEFAULT_TIMEOUT,
            look_for_keys=False,
            allow_agent=False,
            auth_timeout=self.config.DEFAULT_TIMEOUT
        )
        self.logger.debug(f"Connected to jump host: {host}")
        return client

    except paramiko.AuthenticationException:
        self.logger.error(f"Authentication failed on jump host: {host}")
        raise
    except Exception as e:
        self.logger.error(f"Failed to connect to jump host {host}: {e}")
        raise

def _netmiko_via_jump(
    self,
    device_ip: str,
    primary: bool = True
) -> ConnectHandler:
    """
    Create a Netmiko connection, optionally via jump host.

    Args:
        device_ip: Target device IP
        primary: If True, use primary credentials; if False, use fallback

    Returns:
        Connected Netmiko ConnectHandler

    Raises:
        NetmikoAuthenticationException: If authentication fails
        NetmikoTimeoutException: If connection times out
    """
    # Determine credentials to use
    if primary:
        jump_user = self.credentials['primary_user']
        jump_pass = self.credentials['primary_pass']
        device_user = self.credentials['primary_user']
        device_pass = self.credentials['primary_pass']
    else:
        jump_user = self.credentials['primary_user']  # Jump always uses primary
        jump_pass = self.credentials['primary_pass']
        device_user = self.credentials['fallback_user']  # Device uses fallback
        device_pass = self.credentials['fallback_pass']

    # Build Netmiko device dict
    device_params = {
        'device_type': self.config.DEVICE_TYPE,
        'username': device_user,
        'password': device_pass,
        'timeout': self.config.DEFAULT_TIMEOUT,
        'auth_timeout': self.config.DEFAULT_TIMEOUT,
        'conn_timeout': self.config.DEFAULT_TIMEOUT
    }

    # Direct connection (no jump host)
    if not self.jump_host:
        device_params['host'] = device_ip
        self.logger.debug(f"Connecting directly to {device_ip}")
        return ConnectHandler(**device_params)

    # Jump-mediated connection
    self.logger.debug(f"Connecting to {device_ip} via jump host {self.jump_host}")

    # 1. Connect to jump host with Paramiko
    jump_client = self._paramiko_jump_client(self.jump_host, jump_user, jump_pass)

    # 2. Create direct-tcpip channel through jump to target
    channel = jump_client.get_transport().open_channel(
        kind='direct-tcpip',
        dest_addr=(device_ip, self.config.SSH_PORT),
        src_addr=('', 0)
    )

    # 3. Wrap channel as socket for Netmiko
    class ChannelToSocket:
        """Wrapper to make Paramiko channel look like a socket."""
        def __init__(self, channel):
            self.channel = channel
        def send(self, data):
            return self.channel.send(data)
        def recv(self, size):
            return self.channel.recv(size)
        def close(self):
            return self.channel.close()
        def settimeout(self, timeout):
            return self.channel.settimeout(timeout)

    sock = ChannelToSocket(channel)

    # 4. Pass socket to Netmiko
    device_params['sock'] = sock
    conn = ConnectHandler(**device_params)

    # Store jump client for cleanup
    conn._jump_client = jump_client

    return conn

if primary:
    jump_user, jump_pass = primary_user, primary_pass
    device_user, device_pass = primary_user, primary_pass
else:
    jump_user, jump_pass = primary_user, primary_pass  # Jump always uses primary
    device_user, device_pass = fallback_user, fallback_pass  # Device uses fallback

def run_device_commands(self, device_ip: str) -> Tuple[str, str]:
    """
    Execute show commands on device with fallback credential strategy.

    Strategy:
    1. Try with primary credentials
    2. On auth failure, catch and retry with fallback
    3. Don't retry auth failures (credentials won't change)
    4. Do retry transient errors (timeouts, SSH glitches) up to 3 times
    5. Always disconnect in finally block

    Args:
        device_ip: Target device IP

    Returns:
        (show_version_output, show_cdp_neighbors_detail_output) tuple

    Raises:
        Exception: If all retry attempts fail
    """
    max_retries = self.config.MAX_RETRY_ATTEMPTS
    conn = None

    for attempt in range(1, max_retries + 1):
        try:
            # Try primary credentials first
            try:
                conn = self._netmiko_via_jump(device_ip, primary=True)
                self.logger.debug(f"Connected to {device_ip} with primary credentials")
            except NetmikoAuthenticationException:
                # Fall back to secondary credentials
                self.logger.warning(f"Primary auth failed on {device_ip}, trying fallback...")
                conn = self._netmiko_via_jump(device_ip, primary=False)
                self.logger.info(f"Connected to {device_ip} with fallback credentials")

            # Execute commands
            ver_output = conn.send_command("show version", read_timeout=self.config.DEFAULT_TIMEOUT)
            cdp_output = conn.send_command(
                "show cdp neighbors detail",
                read_timeout=self.config.DEFAULT_TIMEOUT
            )

            self.logger.info(f"✓ Successfully collected data from {device_ip}")
            return ver_output, cdp_output

        except NetmikoAuthenticationException as e:
            # Don't retry auth failures
            self.logger.error(f"Authentication failed on {device_ip} (both credentials)")
            with self.data_lock:
                self.authentication_errors.add(device_ip)
            raise

        except (NetmikoTimeoutException, SSHException, socket.timeout) as e:
            # Retry transient errors
            if attempt < max_retries:
                self.logger.warning(f"Attempt {attempt}/{max_retries} failed for {device_ip}: {e}")
                time.sleep(2)  # Brief delay before retry
            else:
                self.logger.error(f"All {max_retries} attempts failed for {device_ip}")
                with self.data_lock:
                    self.connection_errors[device_ip] = str(e)
                raise

        finally:
            # Always clean up connection
            if conn:
                try:
                    conn.disconnect()
                    if hasattr(conn, '_jump_client'):
                        conn._jump_client.close()
                except Exception as e:
                    self.logger.debug(f"Error disconnecting from {device_ip}: {e}")

def parse_outputs_and_enqueue_neighbors(
    self,
    device_ip: str,
    ver_output: str,
    cdp_output: str
) -> None:
    """
    Parse command outputs and enqueue neighbor devices for discovery.

    Three-step process:
    1. Parse version output for device context (hostname, serial, uptime)
    2. Parse CDP neighbors and extract details
    3. Apply queueing heuristic to determine which neighbors to crawl

    Queueing Heuristic:
    - Only enqueue if ALL three conditions are true:
      a) "Switch" in capabilities (infrastructure device)
      b) "Host" NOT in capabilities (not an endpoint)
      c) Has management IP (addressable)

    Args:
        device_ip: Source device IP
        ver_output: 'show version' output
        cdp_output: 'show cdp neighbors detail' output
    """
    # Step 1: Parse Version Output for Device Context
    try:
        ver_parsed = textfsm_parser(
            raw_text=ver_output,
            template_file=str(self.config.VER_TEMPLATE)
        )
        if ver_parsed and len(ver_parsed) > 0:
            local_hostname = ver_parsed[0].get('hostname', device_ip)
            local_serial = ver_parsed[0].get('serial', ['N/A'])[0] if ver_parsed[0].get('serial') else 'N/A'
            local_uptime = ver_parsed[0].get('uptime', 'N/A')
        else:
            local_hostname = device_ip
            local_serial = 'N/A'
            local_uptime = 'N/A'
    except Exception as e:
        self.logger.warning(f"Failed to parse 'show version' for {device_ip}: {e}")
        local_hostname = device_ip
        local_serial = 'N/A'
        local_uptime = 'N/A'

    # Step 2: Parse CDP Neighbors
    try:
        cdp_parsed = textfsm_parser(
            raw_text=cdp_output,
            template_file=str(self.config.CDP_TEMPLATE)
        )
    except Exception as e:
        self.logger.error(f"Failed to parse CDP output for {device_ip}: {e}")
        return

    if not cdp_parsed:
        self.logger.info(f"No CDP neighbors found for {device_ip}")
        return

    # Step 3: Process Each CDP Neighbor
    with self.data_lock:
        for entry in cdp_parsed:
            # Extract CDP fields
            dest_host = entry.get('destination_host', 'N/A')
            local_port = entry.get('local_port', 'N/A')
            remote_port = entry.get('remote_port', 'N/A')
            mgmt_ip = entry.get('management_ip', '')
            platform = entry.get('platform', 'N/A')
            capabilities = entry.get('capabilities', '')

            # Store CDP entry
            self.cdp_neighbor_details.append({
                'LOCAL_HOST': local_hostname,
                'LOCAL_IP': device_ip,
                'LOCAL_PORT': local_port,
                'LOCAL_SERIAL': local_serial,
                'LOCAL_UPTIME': local_uptime,
                'DESTINATION_HOST': dest_host,
                'REMOTE_PORT': remote_port,
                'MANAGEMENT_IP': mgmt_ip,
                'PLATFORM': platform,
                'CAPABILITIES': capabilities
            })

            # Store hostname for DNS resolution
            if dest_host and dest_host != 'N/A':
                self.hostnames.add(dest_host)

            # Apply Queueing Heuristic
            # Only enqueue if: Switch device + not a Host + has management IP
            if "Switch" in capabilities and "Host" not in capabilities and mgmt_ip:
                # Check if already visited or queued
                with self.visited_lock:
                    if mgmt_ip not in self.visited and dest_host not in self.visited:
                        self.queue.put(mgmt_ip)
                        self.visited.add(mgmt_ip)
                        self.visited.add(dest_host)
                        self.logger.info(f"Queued for discovery: {dest_host} ({mgmt_ip})")

if "Switch" in caps and "Host" not in caps and mgmt_ip:

Router (Switch, Router) + 192.0.2.5  → Queue it ✓
IP Phone (Host) + 192.0.2.50         → Skip (endpoint) ✗
Access Point (Host) + no Mgmt IP     → Skip (non-addressable) ✗
Switch (Switch) + 192.0.2.100        → Queue it ✓

def discover_worker(self) -> None:
    """
    Worker thread function for parallel device discovery.

    Loop:
    1. Get next host from queue (timeout=1.0 prevents hangs)
    2. Recognize sentinel (None = shutdown signal)
    3. Check if already visited (prevent duplicate work)
    4. Execute discovery with up to 3 retries
    5. Parse outputs and enqueue new neighbors
    6. Always call task_done() or queue.join() will hang
    """
    while True:
        try:
            # Get next device from queue
            device_ip = self.queue.get(timeout=1.0)

            # Check for shutdown signal
            if device_ip is None:
                self.logger.debug("Worker received shutdown signal")
                self.queue.task_done()
                return

            # Double-check not already processed (race condition protection)
            with self.visited_lock:
                if device_ip in self.visited:
                    self.logger.debug(f"Skipping {device_ip} (already visited)")
                    self.queue.task_done()
                    continue
                self.visited.add(device_ip)

            # Execute discovery
            self.logger.info(f"Discovering: {device_ip}")
            try:
                ver_output, cdp_output = self.run_device_commands(device_ip)
                self.parse_outputs_and_enqueue_neighbors(device_ip, ver_output, cdp_output)
            except Exception as e:
                self.logger.error(f"Failed to discover {device_ip}: {e}")

            # Mark task as complete
            self.queue.task_done()

        except queue.Empty:
            # Queue is empty, poll again
            continue
        except Exception as e:
            self.logger.error(f"Worker thread error: {e}")
            continue

def discover_from_seeds(self, seeds: List[str]) -> None:
    """
    Start network discovery from seed devices.

    Args:
        seeds: List of seed device IPs
    """
    self.logger.info(f"Starting discovery with {len(seeds)} seed device(s)")

    # Add seeds to queue
    for seed in seeds:
        self.queue.put(seed)
        with self.visited_lock:
            self.visited.add(seed)

    # Determine worker count
    worker_count = self.config.DEFAULT_LIMIT

    # Start worker threads
    self.logger.info(f"Starting {worker_count} worker threads")
    workers = []
    for i in range(worker_count):
        t = threading.Thread(target=self.discover_worker, name=f"Worker-{i+1}")
        t.start()
        workers.append(t)

    # Wait for queue to be empty
    self.queue.join()
    self.logger.info("Discovery queue empty, shutting down workers...")

    # Send shutdown signals
    for _ in range(worker_count):
        self.queue.put(None)  # Sentinel value

    # Wait for all workers to finish
    for t in workers:
        t.join()

    self.logger.info("All workers shut down")

def resolve_dns_parallel(self) -> None:
    """
    Resolve all discovered hostnames to IPs in parallel.

    Design:
    - ThreadPoolExecutor with 4-32 workers (based on CDP_LIMIT)
    - Submit all resolutions concurrently
    - Collect results as they complete
    - Best-effort - failures are logged but don't block
    """
    if not self.hostnames:
        self.logger.info("No hostnames to resolve")
        return

    self.logger.info(f"Resolving {len(self.hostnames)} hostnames...")

    # Determine DNS worker count (min 4, max 32, based on CDP_LIMIT)
    dns_workers = max(
        self.config.DNS_MIN_WORKERS,
        min(self.config.DNS_MAX_WORKERS, self.config.DEFAULT_LIMIT * 2)
    )

    def resolve_one(hostname: str) -> Tuple[str, str]:
        """Resolve a single hostname."""
        try:
            ip = socket.gethostbyname(hostname)
            self.logger.debug(f"Resolved {hostname} → {ip}")
            return hostname, ip
        except Exception as e:
            self.logger.debug(f"Failed to resolve {hostname}: {e}")
            return hostname, "N/A"

    # Parallel DNS resolution
    with ThreadPoolExecutor(max_workers=dns_workers) as executor:
        futures = {executor.submit(resolve_one, hostname): hostname for hostname in self.hostnames}

        for future in as_completed(futures):
            try:
                hostname, ip = future.result()
                with self.data_lock:
                    self.dns_ip[hostname] = ip
            except Exception as e:
                self.logger.warning(f"DNS resolution error: {e}")

    self.logger.info(f"DNS resolution complete ({len(self.dns_ip)} resolved)")

"""Professional Excel report generation with template preservation."""

import shutil
import logging
from pathlib import Path
from datetime import datetime
from typing import Dict, List
import pandas as pd
from openpyxl import load_workbook
from cdp_audit.app_config import config


class ExcelReporter:
    """Generate professional Excel reports from templates."""

    def __init__(self, site_name: str, seeds: List[str]):
        """
        Initialize Excel reporter.

        Args:
            site_name: Site name for filename and metadata
            seeds: List of seed devices for metadata
        """
        self.site_name = site_name
        self.seeds = seeds
        self.config = config
        self.logger = logging.getLogger(__name__)

        # Generate output filename
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        self.output_filename = f"{site_name}_CDP_Network_Audit_{timestamp}.xlsx"

def _copy_template(self) -> None:
    """Copy Excel template to output filename."""
    template_path = self.config.EXCEL_TEMPLATE

    if not template_path.exists():
        raise FileNotFoundError(f"Excel template not found: {template_path}")

    shutil.copy2(template_path, self.output_filename)
    self.logger.info(f"Copied template to: {self.output_filename}")

def _stamp_metadata(self, wb: load_workbook) -> None:
    """
    Stamp metadata into specific cells.

    Args:
        wb: Loaded openpyxl workbook
    """
    sheet_name = self.config.EXCEL_SHEETS['audit']
    ws = wb[sheet_name]

    # Metadata to stamp
    now = datetime.now()
    metadata = {
        self.config.EXCEL_METADATA_CELLS['site_name']: self.site_name,
        self.config.EXCEL_METADATA_CELLS['date']: now.strftime("%Y-%m-%d"),
        self.config.EXCEL_METADATA_CELLS['time']: now.strftime("%H:%M:%S"),
        self.config.EXCEL_METADATA_CELLS['primary_seed']: self.seeds[0] if self.seeds else "N/A",
        self.config.EXCEL_METADATA_CELLS['secondary_seed']: self.seeds[1] if len(self.seeds) > 1 else "N/A"
    }

    # Stamp cells
    for cell, value in metadata.items():
        ws[cell] = value
        self.logger.debug(f"Stamped {cell} = {value}")

def generate_report(
    self,
    cdp_data: List[Dict],
    dns_data: Dict[str, str],
    auth_errors: set,
    conn_errors: Dict[str, str]
) -> str:
    """
    Generate Excel report with all data.

    Args:
        cdp_data: List of CDP neighbor dicts
        dns_data: Dict of hostname -> IP mappings
        auth_errors: Set of IPs with auth failures
        conn_errors: Dict of IP -> error message

    Returns:
        Path to generated Excel file
    """
    self.logger.info("Generating Excel report...")

    # Step 1: Copy template
    self._copy_template()

    # Step 2: Load workbook
    wb = load_workbook(self.output_filename)

    # Step 3: Stamp metadata
    self._stamp_metadata(wb)

    # Step 4: Write data to sheets
    with pd.ExcelWriter(
        self.output_filename,
        engine='openpyxl',
        mode='a',
        if_sheet_exists='overlay'
    ) as writer:
        writer.book = wb

        # CDP Audit Data
        if cdp_data:
            df_cdp = pd.DataFrame(cdp_data)
            df_cdp.to_excel(
                writer,
                sheet_name=self.config.EXCEL_SHEETS['audit'],
                startrow=self.config.EXCEL_DATA_START_ROWS['audit'] - 1,
                index=False,
                header=False
            )
            self.logger.info(f"Wrote {len(cdp_data)} CDP entries")

        # DNS Resolved Data
        if dns_data:
            df_dns = pd.DataFrame([
                {'Hostname': k, 'IP Address': v}
                for k, v in dns_data.items()
            ])
            df_dns.to_excel(
                writer,
                sheet_name=self.config.EXCEL_SHEETS['dns'],
                startrow=self.config.EXCEL_DATA_START_ROWS['other'] - 1,
                index=False
            )
            self.logger.info(f"Wrote {len(dns_data)} DNS entries")

        # Authentication Errors
        if auth_errors:
            df_auth = pd.DataFrame(
                list(auth_errors),
                columns=['Authentication Errors']
            )
            df_auth.to_excel(
                writer,
                sheet_name=self.config.EXCEL_SHEETS['auth_errors'],
                startrow=self.config.EXCEL_DATA_START_ROWS['other'] - 1,
                index=False
            )
            self.logger.info(f"Wrote {len(auth_errors)} auth errors")

        # Connection Errors
        if conn_errors:
            df_conn = pd.DataFrame([
                {'IP Address': k, 'Error': v}
                for k, v in conn_errors.items()
            ])
            df_conn.to_excel(
                writer,
                sheet_name=self.config.EXCEL_SHEETS['conn_errors'],
                startrow=self.config.EXCEL_DATA_START_ROWS['other'] - 1,
                index=False
            )
            self.logger.info(f"Wrote {len(conn_errors)} connection errors")

    self.logger.info(f"✓ Report generated: {self.output_filename}")
    return self.output_filename