Zabbix 监控 RDS SQL Server数据库–1 编写python 脚本

Zabbix Server 4.0.2 Python 3.6.8 Centos 7.8

from aliyunsdkcore import client
from aliyunsdkrds.request.v20140815 import DescribeResourceUsageRequest, DescribeDBInstancePerformanceRequest
from aliyunsdkrds.request.v20140815.DescribeDBInstanceAttributeRequest import DescribeDBInstanceAttributeRequest

import json, sys, datetime
##资源属性
'''RDS的资源属性，cpu(DBInstanceCPU),内存(DBInstanceMemory)，
数据库类型(Engine),版本(EngineVersion)，
实例状态(DBInstanceStatus) 最大IOPS数(MaxIOPS) 最大连接数(MaxConnections)
数据库存储空间(DBInstanceStorage)创建时间等
'''
def GetResourceAttribute(DBInstancedId, Jk_key):
    ###查询rds的资源属性信息
    RequestAttribute = DescribeDBInstanceAttributeRequest()
    ##设置返回数据格式，默认为XML
    RequestAttribute.set_accept_format('json')

    ##指定要查询的那一个RDS 实例
    RequestAttribute.set_DBInstanceId(DBInstancedId)
    ResourceAttributeInfo = clt.do_action_with_exception(RequestAttribute)
    # print(ResourceAttributeInfo)
    Info = (json.loads(ResourceAttributeInfo))['Items']['DBInstanceAttribute'][0][Jk_key]
    print(Info)

##RDS的资源使用情况
'''
#日志占用空间 LogSize
#数据库已使用的磁盘空间 DiskUsed
#数据占用空间 DataSize
#数据库备份大小 BackupSize
'''
def GetResourceUsage(DBInstanceId, Jk_Key):
    # 查询rds的资源属使用
    ResourceUsage = DescribeResourceUsageRequest.DescribeResourceUsageRequest()
    ResourceUsage.set_accept_format('json')
    # 指定要查询的那一个RDS 实例
    ResourceUsage.set_DBInstanceId(DBInstanceId)
    ResourceUsageInfo = clt.do_action_with_exception(ResourceUsage)
    # print(ResourceUsageInfo)
    Info = (json.loads(ResourceUsageInfo))[Jk_Key]
    print(Info)

##查询RDS 实例 的性能信息
def GetPerformance(DBInstanceId,Jk_Key,IndexNum,StartTime,EndTime):
    Performance = DescribeDBInstancePerformanceRequest.DescribeDBInstancePerformanceRequest()
    Performance.set_accept_format('json')
    Performance.set_DBInstanceId(DBInstanceId)
    Performance.set_Key(Jk_Key)
    Performance.set_StartTime(StartTime)
    Performance.set_EndTime(EndTime)
    PerformanceInfo = clt.do_action_with_exception(Performance)
    #print(PerformanceInfo)
    Info = (json.loads(PerformanceInfo))
    Value = Info['PerformanceKeys']['PerformanceKey'][0]['Values']['PerformanceValue'][-1]['Value']
    print(str(Value).split('&')[IndexNum])

if __name__ == '__main__':
    Type = sys.argv[1]
    DBInstanceId = sys.argv[2]
    Jk_Key = sys.argv[3]
    AccessKey_ID = sys.argv[4]
    AccessKey_Secret = sys.argv[5]
    RegionId = 'cn-shanghai'
    # 阿里云返回的数据为UTC时间，因此要转换为东八区时间。其他时区同理。
    UTC_End = datetime.datetime.today() - datetime.timedelta(hours=8)
    UTC_Start = UTC_End - datetime.timedelta(minutes=1)
    StartTime = datetime.datetime.strftime(UTC_Start, '%Y-%m-%dT%H:%MZ')
    EndTime = datetime.datetime.strftime(UTC_End, '%Y-%m-%dT%H:%MZ')
    clt = client.AcsClient(AccessKey_ID, AccessKey_Secret, RegionId)
    if (Type == 'Disk'):
        GetResourceUsage(DBInstanceId, Jk_Key)
    elif (Type == 'Attribute'):
        GetResourceAttribute(DBInstanceId,Jk_Key)
    elif (Type == 'Performance'):
    #SQL Server 引擎监控
        # 平均每秒事务数
        if (Jk_Key == 'SQLServer_Transactions'):
            GetPerformance(DBInstanceId, Jk_Key, 0,StartTime, EndTime)
        #平均每秒SQL语句执行次数
        if (Jk_Key == 'SQLServer_QPS'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        ##缓存命中率
        if (Jk_Key == 'SQLServer_BufferHit'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        ##每秒检查点写入Page数
        if (Jk_Key == 'SQLServer_CheckPoint'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
         #每秒登录次数
        if (Jk_Key == 'SQLServer_Logins'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        #平均每秒全表扫描次数
        if (Jk_Key == 'SQLServer_FullScans'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        ##每秒SQL编译
        if (Jk_Key == 'SQLServer_SQLCompilations'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        ##每秒锁超时次数
        if (Jk_Key == 'SQLServer_LockTimeout'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        #每秒死锁次数
        if (Jk_Key == 'SQLServer_Deadlock'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        ##每秒锁等待次数
        if (Jk_Key == 'SQLServer_LockWaits'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        ##SQL Server 资源监控
        #MSSQL实例占用CPU使用率(占操作系统总数%)
        if (Jk_Key == 'SQLServer_InstanceCPUUsage'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        #MSSQL实例的IOPS（每秒IO请求次数） IOPS（单位：次/秒）
        if (Jk_Key == 'SQLServer_IOPS'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        #当前总连接数
        if (Jk_Key == 'SQLServer_Sessions'):
            GetPerformance(DBInstanceId, Jk_Key, 0, StartTime, EndTime)
        #SQLServer实例平均每秒钟的输入流量。单位为KB
        if (Jk_Key == 'SQLServer_NetworkTraffic_IN'):
            GetPerformance(DBInstanceId, 'SQLServer_NetworkTraffic', 0, StartTime, EndTime)
        #SQLServer实例平均每秒钟的输出流量。单位为KB
        if (Jk_Key == 'SQLServer_NetworkTraffic_OUT'):
            GetPerformance(DBInstanceId, 'SQLServer_NetworkTraffic', 1, StartTime, EndTime)

#########******########

上传到脚本到zabbix服务器，或者zabbix proxy(如果你的服务器中间有代理的话)

创建监控rds sqlserver 的配置文件
cd /etc/zabbix/zabbix_agentd.d

vi ali_rds_sqlserver.conf


重启 zabbix-server 或者 zabbix-proxy
systemctl restart zabbix-proxy
在要监控的阿里云RDS SQLServer 实例下面，添加白名单(zabbix server 或者zabbix proxy的IP)