默认的各种exporter有时候用起来都不够顺手，特殊情况下，我们还可以自己写sql然后通过python包装下，将metrics发送到pushgateway，进而上报到Prometheus里面。
下面例子中，演示的是将datasize和logsize指标采集上报。其它指标的采集方法类似。
采集脚本如下：
代码语言：python代码运行次数：0复制# !/usr/bin/env python
# -*-coding:utf-8 -*-
import pymssql
from prometheus_client import CollectorRegistry, Gauge, push_to_gateway
def get_db_list(host, port, db, user, passwd):
conn = pymssql.connect(
host=host, port=port, user=user, password=passwd, database=db, charset="utf8"
)
cursor = conn.cursor()
sql = """select database_id,name,state from sys.databases where [state] not in(1,2,3,4,6) and name not in ('master','model','tempdb','msdb') ORDER BY name asc"""
cursor.execute(sql)
res = cursor.fetchall()
db_list = []
for i in res:
db_list.append(i[1])
return db_list
def push_metrics(host, port, db, user, passwd, db_list):
for db in db_list:
conn = pymssql.connect(
host=host,
port=port,
user=user,
password=passwd,
database='master',
charset="utf8",
)
cursor = conn.cursor()
detail_sql = "USE [" str(db) "];" """
SELECT
DB_NAME() AS [Database Name]
,SUM(size * 8.0 / 1024) AS [Total_Size]
,SUM(CASE WHEN type_desc = 'ROWS' THEN size * 8.0 / 1024 ELSE 0 END) AS [Data Size]
,SUM(CASE WHEN type_desc = 'LOG' THEN size * 8.0 / 1024 ELSE 0 END) AS [Log Size]
,@@SERVERNAME as instance
FROM sys.master_files
WHERE type in (0,1) AND state = 0 AND database_id = DB_ID('""" str(db) "');"
try:
cursor.execute(detail_sql)
except:
continue
res = cursor.fetchall()
for row in res:
Database_Name = str(row[0])
Total_Size = str(row[1])
Data_Size = str(row[2])
Log_Size = str(row[3])
instance = str(row[4])
labels = ["Metrics"]
registry = CollectorRegistry()
rds_metrics = Gauge("Data_Size", "MSSQL自定义指标", labels, registry=registry)
rds_metrics.labels("Data_Size").set(Data_Size)
rds_metrics = Gauge("Log_Size", "MSSQL自定义指标", labels, registry=registry)
rds_metrics.labels("Log_Size").set(Log_Size)
push_to_gateway(
gateway='http://192.168.100.200:9091',
job="MSSQLBasicMetrics",
grouping_key={"instance": instance,"Database_Name":Database_Name},
registry=registry,
timeout=5,
)
print(f"实例: {instance} , 相关metrics已推送到pushgateway")
if __name__ == "__main__":
port = "1433"
db = "master"
user = "abcd"
passwd = "1234"
host_list = ["192.168.100.11","192.168.100.12","192.168.100.13"]
for host in host_list:
db_list = get_db_list(host, port, db, user, passwd)
push_metrics(host, port, db, user, passwd, db_list)
此外，还可以把指标采集到后写到关系型数据库里面，便于关联分析。这里不再赘述。
