# coding=utf-8
from netmiko import ConnectHandler
from openpyxl import load_workbook
import os
from sys import exit
from netmiko import exceptions
import re
# 讀取excel內設備列表信息
def check_and_get_dev_list(filename, sheet_name):
    excel_information = []
    sheet_header = []
    wb = load_workbook(filename)
    sh = wb[sheet_name]
    # 獲取最大行數
    row = sh.max_row
    # 獲取最大列數
    column = sh.max_column
    data = []
    # 獲取表頭寫入列表中方便調用
    for data_1 in range(1, column+1):
        get_sheet_header = sh.cell(row=1, column=data_1).value
        sheet_header.append(get_sheet_header)
    # 第一行為表頭， 此處 row +1 是pyton循環(huán)時不讀取最后一個數
    for row_1 in range(2, row + 1):
        # 存儲一行信息
        sheet_data_1 = dict()
        # 逐行讀取表中的數據
        for b in range(1, column + 1):
            cell = sh.cell(row=row_1, column=b).value
            # 將數據已字典形式寫入 sheet_data_1 中
            # if cell != None:
            sheet_data_1[sheet_header[b-1]] = cell
        excel_information.append(sheet_data_1)
    for i in excel_information:
        if i['ip'] != None:
            data.append(i)
    return data
 
#獲取excel數據并整合成dev字典
def get_dev():
    res = check_and_get_dev_list('./resource.xlsx', 'Sheet1')
    devices = []
    for i in res:
        if i['protocol'] == 'telnet':
            i['type'] = i['type']+'_telnet'
        dev = {'device_type':i['type'],
               'host': i['ip'],
               'username': i['username'],
               'password': i['password'],
               'secret': i['enpassword'],
               'port': i['port'],}
        devices.append(dev)
    return devices
 
# 配置批量備份導出
def devices_confbak(devices=''):
    # 創(chuàng)建備份文件夾
    try:
        path = './conf_bak'
        os.makedirs(path)
    except FileExistsError:
        pass
    # 存儲連接失敗的IP
    failed_ips = []
    # 循環(huán)登錄設備獲取配置
    for dev in devices:
        try:
            with ConnectHandler(**dev) as conn:
                print('\n----------成功登錄到：' + dev['host'] + '----------')
                conn.enable()
                if 'cisco_ios' in dev['device_type']:
                    output = conn.send_command(command_string='show run')
                elif 'huawei' or 'hp_comware' in dev['device_type']:
                    output = conn.send_command(command_string='dis current-configuration')
                else:
                    print('error')
                with open('./conf_bak/'+ dev['host'] +'_conf_bak.txt', mode='w', encoding='utf8') as f:
                    print('正在備份:'+dev['host'])
                    # 文件讀寫異常處理
                    try:
                        f.write(output)
                    except PermissionError:
                        print('*****-無寫入權限，請將文件夾賦予讀寫權限-*****')
                        continue
                    else:
                        print('備份成功！')
        # 連接異常處理
        except exceptions.NetmikoAuthenticationException:
            print('\n**********'+dev['host']+'：登錄驗證失?。?*********')
            failed_ips.append(dev['host'])
            continue
        except exceptions.NetmikoTimeoutException:
            print('\n**********'+dev['host']+'：目標不可達！**********')
            failed_ips.append(dev['host'])
            continue
        except exceptions.ReadTimeout:
            print('\n**********'+dev['host']+'：讀取超時，請檢查enable密碼是否正確！**********')
            failed_ips.append(dev['host'])
            continue
    if len(failed_ips) > 0:
        print('\n以下設備連接失敗,請檢查：')
        for x in failed_ips:
            print(x)
    return 1
 
# 配置巡檢
def devices_autocheck(devices='', cmd=''):
    # 存儲命令執(zhí)行回顯
    results = []
    try:
        for x in range(len(devices)):
            # 循環(huán)登錄設備
            with ConnectHandler(**devices[x]) as conn:
                conn.enable()
                print('正在巡檢：'+devices[x]['host']+' ...')
                result = [devices[x]['host'],devices[x]['device_type']]
                for i in range(len(cmd)):
                    # 循環(huán)執(zhí)行命令，根據不同設備執(zhí)行不同命令
                    if 'cisco_ios' in devices[x]['device_type']:
                        output = conn.send_command(command_string=str(cmd[i]['cisco']))
                    elif 'huawei' or 'hp_comware' in devices[x]['device_type']:
                        conn.send_command(command_string='sys',expect_string=']')
                        output = conn.send_command(command_string=str(cmd[i]['huawei']))
                    result.append(output)
                results.append(result)
 
    except exceptions.NetmikoAuthenticationException:
        print('\n**********'+devices[x]['host']+'：登錄驗證失敗！**********')
    except exceptions.NetmikoTimeoutException:
        print('\n**********' + devices[x]['host'] + '：目標不可達！**********')
    except exceptions.ReadTimeout:
        print('\n**********' + devices[x]['host'] + '：讀取超時，請檢查enable密碼是否正確！**********')
 
    return results
# 計算內存使用率
def get_mem(memstr,devtype=''):
    if 'cisco' in devtype:
        total_match = re.search(r'Processor Pool Total:\s+(\d+)', memstr)
        used_match = re.search(r'Used:\s+(\d+)', memstr)
        # 提取總數和已用數，并將其轉換為整數
        total = int(total_match.group(1))
        used = int(used_match.group(1))
        # 計算使用百分比
        percentage = used / total * 100
        return f"{percentage:.0f}%"
    elif 'huawei' in devtype:
        match = re.search(r"Memory Using Percentage Is:\s*(\d+)%", memstr)
        if match:
            memory_percentage = match.group(1)
            return memory_percentage+'%'
        else:
            return "No match found."
# 獲取CPU利用率
def get_cpu(cpustr,devtype=''):
    if 'cisco' in devtype:
        pattern = r"CPU utilization for five seconds: (\d+)%"
        match = re.search(pattern, cpustr)
        if match:
            cpu_utilization = match.group(1)
            return cpu_utilization+'%'
        else:
            return "No match found."
    elif 'huawei' in devtype:
        match = re.search(r"\b(\d+(\.\d+)?)%.*?\bMax", cpustr)
        if match:
            cpu_utilization = match.group(1)
            return cpu_utilization+'%'
        else:
            return "No match found."
# 運行主程序
if __name__ == '__main__':
    while True:
        print("\n##############################################\n")
        print("1：批量備份交換機配置")
        print("2：批量巡檢交換機設備")
        print("0：退出")
        option = str(input("請輸入需要的操作編號："))
        if option == '1':
            dev = get_dev()
            devices_confbak(devices=dev)
            continue
        elif option == '2':
            # 定義巡檢命令
            # cmds[x]['cisco']
            # cmds[x]['huawei']
            cmds = [
                {'cisco':'show clock','huawei':'display clock'},                        #檢查時鐘
                {'cisco':'show env power','huawei':'display power'},                    #檢查電源
                {'cisco':'show env fan','huawei':'display fan'},                        #檢查風扇
                {'cisco':'show env temperature status', 'huawei': 'display environment'},#檢查溫度
                {'cisco':'show processes cpu', 'huawei': 'display cpu-usage'},          #檢查CPU利用率
                {'cisco':'show processes memory', 'huawei': 'display memory-usage'},    #檢查內存利用率
            ]
            dev = get_dev()
            checkres = devices_autocheck(dev,cmds)
            for res in checkres:
                # print(res)
                print('\n+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++')
                print(res[0]+'-巡檢結果：')
                print('\n時鐘：\n'+res[2])
                print('電源：\n'+res[3])
                print('風扇：\n'+res[4])
                if 'Unrecognized command' in res[5]:
                    print('溫度：\n該設備不支持獲取此數據！')
                else:print('溫度：\n'+res[5])
                print('CPU利用率：\n' + get_cpu(res[6],res[1]))
                print('內存利用率：\n' + get_mem(res[7],res[1]))
                print('\n+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++')
            continue
        elif option == '0':
            break
        else:
            print("請輸入正確的編號！")