python 读取openTSDB自动生成网络监控日报（第二版）

第二版程序引入了yaml配置文件，重新抽象了一下程序。发表出来，希望可以对读者有帮助。
<meta charset="utf-8">

该版引入yaml配置文件

整个程序增加了代码复用程度

文件一：主程序

# coding:utf-8
# @author:gswu
import yaml
import time
import requests
import openpyxl

startTime = "24h-ago"  # 定义起始时间
filename = "Net Report.xlsx"  # 定义模板表的名称，路径为同文件夹
configfile="cfg.yaml"


def get_data_by_get(query):
    # 通过接口获取数据，返回值为数据的字典
    r = requests.get("http://192.168.51.6:4242/api/query?" + query)
    if len(r.json()) > 0:
        dps = r.json()[0]['dps']
        return dps
    else:
        return None


def getCPU(ip, row, column):
    # 获取CPU的过往最大占用率
    global ws
    global startTime
    CPU_Uti = get_data_by_get('start=%s&m=sum:switch.CpuUtilization{endpoint=%s}' % (startTime, ip))  # 读数据库
    CPUpercent = max(CPU_Uti.values()) / 100  # 计算最大值
    ws.cell(row=row, column=column, value=CPUpercent)  # 写入表格
    print(time.strftime("%Y-%m-%d %X", time.localtime()),"--%s最大CPU使用率获取完成" % ip)


def getConnectionStat(ip, row, column):
    # 获取最大连接数
    global ws
    global startTime
    ConnectData = get_data_by_get('start=%s&m=sum:switch.ConnectionStat{endpoint=%s}' % (startTime, ip))  # 读数据库
    ConnectDataMax = int(max(ConnectData.values()) / 1000) # 计算最大值
    ws.cell(row=row, column=column, value=ConnectDataMax)  # 写入表格
    print(time.strftime("%Y-%m-%d %X", time.localtime()),"--%s最大连接数获取完成" %ip)


def getFlow(ip,row,column,ifname,metric):
    # 获取最大流量
    global ws
    global startTime
    data1 = get_data_by_get('start=%s&m=sum:%s{ifname=%s,endpoint=%s}'% (startTime, metric, ifname, ip))
    data = int(max(data1.values()) / (1024 * 1024))
    ws.cell(row=row, column=column, value=data)  # 写入表格
    print(time.strftime("%Y-%m-%d %X",time.localtime()),"--%s-%s-%s 获取完成" %(ip, metric, ifname))


if __name__ == "__main__":
    print(time.strftime("%Y-%m-%d %X", time.localtime()), "开始进行数据收集！")
    wb = openpyxl.load_workbook(filename)  # 打开文件
    ws = wb.active  # 获取第一个sheet页
    f = open(configfile, 'r', encoding='utf-8').read()  # 打开配置文件
    confData = yaml.load(f, Loader=yaml.FullLoader)  # 加载yaml配置文件
    wb = openpyxl.load_workbook(filename)  # 打开文件
    ws = wb.active  # 获取第一个sheet页

    for ip in confData.keys():
        for i in range(len(confData[ip])):
            metric = confData[ip][i]["metric"]
            row = confData[ip][i]["row"]
            column = confData[ip][i]["column"]
            if metric == "switch.CpuUtilization":
                getCPU(ip=ip, row=row, column=column)
            elif metric == "switch.ConnectionStat":
                getConnectionStat(ip=ip, row=row, column=column)
            else:
                ifname = confData[ip][i]["ifname"]
                getFlow(ip=ip, row=row, column=column, ifname=ifname, metric=metric)

    # 按日保存表格
    nameTime = time.strftime('-%Y-%m-%d-')
    excelName = 'Net Report' + nameTime + startTime + '.xlsx'
    wb.save(filename=excelName)
    print(time.strftime("%Y-%m-%d %X"), "  excel save complete！！")

文件二：yaml配置文件：cfg.yaml

注意冒号后面带空格、-后面带空格，逗号后面最好也带空格
这样就便于维护了！！！

# 防火墙
192.168.123.123:
- {metric: switch.CpuUtilization, row: 2, column: 3}
- {metric: switch.if.In, ifname: outside, row: 2, column: 6}
- {metric: switch.if.Out, ifname: outside, row: 3, column: 6}
- {metric: switch.if.In, ifname: inside, row: 4, column: 6}
- {metric: switch.if.Out, ifname: inside, row: 5, column: 6}
- {metric: switch.ConnectionStat, row: 2, column: 12}

# 防火墙
192.168.123.456:
- {metric: switch.CpuUtilization, row: 6, column: 3}
- {metric: switch.if.In, ifname: outside, row: 6, column: 6}
- {metric: switch.if.Out, ifname: outside, row: 7, column: 6}
- {metric: switch.if.In, ifname: inside, row: 8, column: 6}
- {metric: switch.if.Out, ifname: inside, row: 9, column: 6}
- {metric: switch.ConnectionStat, row: 6, column: 12}