使用python 实现linux 系统巡检远程获取系统资源情况，导出为excel表格

背景： 因为服务器很多，刚开始一台一台手动巡检，效率很低，于是我想能不能写个工具实现一劳永逸，于是我想到了python ,python 具有丰富的类库，且语言简洁，作为运维使用来说比较方便

上代码

import paramiko
from openpyxl import Workbook,load_workbook
from openpyxl.styles import Alignment
# 建立 SSH 客户端对象
ssh = paramiko.SSHClient()
# 当 SSH 没有被信任时自动添加主机密钥
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())

# 主机清单
host_lst = {'host1': {
    'host': '192.168.31.200',
    'port': 22,
    'user': 'root',
    'password': '123456'
},
 'host2' : {
     'host': '192.168.31.201',
     'port': 22,
     'user': 'root',
     'password': '123456'
}
}

def disk_func(ssh):
    # 获取磁盘使用率
    disk_cmd = "df -h"
    stdin, stdout, stderr = ssh.exec_command(disk_cmd)
    output = stdout.read().decode('utf-8')  # 获取标准输出
    output = output.strip().split("\n")
    disk_info = ''
    # 处理数据，得到最后的磁盘信息
    for line in output[1:]:
        line = [s for s in line.split() if s.strip()]
        if (line[0].startswith("/dev")) and (line[-1] not in ['/boot', '/boot/efi']):
            line_str = f"{line[-1]}: {line[-2]}\n"
            disk_info = str(disk_info) + line_str 

    return  disk_info

def cpu_func(ssh):
    # 获取cpu使用率
    cpu_cmd = "top -bn1 | grep Cpu | awk '{print $8}'"
    stdin, stdout, stderr = ssh.exec_command(cpu_cmd)
    output = stdout.read().decode('utf-8')  # 获取标准输出

    return  "{:.2f}%".format(100 - float(output))

def mem_func(ssh):
    # 获取内存使用率
    mem_cmd = "free"
    stdin,stdout,stderr = ssh.exec_command(mem_cmd)
    #output = stdout.read().decode("utf-8").strip().split("\n")
    output = stdout.read().decode("utf-8")
    out_lst = [i.strip().split() for i in output.strip().split("\n") if 'mem' in i.lower()][0]
    result = float(out_lst[2]) / float(out_lst[1]) * 100
    return "{:.2f}%".format(result)

# 插入巡检结果
def save_excel(info_lst):
    row = ws.max_row + 1
    for i in range(len(info_lst)):
        col = i + 1 
        cell = ws.cell(row=row,column=col)
        if i == 1 :
            cell.alignment = Alignment(wrap_text=True)
        ws.column_dimensions[cell.column_letter].auto_size = True     # 设置列宽适应内容
        ws.row_dimensions[cell.row].height = None                     # 设置行高适应内容

        cell.value = info_lst[i]

              

# 定义一个工作薄
# 实例化
path = 'D:/test/check_linux.xlsx'
sheet = "巡检结果记录"
try:
    wb = load_workbook(path)
except FileNotFoundError:
    wb = Workbook()

# 创建一个sheet
if sheet in wb.sheetnames:
    ws = wb[sheet]
else:
    ws = wb.create_sheet(title=sheet)
    
ws.delete_rows(2, ws.max_row)
ws.merge_cells('A1:E1')
ws['A1'] = sheet
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')

# 插入巡检指标
option_lst = ["ip地址","磁盘使用率","cpu使用率","内存使用率"]

# 行
row = ws.max_row + 1
for i in range(len(option_lst)):
    col = i + 1 
    cell = ws.cell(row=row,column=col)
    cell.value = option_lst[i]



# 执行
for i in host_lst.values():
    host = list(i.values())[0]
    port = list(i.values())[1]
    user = list(i.values())[2]
    password = list(i.values())[3]
    try:
        # 连接远程主机
        ssh.connect(
            hostname=host,
            port=port,
            username=user,
            password=password
        )
        print("SSH connection successful!")
        # 磁盘
        disk_info = disk_func(ssh)
        # cpu
        cpu_info = cpu_func(ssh)
        # 内存
        mem_info = mem_func(ssh)

        print(disk_info)
        print(cpu_info)
        print(mem_info)

        os_lst = [host,disk_info,cpu_info,mem_info]
        save_excel(os_lst)
        
    except Exception as e:
        print(f"SSH connection failed: {e}")
    finally:
        ssh.close()

wb.save(path)
print(f"结果保存在{path}中")

不足之处还望批评指正