python巡检服务器自动生成excel报告

由于一些特殊情况，不允许安装监控平台，为了巡检方便自己写了python脚本

python代码：

#!/usr/bin/env python
# -*-coding:utf8 -*-
# @Time : 2020/12/11 11:15
# @Author : riches
# @Site :
# @File : hnxj.py
# @Software: PyCharm
# -*- coding: utf-8 -*-
import os, sys

reload(sys)
sys.setdefaultencoding('utf8')
import paramiko
import xlsxwriter
import time


def sshexeccmd(ip):
    tmplist = {}
    tmplist["ip"] = ip
    tmplist["cpuuse"] = None
    tmplist["momeryall"] = None
    tmplist["momeryuse"] = None
    tmplist["diskall"] = None
    tmplist["diskuse"] = None
    tmplist["homeall"] = None
    tmplist["homeuse"] = None
    tmplist["datause"] = None
    tmplist["flowuse"] = None
    tmplist["flow1use"] = None
    try:
        ssh = paramiko.SSHClient()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
       pkey = paramiko.RSAKey.from_private_key_file('/root/.ssh/id_rsa')
        #pkey = paramiko.RSAKey.from_private_key_file('C:\\Users\\riches\\.ssh\\id_rsa')#Windows系统写法

        # ssh.connect(hostname=ip, port=22, username='root', pkey=pkey)
        ssh.connect(hostname=ip, port=22, username='xunjian', pkey=pkey)#建立专用巡检账号避免root账号直接登录
        # stdin, stdout, stderr = ssh.exec_command(cmd)#命令基本格式
        stdincpu, stdoutcpu, stderrcpu = ssh.exec_command(
            "top -b -n 1 | head -n 4 | grep 'Cpu(s)' | awk '{print $2}' | cut -d 'u' -f 1")
        stdinmomeryall, stdoutmomeryall, stderrmomeryall = ssh.exec_command(
            "free -m | awk '{print $2}' | awk 'NR==2{print}'")
        stdinmomery, stdoutmomery, stderrmomery = ssh.exec_command("echo `free -m | sed -n '2p' | awk '{print  ($2-$7)/$2*100}'|awk -F'.' '{print $1}'`%")
        stdindiskall, stdoutdiskall, stderrdiskall = ssh.exec_command("df -hP | awk '/\/$/ {print $2}'")
        stdindisk, stdoutdisk, stderrdisk = ssh.exec_command("df -hP | awk '/\/$/ {print $5}'")
        stdinhomeall, stdouthomeall, stderrhomeall = ssh.exec_command(
            "df -hP /home | awk '{print $2}' | awk 'NR==2{print}'")
        stdinhome, stdouthome, stderrhome = ssh.exec_command(
            "df -hP /home | awk '{print $5}' |awk 'NR==2{print}'")
        stdindata, stdoutdata, stderrdata = ssh.exec_command(
            "df -hP /data | awk '{print $5}' | awk 'NR==2{print}'")
        stdinflow, stdoutflow, stderrflow = ssh.exec_command(
            "pescli localhost show rate|grep eth2 |awk -F: '{print $2}'|awk 'NR==1{print $1}'| sed s'/M,//'")
        stdinflow1, stdoutflow1, stderrflow1 = ssh.exec_command(
            "pescli localhost show rate|grep eth4 |awk -F: '{print $2}'|awk 'NR==1{print $1}'| sed s'/M,//' ")

        tmplist["ip"] = ip
        tmplist["cpuuse"] = (stdoutcpu.read()).replace("\n", "")
        tmplist["momeryall"] = (stdoutmomeryall.read()).replace("\n", "")
        tmplist["momeryuse"] = (stdoutmomery.read()).replace("\n", "")
        tmplist["diskall"] = (stdoutdiskall).read().replace("\n", "")
        tmplist["diskuse"] = (stdoutdisk.read()).replace("\n", "")
        tmplist["homeall"] = (stdouthomeall).read().replace("\n", "")
        tmplist["homeuse"] = (stdouthome).read().replace("\n", "")
        tmplist["datause"] = (stdoutdata).read().replace("\n", "")
        tmplist["flowuse"] = (stdoutflow).read().replace("\n", "")
        tmplist["flow1use"] = (stdoutflow1).read().replace("\n", "")

        print tmplist
        return tmplist

        ssh.close()
    except Exception, e:
        print e
        return tmplist


def trywexrestr(lists):
    nowtime = time.strftime('%Y-%m-%d-%H-%M-%S', time.localtime(time.time())) + ""
    bookurl = "systems" + nowtime + 'info.xlsx'
    # filenames=nowtime+'info.xlsx'
    workbook1 = xlsxwriter.Workbook(bookurl)
    worksheet = workbook1.add_worksheet()
    title = [u'IP地址',u'CPU使用率', u'内存总量', u'内存使用率',u'根目录大小', u'根目录使用百分比', u'home大小' \
        , u'home使用率', u'data使用率', u'flow流量', u'flow1流量']
    format = workbook1.add_format()
    worksheet.set_column(0, 15, 20)
    format.set_bold()
    worksheet.write_row('A1', title, format)
    row = 1

    for a in lists:
        worksheet.write(row, 0, a["ip"])
        worksheet.write(row, 1, a["cpuuse"])
        worksheet.write(row, 2, a["momeryall"])
        worksheet.write(row, 3, a["momeryuse"])
        worksheet.write(row, 4, a["diskall"])
        worksheet.write(row, 5, a["diskuse"])
        worksheet.write(row, 6, a["homeall"])
        worksheet.write(row, 7, a["homeuse"])
        worksheet.write(row, 8, a["datause"])
        worksheet.write(row, 9, a["flowuse"])
        worksheet.write(row, 10, a["flow1use"])

        row = row + 1
    workbook1.close()


def readinfo():
    myfile = open('/home/system_check/xunjian/ip.txt', 'r')
    listall = []
    for line in myfile:
        # con=line.split()
        ip = line.strip()
        tmplist = sshexeccmd(ip)
        listall.append(tmplist)
    return listall


listall = readinfo()
trywexrestr(listall)

ip.txt

每个ip一行

运行脚本效果如下：