Exports WAN circuit details to an Excel spreadsheet and sends to recipients via email

get-wan-circuit-info.py

#!/usr/bin/env python3

'''Parse WAN circuit details from router configurations

get-wan-circuit-info.py

Author: David Cruz (davidcruz72@gmail.com)

Python version >= 3.6

Required Python packages:
    ciscoconfparse
    openpyxl

Features:
    Exports WAN circuit details to an Excel spreadsheet and sends to recipients via email
'''

import mimetypes
import operator
import os
import re
import signal
import smtplib
import sys
import time

from ciscoconfparse import CiscoConfParse
from email.message import EmailMessage
from email.policy import SMTP
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

configs_path = r'C:\Program Files (x86)\NetBrain\Workstation Enterprise Edition\userData\Baseline\42000000000005\ConfigFile'
re_fname = re.compile(r'(.*rt)', re.IGNORECASE)
re_description = re.compile(r'.*(cir|ckid|broadband|\d{4,})')
report_fname = f'WAN Circuits-{time.strftime("%Y.%m.%d")}.xlsx'

smtp_server = 'mailhost.domain.com'
sender = 'user@domain.com'
recipients = ['recipient@domain.com']

def main():
    # Ctrl+C graceful exit
    signal.signal(signal.SIGINT, sigint_handler)

    wb = Workbook()
    ws = wb.active
    ws.title = 'WAN Circuits'

    # Write header
    ws.append(['Router', 'Store Number', 'Interface', 'Description', 'IP Address', 'Netmask', 'Interface State']) # pylint: disable=no-member

    t1_start = time.process_time()
    circuits, parsed_file_count, total_file_count = parse_configs()
    t1_stop = time.process_time()

    # Sort results by store number and write rows
    for c in sorted(circuits, key=operator.itemgetter(0)):
        ws.append(c) # pylint: disable=no-member
    
    # Bold header row
    for cell in ws['1:1']: # pylint: disable=unsubscriptable-object
        cell.font = Font(bold=True)

    # Freeze panes
    ws.freeze_panes = ws['A2'] # pylint: disable=unsubscriptable-object

    # Enable worksheet filter
    ws.auto_filter.ref = f'A1:{get_column_letter(ws.max_column)}{str(ws.max_row)}' # pylint: disable=no-member

    # Set sort by store number
    ws.auto_filter.add_sort_condition(f'A2:A{str(ws.max_row)}') # pylint: disable=no-member

    # Resize columns
    for col in ws.iter_cols(): # pylint: disable=no-member
        max_length = 0
        column_letter = col[0].column_letter

        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                continue

        adjusted_width = max_length + 4
        ws.column_dimensions[column_letter].width = adjusted_width # pylint: disable=no-member

    wb.active = 0
    wb.save(report_fname)

    stats = f'\nParsed {parsed_file_count :,d} out of {total_file_count :,d} configurations in {t1_stop-t1_start :.3f} seconds'
    send_report(stats)

    os.remove(report_fname)

    print(stats)

def sigint_handler(signum, frame):
    sys.exit(1)

def parse_configs():
    # Process each config file
    total_file_count = 0
    parsed_file_count = 0
    results = []
    p = Path(configs_path)
    re_store_num = re.compile(r'.*?(\d{4})')
    for config in p.glob(f'*.config'):
        total_file_count += 1
        # Skip non Cisco router configurations
        if not re_fname.match(config.name):
            continue

        try:
            parser = CiscoConfParse(str(config), factory=True)
            parsed_file_count += 1
        except:
            continue

        # Extract store number from filename
        try:
            store_num = re_store_num.match(config.name).group(1)
        except:
            store_num = ''
            # If the store number is not part of the file name, try parsing it from the login banner
            login_banner = parser.find_children(r'banner login')
            for line in login_banner:
                if 'Store #' in line:
                    try:
                        store_num = f'{int(line.split("Store #")[1].lstrip()) :0>4d}'
                    except ValueError:
                        break
                    break
        
        # Parse hostname
        try:
            router_hostname = parser.find_objects(r'^hostname')[0].hostname.upper()
        except IndexError:
            continue

        # Look for WAN interfaces
        # for iface in parser.find_objects(r'^interface\s.*?\d+/\d+(/\d+)*(:\d+)*$'):
        for iface in parser.find_objects(r'^interface\s'):
            iface_state = "Shutdown" if iface.is_shutdown else "Up"

            # Find all WAN interfaces
            description = iface.description.strip().lower()
            if re_description.match(description):
                # An exception is thrown if ipv4_addr is accessed when DHCP is enabled
                try:
                    ipaddr = iface.ipv4_addr
                    netmask = iface.ipv4_netmask
                    
                    # Extract the IP address from the subinterface if the main interface does not have one
                    if not ipaddr:
                        for subiface in parser.find_objects(r'^interface\s' + iface.parent.name + r'\.'):
                            try:
                                ipaddr = subiface.ipv4_addr
                                if not ipaddr:
                                    continue
                                netmask = subiface.ipv4_netmask
                                break
                            except:
                                ipaddr = 'DHCP'
                                netmask = ''
                                break
                except:
                    ipaddr = 'DHCP'
                    netmask = ''
                description = description.title()

                results.append([router_hostname, store_num, iface.parent.name, description, ipaddr, netmask, iface_state])

    return results, parsed_file_count, total_file_count

def send_report(stats):
    msg = EmailMessage()
    msg['Subject'] = 'WAN Circuits Export'
    msg['To'] = ', '.join(recipients)
    msg['From'] = sender
    msg.set_content(f'The latest WAN circuits export is attached.\n{stats}')

    ctype, encoding = mimetypes.guess_type(report_fname)
    if ctype is None or encoding is not None:
        # No guess could be made, or the file is encoded (compressed), so
        # use a generic bag-of-bits type.
        ctype = 'application/octet-stream'

    maintype, subtype = ctype.split('/', 1)
    with open(report_fname, 'rb') as fp:
        msg.add_attachment(fp.read(),
                            maintype=maintype,
                            subtype=subtype,
                            filename=report_fname)
    
    # Send the message
    with smtplib.SMTP(smtp_server) as s:
        s.send_message(msg)


if __name__ == '__main__':
    main()