python批量处理网络设备的巡检文本文件，提取关键指标写入表格或数据库

当我们在做巡检时，需要从一批show文本中提取一些关键指标保存到表格或数据库，这时可以尝试用python来处理，思路大概就是先从各个文本提取hostname，再进行数据分块，接着对相关命令(比如show version，show inventory)的输出信息做正则提取，最后写入表格或数据库保存。

 1、读取文本所在文件夹，获取文本路径，写入列表

def GetPath():
    for root, dirs, files in os.walk(u'Before'):
        for file in files:
            #文件相对路径（提取文件名）
            #xlsname = file.decode('gbk').encode('utf-8')
            filename = file#.split('.')[0]
            filename_list.append(filename)
            
            #文件绝对路径
            xlspath = os.path.join(root,file)
            dirpath_list.append(xlspath)
    print(len(dirpath_list))

 2、主函数调用多线程方式对每个文件进行解析

    #执行多线程
    threads_data_list =[]   #多线程结果列表
    while filename_list:
        filename = filename_list[0]
        filename_list.pop(0)
        dirpath = dirpath_list[0]
        dirpath_list.pop(0)
        t = MyThread(main,(filename,dirpath), main.__name__)
        threads_data_list.append(t)
    for thr in threads_data_list:
        thr.start()
    for thr in threads_data_list:
        if thr.is_alive():
            thr.join()
    time.sleep(0.2)

3、读取文本内容，提取hostname，重新命名文本。比如处理的是show version 及 show inventory命令的输出信息。

def main(filename,dirpath):
    output  = open (dirpath, 'r',encoding='utf-8-sig')
    output = output.read()
    hostname = 'N/A'
    #提取hostname，重新命名txt
    matchObj = re.search( r'hostname (\S+)', output, re.M|re.I)
    if matchObj:
        hostname = matchObj.group(1)
        new_filename_list.append(hostname)
        WriteTxt(hostname,output)
    else:
        error_list.append(dirpath)

    ip = '  '
    matchObj1 = re.search('((\d+\.){3}\d+)',filename,re.M|re.I)
    if matchObj1:
        ip = matchObj1.group(1)
    
    #记录文本文件名称转换记录
    echo_info = filename + '>>>' +hostname +'>>>' + ip + '\n'
    with open('.\\log.txt','a')as td:
        td.write(echo_info)
    
    if hostname != 'N/A':
        deivce_data = GetData(output,hostname,ip)
        return deivce_data

def GetData(info,hostname,ip):
    if re.search(r'www\.cisco\.com',info,re.I):
        brand='cisco'
        #print(brand)

    piece_data_list = info.split('{}[#|>|\]]'.format(hostname))
    #print(hostname)
    for piece_data in piece_data_list:
        data_info_dic={
            'hostname':hostname,
            'ip':ip,
            'HareWareModel':'',
            'UpTime':'',
            'SystemImageFile':'',
            'SystemSN':'',
            'show inventory':''
        }

        #查看模块命令show inventory，多行匹配，多个匹配模式，返回元组列表
        if re.search('show inventory|inv.*',piece_data,re.I|re.M):
            # #show inventory : NAME,PID,SN
            data_info_dic = data_parsing.parsing('show inventory',piece_data,data_info_dic)
            
        #命令show version，单行匹配
        if re.search('show version|ver.*',piece_data,re.I|re.M):
            #show version : HareWareModel,UpTime,System image file,SystemSN
            data_info_dic = data_parsing.parsing('show version',piece_data,data_info_dic)
            break

    return data_info_dic
    #WriteExcel(data_info_dic)

4、数据写入表格

主函数中调用WriteExcel
    #数据写入表格
    WriteExcel(threads_data_list)

def WriteExcel(all_data_list):

    rownum = 2
    rownum1 = 2
    number = 1
    #新建表格过程
    Workbook = xlwt.Workbook()         #创建工作簿

    alignment = xlwt.Alignment()
    alignment.horz = 0x02    # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
    alignment.vert = 0x01    # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
    alignment.wrap = 1    # 设置自动换行

    # 初始化样式
    style = xlwt.XFStyle()
    style.alignment = alignment

    sheet1 = Workbook.add_sheet(u'设备信息',cell_overwrite_ok=True) #创建sheet
    row0 = ['序号','主机名','管理IP','型号','运行时间','软件版本','序列号']
    row1 = ['Name','PID','序列号']

    #创建表头信息
    for i in range(0,len(row0)):
        sheet1.write_merge(0,1,i,i,row0[i],style)

    sheet1.write_merge(0,0,len(row0),len(row0)+2,'模块信息',style)

    for i in range(0,len(row1)):
        sheet1.write(1,i+len(row0),row1[i],style)

    for i in range(0,len(row0)+len(row1)):
        sheet1.col(i).width = 20 * 256      #xlwt中列宽的值表示方法：默认字体0的1/256为衡量单位。xlwt创建时使用的默认宽度为2960，既11个字符0的宽度,所以我们在设置列宽时可以用如下方法：width = 256 * 20    #256为衡量单位，20表示20个字符宽度


    for data in all_data_list:
        if data.get_result() != None:
            data_info_dic =data.get_result()
            try:
                hostname = data_info_dic['hostname']
                ip = data_info_dic['ip']
                data_list = data_info_dic['show inventory']

                len_num = len(data_list)
                if len_num >=1:
                    # write_merge(a,b,c,d,message)函数将从第a行到第b行的第c列到第d列的单元格合并，并填入内容message
                    sheet1.write_merge(rownum,rownum+len_num-1,0,0,str(number),style)  #第一列
                    sheet1.write_merge(rownum,rownum+len_num-1,1,1,hostname,style)  #第一列
                    sheet1.write_merge(rownum,rownum+len_num-1,2,2,ip,style)
                    sheet1.write_merge(rownum,rownum+len_num-1,3,3,data_info_dic['HareWareModel'],style)
                    sheet1.write_merge(rownum,rownum+len_num-1,4,4,data_info_dic['UpTime'],style)
                    sheet1.write_merge(rownum,rownum+len_num-1,5,5,data_info_dic['SystemImageFile'],style)
                    sheet1.write_merge(rownum,rownum+len_num-1,6,6,data_info_dic['SystemSN'],style)

                    for data in data_list:
                        for k in range(0,len(data)):
                            # 每行，逐列写入
                            sheet1.write(rownum1,k+len(row0),data[k].strip(),style)
                        rownum1 += 1
                    rownum = rownum + len_num

                else:
                    sheet1.write(rownum,0,str(number),style)
                    sheet1.write(rownum,1,hostname,style)  #第一列
                    sheet1.write(rownum,2,ip,style)
                    sheet1.write(rownum,3,data_info_dic['HareWareModel'],style)
                    sheet1.write(rownum,4,data_info_dic['UpTime'],style)
                    sheet1.write(rownum,5,data_info_dic['SystemImageFile'],style)
                    sheet1.write(rownum,6,data_info_dic['SystemSN'],style)
                    rownum += 1
                    rownum1 += 1

                number += 1
            except Exception as e:
                print(str(e))
                print(data.get_result())

    Workbook.save('./demo2.xls') #保存文件 

表格格式如图所示 ：

 demo code：https://download.csdn.net/download/bosshuang666/87647395

欢迎交流！