Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@goshawking

Pythonでshow interface情報をxlsx出力してみた

Last updated at Posted at 2019-10-13

はじめに

Cisco機器のshow interfaceを定期的に採取して調査する必要があったので、
Pythonで試行錯誤してみたアウトプットです。

目的

show interfaceを定期的に出力したテキストファイルから
各種カウンターを取り出して表形式で出力する。
showコマンド採取時のclockも一緒に取得したい。

Pythonスクリプトの作成

メイン処理

main.py

# !/usr/bin/env python

if __name__ == '__main__': # スクリプトを直接実行時は__name__に__main__が自動で入る

    os.chdir(os.path.dirname(os.path.abspath(__file__))) #カレントを実行ファイルのフォルダに移動
    hdr_list = ['Time' , 'CPU(5sec)' , 'Port' , 'In-Traffic(bps)'  , 'Out-Traffic(bps)' , 'In-Packets' , 'Out-Packets' , 'Out-Drop' ,
                'In Error' , 'CRC(In)' , 'Frame(In)' , 'Over(In)' , 'Ignor(In)' ,
                'Out Error' , 'Collisions(Out)' , 'IF-Reset(Out)' , '']
    counter_list = ['-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-']
    rows_txt = ''

    file_paths = files_select()
    try:
        for file_path in file_paths:
            with open(file_path , 'r') as f:
                show_flag = False # show intを見つけた時True
                port_flag = False # Port情報を収集するときTrue
                row_count = 1
                file_name = os.path.basename(file_path)
        
                # 1行づつ読込
                for line in f:
                    # show intを見つけたらホスト名をピックアップ 
                    if ( re.match(r'(\S+)#sho?w? (int|interfaces?)$' , line) ):
                        hostname = re.match(r'(\S+)#sho?w? (int|interfaces?)$' , line)
                        h_name = hostname.group(1)

                        show_flag = True
                        # show intを見つけたらxlsxファイルを作成
                        if ( row_count == 1 ):
                            xlsx_temp_create(file_name)
                            wb = OPP.load_workbook('{0}.xlsx'.format(file_name))
                            sheet = wb['show interface']
                            continue

                    elif ( show_flag and re.search(r'Load for five secs: (\S+)\/(\S+);' , line) ):
                        cpu = re.search(r'Load for five secs: (\S+)\/(\S+);' , line)
                        cpu5sec = '{0}/{1}'.format(cpu.group(1) , cpu.group(2))
                        continue

                    elif ( show_flag and re.search(r'Time source is NTP, (\S+) ' , line) ):
                        ntp = re.search(r'Time source is NTP, (\S+) ' , line)
                        continue

                    elif ( show_flag and re.match(r'(\S+) is ' , line) ):

                        # 1ポート目
                        if ( row_count == 1 ):
                            interface = re.match(r'(\S+) is ' , line)
                            port_flag = True
                            row_count += 1
                            counter_list[0] = ntp.group(1)
                            counter_list[1] = cpu5sec
                            counter_list[2] = interface.group(1)
                            continue

                        # 2ポート目以降
                        else:
                            counter_list.append('')
                            # ワークシート、データのリスト、入力行数
                            xlsx_write(sheet , counter_list , row_count)

                            # 初期化して再度カウント
                            counter_list = ['-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-']
                            interface = re.match(r'(\S+) is ' , line)
                            port_flag = True
                            row_count += 1
                            counter_list[0] = ntp.group(1)
                            counter_list[1] = cpu5sec
                            counter_list[2] = interface.group(1)
                            continue

                    elif ( port_flag and re.search(r'5 minute input rate (\S+) bits/sec, (\S+) packets/sec' , line) ):
                        in_count = re.search(r'5 minute input rate (\S+) bits/sec, (\S+) packets/sec' , line)
                        counter_list[3] = in_count.group(1)
                        continue
                
                    elif ( port_flag and re.search(r'5 minute output rate (\S+) bits/sec, (\S+) packets/sec' , line) ):
                        out_count = re.search(r'5 minute output rate (\S+) bits/sec, (\S+) packets/sec' , line)
                        counter_list[4] = out_count.group(1)
                        continue

                    elif ( port_flag and re.search(r' (\S+) packets input, (\S+) bytes' , line) ):
                        in_total = re.search(r' (\S+) packets input, (\S+) bytes' , line)
                        counter_list[5] = in_total.group(1)
                        continue
    
                    elif ( port_flag and re.search(r' (\S+) packets output, (\S+) bytes' , line) ):
                        out_total = re.search(r' (\S+) packets output, (\S+) bytes' , line)
                        counter_list[6] = out_total.group(1)
                        continue

                    elif ( port_flag and re.search(r'Total output drops: (\S+)' , line) ):
                        out_drop = re.search(r'Total output drops: (\S+)' , line)
                        counter_list[7] = out_drop.group(1)
                        continue

                    elif ( port_flag and re.search(r' (\S+) input errors, (\S+) CRC, (\S+) frame, (\S+) overrun, (\S+) ignored' , line) ):
                        in_errors = re.search(r' (\S+) input errors, (\S+) CRC, (\S+) frame, (\S+) overrun, (\S+) ignored' , line)
                        counter_list[8]  = in_errors.group(1)
                        counter_list[9]  = in_errors.group(2)
                        counter_list[10]  = in_errors.group(3)
                        counter_list[11]  = in_errors.group(4) # 処理能力超過によりバッファに送れなかったパケット
                        counter_list[12] = in_errors.group(5) # バッファ不足による破棄
                        continue

                    elif ( port_flag and re.search(r' (\S+) output errors, (\S+) interface resets' , line) ):
                        out_errors_svi = re.search(r' (\S+) output errors, (\S+) interface resets' , line)
                        counter_list[13] = out_errors_svi.group(1)
                        counter_list[15] = out_errors_svi.group(2)
                        continue

                    elif ( port_flag and re.search(r' (\S+) output errors, (\S+) collisions, (\S+) interface resets' , line) ):
                        out_errors = re.search(r' (\S+) output errors, (\S+) collisions, (\S+) interface resets' , line)
                        counter_list[13] = out_errors.group(1)
                        counter_list[14] = out_errors.group(2)
                        counter_list[15] = out_errors.group(3)
                        continue

                    elif ( show_flag and re.match(r'(\S+)#' , line) ):

                        #最終行の書き込み後フラグクリア
                        counter_list.append('')
                        # ワークシート、データのリスト、入力行数
                        xlsx_write(sheet , counter_list , row_count)
                        counter_list = ['-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-']
                
                        show_flag = False
                        port_flag = False
                        continue
                
                    else:
                        pass

            # 保存
            wb.save('{0}.xlsx'.format(file_name))
            rows_txt += '{0}の出力行は{1}\n'.format(h_name , row_count - 1 )

    except FileNotFoundError:
        sys.exit()

    TKM.showinfo('パース終了' , rows_txt)

ファイルのパスを取得

files_select.py
def files_select():
    try:
        fTyp = [('' , '.txt') , ('' , '.log')]
        current = os.path.abspath(os.path.dirname(__file__))
    
        TKM.showinfo('ログ選択' , 'show interfaceのログファイルを選択')

        file_path = TKF.askopenfilenames(filetypes = fTyp , initialdir = current) # 複数時はaskopenfilenames
        file_paths = list(file_path)

        return file_paths

    except FileNotFoundError:
        pass

xlsxへの書き込み

xlsx_temp_create.py
# xlsxのテンプレート作成
def xlsx_temp_create(file_name):
    
    wb = OPP.Workbook()
    wb.create_sheet(index = 0 , title = 'show interface')
    sheet = wb['show interface']

    # 網掛け指定
    blue = PatternFill(patternType = 'solid' ,
                fgColor='0066FF' ,
                bgColor='0066FF')
   
    for col_ptn in ['A' , 'B' , 'C' , 'D' , 'E' , 'F' , 'G' , 'H' , 'I' ,
                    'J' , 'K' , 'L' , 'M' , 'N' , 'O' , 'P']:
        sheet.column_dimensions[col_ptn].width = 16 # セル幅
        sheet[col_ptn + '1'].fill = blue            # 網掛け
        sheet.freeze_panes = 'A2'                   # セル固定

    # ワークシート、データのリスト、入力行数
    xlsx_write(sheet , hdr_list , 1)

    # 保存
    save = True

    while ( save == True ):
        try:
            wb.save('{0}.xlsx'.format(file_name))
            save = False
    
        except PermissionError:
            TKM.showinfo('Error' , 'xlsxファイルを閉じてください')
xlsx_write.py
# xlsx書込み
def xlsx_write(sheet , col_list , r):
    font = Font(name = 'メイリオ' ,
                size = 9 ,
                bold = False ,
                italic = False ,
                underline = 'none',
                strike = False,
                color = 'FF000000')

    for col in range(1 , len(col_list)): # 1から入力カラム値まで記載
        c = col - 1                      # リストインデックスは-1する(0開始)
        sheet.cell(row = r , column = col).value = col_list[c] # 行row_count 列1からcol_maxまで
        sheet.cell(row = r , column = col).font = font         # フォント合わせ
xlsx_temp_create.py
# xlsx書込み
def xlsx_write(sheet , col_list , r):
    font = Font(name = 'メイリオ' ,
                size = 9 ,
                bold = False ,
                italic = False ,
                underline = 'none',
                strike = False,
                color = 'FF000000')

    for col in range(1 , len(col_list)): # 1から入力カラム値まで記載
        c = col - 1                      # リストインデックスは-1する(0開始)
        sheet.cell(row = r , column = col).value = col_list[c] # 行row_count 列1からcol_maxまで
        sheet.cell(row = r , column = col).font = font         # フォント合わせ

~~~

# 更新履歴
2020/3/5
matchからsearchに変更、複数ファイルを選択できるように変更
0
1
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?