Api扫描RouterOS路由器的线路，并将故障线路写入xlsx表格中

1.api参考于routeros官方api文档：

API_Python3 - RouterOS - MikroTik Documentationhttps://help.mikrotik.com/docs/display/ROS/API_Python3

2.内嵌routeros路由器命令的api代码如下(api6.py)：

#!/usr/bin/python3
import sys, time, binascii, socket, select, ssl
import hashlib
import re


class ApiRos:
    "Routeros api"

    def __init__(self, sk):
        self.sk = sk
        self.currenttag = 0

    def login(self, username, pwd):
        for repl, attrs in self.talk(["/login", "=name=" + username,
                                      "=password=" + pwd]):
            if repl == '!trap':
                return False
            elif '=ret' in attrs.keys():
                # for repl, attrs in self.talk(["/login"]):
                chal = binascii.unhexlify((attrs['=ret']).encode(sys.stdout.encoding))
                md = hashlib.md5()
                md.update(b'\x00')
                md.update(pwd.encode(sys.stdout.encoding))
                md.update(chal)
                for repl2, attrs2 in self.talk(["/login", "=name=" + username,
                                                "=response=00" + binascii.hexlify(md.digest()).decode(
                                                    sys.stdout.encoding)]):
                    if repl2 == '!trap':
                        return False
        return True

    def talk(self, words):
        if self.writeSentence(words) == 0: return
        r = []
        while 1:
            i = self.readSentence();
            if len(i) == 0: continue
            reply = i[0]
            attrs = {}
            for w in i[1:]:
                j = w.find('=', 1)
                if (j == -1):
                    attrs[w] = ''
                else:
                    attrs[w[:j]] = w[j + 1:]
            r.append((reply, attrs))
            if reply == '!done': return r

    def writeSentence(self, words):
        ret = 0
        for w in words:
            self.writeWord(w)
            ret += 1
        self.writeWord('')
        return ret

    def readSentence(self):
        r = []
        while 1:
            w = self.readWord()
            if w == '': return r
            r.append(w)

    def writeWord(self, w):
        # print(("<<< " + w))
        self.writeLen(len(w))
        self.writeStr(w)

    def readWord(self):
        ret = self.readStr(self.readLen())
        # print((">>> " + ret))
        return ret

    def writeLen(self, l):
        if l < 0x80:
            self.writeByte((l).to_bytes(1, sys.byteorder))
        elif l < 0x4000:
            l |= 0x8000
            tmp = (l >> 8) & 0xFF
            self.writeByte(((l >> 8) & 0xFF).to_bytes(1, sys.byteorder))
            self.writeByte((l & 0xFF).to_bytes(1, sys.byteorder))
        elif l < 0x200000:
            l |= 0xC00000
            self.writeByte(((l >> 16) & 0xFF).to_bytes(1, sys.byteorder))
            self.writeByte(((l >> 8) & 0xFF).to_bytes(1, sys.byteorder))
            self.writeByte((l & 0xFF).to_bytes(1, sys.byteorder))
        elif l < 0x10000000:
            l |= 0xE0000000
            self.writeByte(((l >> 24) & 0xFF).to_bytes(1, sys.byteorder))
            self.writeByte(((l >> 16) & 0xFF).to_bytes(1, sys.byteorder))
            self.writeByte(((l >> 8) & 0xFF).to_bytes(1, sys.byteorder))
            self.writeByte((l & 0xFF).to_bytes(1, sys.byteorder))
        else:
            self.writeByte((0xF0).to_bytes(1, sys.byteorder))
            self.writeByte(((l >> 24) & 0xFF).to_bytes(1, sys.byteorder))
            self.writeByte(((l >> 16) & 0xFF).to_bytes(1, sys.byteorder))
            self.writeByte(((l >> 8) & 0xFF).to_bytes(1, sys.byteorder))
            self.writeByte((l & 0xFF).to_bytes(1, sys.byteorder))

    def readLen(self):
        c = ord(self.readStr(1))
        # print (">rl> %i" % c)
        if (c & 0x80) == 0x00:
            pass
        elif (c & 0xC0) == 0x80:
            c &= ~0xC0
            c <<= 8
            c += ord(self.readStr(1))
        elif (c & 0xE0) == 0xC0:
            c &= ~0xE0
            c <<= 8
            c += ord(self.readStr(1))
            c <<= 8
            c += ord(self.readStr(1))
        elif (c & 0xF0) == 0xE0:
            c &= ~0xF0
            c <<= 8
            c += ord(self.readStr(1))
            c <<= 8
            c += ord(self.readStr(1))
            c <<= 8
            c += ord(self.readStr(1))
        elif (c & 0xF8) == 0xF0:
            c = ord(self.readStr(1))
            c <<= 8
            c += ord(self.readStr(1))
            c <<= 8
            c += ord(self.readStr(1))
            c <<= 8
            c += ord(self.readStr(1))
        return c

    def writeStr(self, str):
        n = 0;
        while n < len(str):
            r = self.sk.send(bytes(str[n:], 'UTF-8'))
            if r == 0: raise RuntimeError("connection closed by remote end")
            n += r

    def writeByte(self, str):
        n = 0;
        while n < len(str):
            r = self.sk.send(str[n:])
            if r == 0: raise RuntimeError("connection closed by remote end")
            n += r

    def readStr(self, length):
        ret = ''
        # print ("length: %i" % length)
        while len(ret) < length:
            s = self.sk.recv(length - len(ret))
            if s == b'': raise RuntimeError("connection closed by remote end")
            # print (b">>>" + s)
            # atgriezt kaa byte ja nav ascii chars
            if s >= (128).to_bytes(1, "big"):
                return s
            # print((">>> " + s.decode(sys.stdout.encoding, 'ignore')))
            ret += s.decode('GBK', "replace")
        return ret


def open_socket(dst, port, secure=False):
    s = None
    res = socket.getaddrinfo(dst, port, socket.AF_UNSPEC, socket.SOCK_STREAM)
    af, socktype, proto, canonname, sockaddr = res[0]
    skt = socket.socket(af, socktype, proto)
    if secure:
        s = ssl.wrap_socket(skt, ssl_version=ssl.PROTOCOL_TLSv1_2, ciphers="ADH-AES128-SHA256")  # ADH-AES128-SHA256
    else:
        s = skt
    s.connect(sockaddr)
    return s


def main(use, pwd):
    s = None
    # 定义routeros的api连接信息
    dst = "192.168.10.1"
    user = use
    passw = pwd
    secure = False
    port = 16666

    # use default username and pasword if not specified
    if len(sys.argv) == 4:
        user = sys.argv[2]
        passw = sys.argv[3]
    elif len(sys.argv) == 3:
        user = sys.argv[2]

    if (port == 0):
        port = 8729 if secure else 8728

    s = open_socket(dst, port, secure)
    if s is None:
        print('could not open socket')
        sys.exit(1)

    apiros = ApiRos(s);
    if not apiros.login(user, passw):
        return

    # 定义存储故障线路的数组
    gz_fzlist = []

    # 定义需要写入routeros的api命令
    inputsentence = ['/ip/route/print', '?>comment=', '?active=false', '?disabled=false', '?static=true',
                     '=.proplist=active,disabled,static,gateway']
    # 发送定义好的api命令
    apiros.writeSentence(inputsentence)
    
    while 1:
        # 读取返回的结果（需要循环读取）
        x = apiros.readSentence()
        print(x)
        # 读取完结果后跳出循环
        if x == ['!done'] or x == ['!re', '=status=finished']:
            break
        try:
            fznum = x[4].split('.')[3]
            if '=disabled=false' in x and '=static=true' in x:
                gz_fzlist.append(fznum)
        except:
            pass

    return gz_fzlist


if __name__ == '__main__':
    use = 'admin'
    pwd = '123456'
    main(use, pwd)

2.数据处理并将结果写入xlsx表格中的代码（write_to_excel.py）：

# ！/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    :2021/8/13 16:00
# @Auth0r  :LRH
# @Email   :1053716711@qq.com
# @File    :write_to_excel.py


import api6
import openpyxl
import time
import requests


def write_xls(fzgz_list, branch_city):
    pass
    wd = openpyxl.load_workbook('.\\故障时间统计.xlsx')
    table = wd.get_sheet_by_name('自用')
    norecove_fz = []  # 存储所有未恢复分支
    count = 1
    # for num in fzgz_list:
    while 1:
        cell_data = table.cell(row=count, column=2).value
        if cell_data:
            pass
            cell_data = str(cell_data)
            cell_data2 = table.cell(row=count, column=5).value
            if cell_data2:
                pass
            else:
                # 统计未恢复的分支
                norecove_fz.append(cell_data)
                # print(cell_data)
                # 给已恢复的分支写入恢复时间
                if cell_data in fzgz_list:
                    pass
                else:
                    gz_time = time.strftime('%Y/%m/%d %H:%M')
                    # print(gz_time)
                    table['E{}'.format(count)] = gz_time
                    norecove_fz.remove(cell_data)
                # print(type(cell_data))
        else:
            gz_time = time.strftime('%Y/%m/%d %H:%M')
            # print(gz_time)
            # 将新挂的分支写入表格
            for num in fzgz_list:
                if num in norecove_fz:
                    pass
                else:
                    table['B{}'.format(count)] = num
                    try:
                        table['C{}'.format(count)] = branch_city[num]
                    except:
                        table['C{}'.format(count)] = '后台还未录入'
                    table['D{}'.format(count)] = gz_time
                    norecove_fz.append(num)
                    count += 1
                    # print(num,branch_city[num])
            break
        count += 1
    print('所有挂分支{}'.format(fzgz_list))
    print('未恢复的分支{}'.format(norecove_fz))
    wd.save('.\\故障时间统计.xlsx')

# 从后台获取各个线路的具体信息(该部分根据本身实际情况修改)
def get_branch_city():
    headers = {
        'authorization': 'cf9c873338edf6fc2feeaa9d7295f920',
        'user-agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) '
                      'Chrome/78.0.3904.108 Safari/537.36 '
    }
    
    # url根据本身实际情况修改
    url = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

    response = requests.get(url, headers=headers)
    # print(response)
    # return response
    branch_city = {}
    result = response.json()
    # print(result)
    result = result['data']['list']['data']
    for i in result:
        # print(i)
        branch = str(i['branch'])
        name = i['name']
        branch_city[branch] = name
        # print(branch,name)
    # print(branch_city)
    return branch_city


if __name__ == '__main__':
    use = 'admin'
    pwd = '123456'
    while 1:
        print(time.strftime('%Y/%m/%d %H:%M'))
        try:
            fzgz_list = api6.main(use, pwd)
        except:
            print('获取故障分支失败------')
            time.sleep(5)
            continue

        try:
            branch_city = get_branch_city()
        except:
            print('获取后台线路信息失败--------')
            time.sleep(5)
            continue
        # print(fzgz_list)
        try:
            write_xls(fzgz_list, branch_city)
        except Exception as e:
            print('数据写入失败--------')
            print(str(e))
            time.sleep(5)
            continue

        time.sleep(600)

3.最终表格中的效果如下（注意：表格需先自己创建好，并填好表头）：