파이썬 자동화 -6-

import paramiko, time, socks, re, os
from getpass import getpass
import pandas as pd
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
from concurrent.futures import ThreadPoolExecutor, as_completed
import threading

def juniper_parse_fixed(df, up_t):
    df.loc[0, "uptime"] = up_t[0]
    return df 

def re_ver(data):
    ex2200_match = re.search(r"ex2200", data)
    if ex2200_match:
        match2 = re.search(r"JUNOS Base OS boot \[(\w{1,2}.\w{1,4}.\w{1,2})\]",data)
        if match2:
            return match2.group(1)
        else:
            return ""
    else:
        match1 = re.search(r"Junos: (\w{1,2}.\w{1,3})",data)
        if match1:
            return match1.group(1)
        else:
            return ""

def keyword_fpc(data, fpc, m):
    lines = data.split("\n")
    fp = []
   
    for line in lines:
        words = line.split()
        if fpc in words:
            idx = words.index(fpc)
            if len(words) >= idx+6:
                com1 = words[idx+0]+ " "+words[idx+1]
                com2 = words[idx+2] + " " + words[idx+3]
                fo = words[idx+4]
                fi = words[idx+5]
                si = words[idx+6]
                fp.append((com1, com2,fo,fi,si))
    return fp

def keyword_vir(data):
    lines = data.split("\n")
    vir_c =[]
    vir = re.compile(r"\((\S+\s+\S+)\)\s+\S+\s+\S+\s+\S+\s+\S+\s+(Master)")
    for line in lines:
        vir_m = vir.search(line)
        if vir_m:
            v1,v5 = vir_m.group(1), vir_m.group(2)
            v2 = ""
            v3 = ""
            v4 = ""
            vir_c.append((v1,v2,v3,v4,v5))
    df_vir = pd.DataFrame(vir_c, columns=["item","rev_ver","part_num","serial","model"])
    return df_vir

def keyword_power(data, power):
    lines = data.split("\n")
    po = []
    for line in lines:
        words = line.split()
        if power in words:
            idx = words.index(power)
            if power == "PSU":
                com1 = words[idx+0]+" "+words[idx+1] 
                com2 = words[idx+2]+" "+words[idx+3]
                fi = words[idx+4]
                si = words[idx+5]
                se = words[idx+6]
            elif len(words) >= idx+7:
                com1 = words[idx+0]+" "+words[idx+1]+" "+words[idx+2] 
                com2 = words[idx+3]+" "+words[idx+4]
                fi = words[idx+5]
                si = words[idx+6]
                se = words[idx+7]
                if se =="PS" and len(words) >= idx +10:
                    se = words[idx+7]+" "+words[idx+8]+" "+words[idx+9]
            po.append((com1, com2, fi, si, se))
    if not po:
        po = [("Power Supply 0","None","None","None","PS 550W AC")]
    return po

def keyword_q5000(data):
    lines = data.split("\n")
    q5000_f = []
    q5000_p = []
    q5000_r = []
    fpc = "FPC"
    power = "Power"
    fan = re.compile(r"(Fan Tray\s+\S+,)\s+(\S+\s+\S+\s+\S+\s+\S+\s+\S+\s+\S+)?")
    cha = re.compile(r"(Chassis)\s+(\S+)\s+(\S+)?")
    
    for line in lines:
        words = line.split()
        if power in words:
            idx = words.index(power)
            if len(words) >= idx+7:
                pcom1 = words[idx+0]+" "+words[idx+1]+" "+words[idx+2]
                pcom2 = words[idx+3]+" "+words[idx+4]
                pfi = words[idx+5]
                psi = words[idx+6]
                pse = words[idx+7]
                q5000_p.append((pcom1, pcom2, pfi, psi, pse))
           
        if fpc in words:
            idx = words.index(fpc)
            if len(words) >= idx+6:
                fcom1 = words[idx+0]+" "+words[idx+1]
                fcom2 = words[idx+2]+ " " +words[idx+3]
                ffo = words[idx+4]
                ffi = words[idx+5]
                ffe = words[idx+6]
                q5000_f.append((fcom1, fcom2,ffo,ffi,ffe))
        
        fan_m = fan.search(line)
        if fan_m:
            fan1,fan5 =  fan_m.group(1),fan_m.group(2)
            fan2 = "" 
            fan3 = ""
            fan4 = ""
            q5000_r.append((fan1,fan2,fan3,fan4,fan5))
                
    df_q5000_r = pd.DataFrame(q5000_r, columns=["item","rev_ver","part_num","serial","model"])
    df_q5000_f = pd.DataFrame(q5000_f, columns=["item","rev_ver","part_num","serial","model"])
    df_q5000_p = pd.DataFrame(q5000_p, columns=["item","rev_ver","part_num","serial","model"])
    return df_q5000_r,df_q5000_f,df_q5000_p

def keyword_q10000(data):
    lines = data.split("\n")
    q10000_c = []
    q10000_f = []
    q10000_p = []
    q10000_r = []
    fpc = "FPC"
    power = "Power"
    cb = "CB"
    cha = re.compile(r"(Chassis)\s+(\S+)\s+(\S+)?")
    
    for line in lines:
        words = line.split()
        if power in words:
            idx = words.index(power)
            if len(words) >= idx+7:
                pcom1 = words[idx+0]+" "+words[idx+1]+" "+words[idx+2]
                pcom2 = words[idx+3]+" "+words[idx+4]
                pfi = words[idx+5]
                psi = words[idx+6]
                pse = words[idx+7]
                q10000_p.append((pcom1, pcom2, pfi, psi, pse))
           
        if fpc in words:
            idx = words.index(fpc)
            if len(words) >= idx+6:
                fcom1 = words[idx+0]+" "+words[idx+1]
                fcom2 = words[idx+2]+ " " +words[idx+3]
                ffo = words[idx+4]
                ffi = words[idx+5]
                ffe = words[idx+6]
                q10000_f.append((fcom1, fcom2,ffo,ffi,ffe))
 
        if cb in words:
            idx = words.index(cb)
            if len(words) >= idx+7:
                rcom1 = words[idx+0]+" "+words[idx+1]
                rcom2 = words[idx+2]+ " " +words[idx+3]
                rfo = words[idx+4]
                rfi = words[idx+5]
                rfe = words[idx+6]
                q10000_r.append((rcom1,rcom2,rfo,rfi,rfe))
                
        cha_m = cha.search(line)
        if cha_m:
            c1,c4,c5 = cha_m.group(1),cha_m.group(2),cha_m.group(3)
            c2 = " "
            c3 = " "
            q10000_c.append((c1,c2,c3,c4,c5))
    df_q10000_c = pd.DataFrame(q10000_c, columns=["item","rev_ver","part_num","serial","model"])
    df_q10000_r = pd.DataFrame(q10000_r, columns=["item","rev_ver","part_num","serial","model"])
    df_q10000_f = pd.DataFrame(q10000_f, columns=["item","rev_ver","part_num","serial","model"])
    df_q10000_p = pd.DataFrame(q10000_p, columns=["item","rev_ver","part_num","serial","model"])
    return df_q10000_c,df_q10000_r,df_q10000_f,df_q10000_p

def keyword_ex8200(data):
    lines = data.split("\n")
    e8200_c = []
    e8200_f = []
    e8200_p = []
    e8200_r = []
    fpc = "FPC"
    power = "PSU"
    cb = "CB"
    cha = re.compile(r"(Chassis)\s+(\S+\s+\S+)\s+(\S+)\s+(\S+)?")
    
    for line in lines:
        words = line.split()
        if power in words:
            idx = words.index(power)
            if len(words) >= idx+7:
                pcom1 = words[idx+0]+" "+words[idx+1]
                pcom2 = words[idx+2]+" "+words[idx+3]
                pfi = words[idx+4]
                psi = words[idx+5]
                pse = words[idx+6]
                e8200_p.append((pcom1, pcom2, pfi, psi, pse))
           
        if fpc in words:
            idx = words.index(fpc)
            if len(words) >= idx+6:
                fcom1 = words[idx+0]+" "+words[idx+1]
                fcom2 = words[idx+2]+ " " +words[idx+3]
                ffo = words[idx+4]
                ffi = words[idx+5]
                ffe = words[idx+6]
                e8200_f.append((fcom1, fcom2,ffo,ffi,ffe))
 
        if cb in words:
            idx = words.index(cb)
            if len(words) >= idx+7:
                rcom1 = words[idx+0]+" "+words[idx+1]
                rcom2 = words[idx+2]+ " " +words[idx+3]
                rfo = words[idx+4]
                rfi = words[idx+5]
                rfe = words[idx+6]
                e8200_r.append((rcom1,rcom2,rfo,rfi,rfe))
                
        cha_m = cha.search(line)
        if cha_m:
            c1,c2,c4,c5 = cha_m.group(1),cha_m.group(2),cha_m.group(3),cha_m.group(4)
            c3 = " "
            e8200_c.append((c1,c2,c3,c4,c5))

    df_e8200_c = pd.DataFrame(e8200_c, columns=["item","rev_ver","part_num","serial","model"])
    df_e8200_r = pd.DataFrame(e8200_r, columns=["item","rev_ver","part_num","serial","model"])
    df_e8200_f = pd.DataFrame(e8200_f, columns=["item","rev_ver","part_num","serial","model"])
    df_e8200_p = pd.DataFrame(e8200_p, columns=["item","rev_ver","part_num","serial","model"])
    return df_e8200_c,df_e8200_r,df_e8200_f,df_e8200_p

def keyword_ex9200(data):
    lines = data.split("\n")
    e9200_cb = []
    e9200_c = []
    e9200_f = []
    e9200_p = []
    e9200_r = []
    fpc = "FPC"
    power = "PEM"
    routing = "Routing"
    cha = re.compile(r"(Chassis)\s+(\S+)\s+(\S+)?")
    cb = re.compile(r"(CB \S+)\s+(\S+\s+\S+)\s+(\S+)\s+(\S+)\s+(\S+)?")
    
    for line in lines:
        words = line.split()
        if power in words:
            idx = words.index(power)
            if len(words) >= idx+7:
                pcom1 = words[idx+0]+" "+words[idx+1]
                pcom2 = words[idx+2]+" "+words[idx+3]
                pfi = words[idx+4]
                psi = words[idx+5]
                pse = words[idx+6]
                e9200_p.append((pcom1, pcom2, pfi, psi, pse))
           
        if fpc in words:
            idx = words.index(fpc)
            if len(words) >= idx+6:
                fcom1 = words[idx+0]+" "+words[idx+1]
                fcom2 = words[idx+2]+ " " +words[idx+3]
                ffo = words[idx+4]
                ffi = words[idx+5]
                ffe = words[idx+6]
                e9200_f.append((fcom1, fcom2,ffo,ffi,ffe))
 
        if routing in words:
            idx = words.index(routing)
            if len(words) >= idx+7:
                rcom1 = words[idx+0]+" "+words[idx+1]+" "+words[idx+2]
                rcom2 = words[idx+3]+ " " +words[idx+4]
                rfo = words[idx+5]
                rfi = words[idx+6]
                rfe = words[idx+7]
                e9200_r.append((rcom1,rcom2,rfo,rfi,rfe))
        cha_m = cha.search(line)
        if cha_m:
            c1,c4,c5 = cha_m.group(1),cha_m.group(2),cha_m.group(3)
            c2 = " "
            c3 = " "
            e9200_c.append((c1,c2,c3,c4,c5))
            
        cb_m = cb.search(line)
        if cb_m:
            c1,c2,c3,c4,c5 = cb_m.group(1),cb_m.group(2),cb_m.group(3),cb_m.group(4),cb_m.group(5)
            e9200_cb.append((c1,c2,c3,c4,c5))
    df_e9200_cb = pd.DataFrame(e9200_cb, columns=["item","rev_ver","part_num","serial","model"])
    df_e9200_c = pd.DataFrame(e9200_c, columns=["item","rev_ver","part_num","serial","model"])
    df_e9200_r = pd.DataFrame(e9200_r, columns=["item","rev_ver","part_num","serial","model"])
    df_e9200_f = pd.DataFrame(e9200_f, columns=["item","rev_ver","part_num","serial","model"])
    df_e9200_p = pd.DataFrame(e9200_p, columns=["item","rev_ver","part_num","serial","model"])
    return df_e9200_c,df_e9200_r,df_e9200_f,df_e9200_p,df_e9200_cb

def juniper_parse(ip_a, u_id, u_pas, lock, ho, ve, fp, po):
    try:
        model = re.compile(r"Model: (\S+)")
        ssh = paramiko.SSHClient()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ssh.connect(ip_a, username=u_id, password=u_pas)
                
        print(f"{ip_a} Connected Complete")
        #interactive shell 선언
        connection = ssh.invoke_shell()

        time.sleep(3)            
        connection.send("set cli screen-length 0\n")
        time.sleep(0.5)
        connection.send(f"show version\n" "show system uptime\n")
        time.sleep(4)
        data = connection.recv(65535)
        data = (data.decode())
        
        m=model.search(data).group(1)
        if m=="ex9208" or m=="ex9214":
            connection.send(f"show chassis hardware model\n" "show chassis hardware | match chassis\n")
            time.sleep(2)
        else:
            connection.send(f"show chassis hardware\n" "show virtual-chassis\n")
            time.sleep(2.5)
        
        data2 = connection.recv(65535)
        data2 = (data2.decode())
        data = data+data2
        ssh.close()

        with open("juniper_rawdata.txt", 'a',newline="") as f:
                f.write(str(data))
    
        host = re.compile(r"Hostname: (\S+)")
        up = re.compile(r"System booted: \d{1,4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2} \w{1,3} \((\w{1,6} \d{1,2}:\d{1,2} ago)\)")

        fpc = "FPC"
        power = "Power"
        h=host.search(data).group(1)
        m=model.search(data).group(1)
        v = re_ver(data)
        if m=="ex8208":
            df_e8200_c,df_e8200_r,df_e8200_f, df_e8200_p = keyword_ex8200(data)
            df_c = df_e8200_c
            df_fp = df_e8200_f
            df_po = df_e8200_p
            df_router_data = df_e8200_r
            df_cb = None
        elif "ex92" in m:
            df_e9200_c,df_e9200_r,df_e9200_f, df_e9200_p, df_e9200_cb = keyword_ex9200(data)
            df_c = df_e9200_c
            df_fp = df_e9200_f
            df_po = df_e9200_p
            df_router_data = df_e9200_r
            df_cb = df_e9200_cb
        elif m == "qfx10008":
           df_q10000_c,df_q10000_r,df_q10000_f,df_q10000_p = keyword_q10000(data)
           df_c = df_q10000_c
           df_fp = df_q10000_f
           df_po = df_q10000_p
           df_router_data = df_q10000_r
           df_cb = None
        elif "qfx51" in m  or "qfx5200" in m :
            df_q5000_r,df_q5000_f,df_q5000_p = keyword_q5000(data)
            df_c = None
            df_fp = df_q5000_f
            df_po = df_q5000_p
            df_router_data = df_q5000_r
            df_cb = None
        else:    
            fp = keyword_fpc(data, fpc, m)
            po = keyword_power(data, power)
            df_vir = keyword_vir(data)
            df_router_data = df_vir
            df_c = None
            df_cb = None
            df_fp = pd.DataFrame(fp, columns=["item","rev_ver","part_num","serial","model"])
            df_po = pd.DataFrame(po, columns=["item","rev_ver","part_num","serial","model"])
        
        up_t = [up.search(data).group(1)]

        raw_data1 = {"hostname" : [],"ipaddr" : [],"version" : [],"item" : [],"rev_ver" : [],
                    "part_num" : [],"serial" : [],"model": [],"uptime" : []}
        if df_c is not None:
            for _, row in df_c.iterrows():
                raw_data1["hostname"].append(h)
                raw_data1["version"].append(v)
                raw_data1["ipaddr"].append(ip_a)
                raw_data1["item"].append(row["item"])
                raw_data1["rev_ver"].append(row["rev_ver"])
                raw_data1["part_num"].append(row["part_num"])
                raw_data1["serial"].append(row["serial"])
                raw_data1["model"].append(row["model"])
                raw_data1["uptime"].append("")
        for _,row in df_fp.iterrows():
                raw_data1["hostname"].append(h)
                raw_data1["version"].append(v)
                raw_data1["ipaddr"].append(ip_a)
                raw_data1["item"].append(row["item"])
                raw_data1["rev_ver"].append(row["rev_ver"])
                raw_data1["part_num"].append(row["part_num"])
                raw_data1["serial"].append(row["serial"])
                raw_data1["model"].append(row["model"])
                raw_data1["uptime"].append("")
        for _,row in df_po.iterrows():
                raw_data1["hostname"].append(h)
                raw_data1["version"].append(v)
                raw_data1["ipaddr"].append(ip_a)
                raw_data1["item"].append(row["item"])
                raw_data1["rev_ver"].append(row["rev_ver"])
                raw_data1["part_num"].append(row["part_num"])
                raw_data1["serial"].append(row["serial"])
                raw_data1["model"].append(row["model"])
                raw_data1["uptime"].append("")
        if df_router_data is not None:
            for _, row in df_router_data.iterrows():
                raw_data1["hostname"].append(h)
                raw_data1["version"].append(v)
                raw_data1["ipaddr"].append(ip_a)
                raw_data1["item"].append(row["item"])
                raw_data1["rev_ver"].append(row["rev_ver"])
                raw_data1["part_num"].append(row["part_num"])
                raw_data1["serial"].append(row["serial"])
                raw_data1["model"].append(row["model"])
                raw_data1["uptime"].append("")
        if df_cb is not None:
            for _, row in df_cb.iterrows():
                raw_data1["hostname"].append(h)
                raw_data1["version"].append(v)
                raw_data1["ipaddr"].append(ip_a)
                raw_data1["item"].append(row["item"])
                raw_data1["rev_ver"].append(row["rev_ver"])
                raw_data1["part_num"].append(row["part_num"])
                raw_data1["serial"].append(row["serial"])
                raw_data1["model"].append(row["model"])
                raw_data1["uptime"].append("")

        df1 = pd.DataFrame(raw_data1)
        df1 = juniper_parse_fixed(df1, up_t)
        print(df1)  
           
        with lock:        
            wb = openpyxl.load_workbook("juniper_test.xlsx")
            ws = wb["Sheet1"]
            rows = dataframe_to_rows(df1, index=False, header=False)
            for r_idx,row in enumerate(rows, ws.max_row+1):
                for c_idx, value in enumerate(row, 1):
                    ws.cell(row=r_idx, column=c_idx, value=value)

            wb.save("juniper_test.xlsx")
    except paramiko.AuthenticationException:
        print("###### Invalid Username or Password ######\n")
                   
        
    except paramiko.ssh_exception.SSHException as e:
        print("something is wrong with SSH : {}".format(e))
 
    
    except Exception as ex:
        print("연결오류",ex)

def main():
    with open ("switch_list.txt") as f:
        content=f.read().splitlines()

    u_id = input("Enter your Username:")
    if not u_id:
        u_id = "admin"
        print(f"if you No Username input please Enter to keep goning default ID {u_id}")

    u_pas= getpass(f"Enter Password of the user {u_id}: ")or "admin"   

    file = "juniper_rawdata.txt"
    if os.path.isfile(file):
        os.remove(file)
        print("RAW data file removed")
    else:
        print("raw data file not found keep going")
    
    ho = []
    ve = []
    fp = []
    po = []
    max_threads = 10
    
    thread_pool = ThreadPoolExecutor(max_workers=max_threads)
    lock = threading.Lock()
    futures = [thread_pool.submit(juniper_parse, device, u_id, u_pas, lock, ho, ve, fp, po)for device in content]
    for future in as_completed(futures):
        future.result()
                
if __name__ == "__main__":
    print ("we are ready to start")
    main()

 

이 코드는 주니퍼 장비의 자산정보를 확인하기 위해 제작된 코드로서 지원되는 장비는 ex3000시리즈 ex4000시리즈 qfx5000시리즈 qfx10000시리즈 ex8200시리즈 ex9200시리즈 이다 해당되는 모델과 커맨드 출력값이 같다면 다른모델에서도 사용이 가능하다 

ex3000과4000시리즈 에서는 FPC정보 PSU정보그리고 Virtual-chassis의 Master FPC 정보를 가지고 오며

qfx5000 시리즈의 경우 FPC 정보 PSU정보 FAN 정보를 가지고 오며

qfx10000시리즈의 경우 chassis 정보 FPC 정보 PSU 정보 CB정보를 가지고 오며

ex8200시리즈의 경우 qfx10000시리즈와 같은 정보를 가지고 오고

ex9200시리즈의 경우 chassis 정보 FPC 정보 PEM 정보 routing engine정보 CB 정보를 가지고게 짜여있다

 

코드 리뷰를 해보자면 

switch_list.txt파일에 각 줄별로 입력된 IP 정보를 가지고 와서 SSH 접속을 시도하며 SSH 접속시 아이디와 패스워드는 유저의 입력을 받아서 진행되게 되어있으며

멀티쓰래드 기능을 사용하여 최대 10개의 ssh 세션을 동시에 만들게 되어 있으며 SSH에 접속한 뒤 data변수에 커맨드

입력 후 출력값을 저장 하며 juniper_rawdata.txt파일에는 출력값을 그대로 저장하여 디버그 할때 사용할수 있도록 하였다

data 변수를 기반으로 모델명을 확인하여 모델에 맞는 함수로 결과 값을 보낸 후 

함수에 모델에 맞게 필요정보를 파싱 후 dataframe형태로 만들어 엑셀파일에 저장하게 되어있으며

멀티쓰래드 기능이 활성화 되어 있어 엑셀파일에 동시에 쓸경우 충돌이 발생할 수 있기 때문에 loc 기능을 사용하여 엑셀파일이 사용중에는 쓰지 못하도록 하였다

 

필요정보를 파싱 후 dataframe으로 만든 이유는 멀티쓰래드 기능때문에 한줄씩 엑셀파일에 쓰다보면 데이터가 썩일 가능성이 있다는 판단하에 dataframe형태로 만들어 한번에 표형태로 쓰게 만들었다

 

이 코드를 사용하기 전에 juniper_test.xlsx 이 파일이 코드와 같은 폴더에 있어야 한다