#!/usr/#!/usr/bin/python3
#coding: utf-8
import pymssql
import argparse
import inspect
import re
import uuid
import json
########################登入##########################
class Main(object):
def __init__(self):
parser = argparse.ArgumentParser()
parser.add_argument('--server')
parser.add_argument('--user')
parser.add_argument('--password')
subparsers = parser.add_subparsers()
for name in dir(self):
if not name.startswith("_"):
p = subparsers.add_parser(name)
method = getattr(self, name)
argnames = inspect.getargspec(method).args[1:]
for argname in argnames:
p.add_argument(argname)
p.set_defaults(func=method, argnames=argnames)
self.args = parser.parse_args()
def db_connect(self):
a = self.args
server = a.server
user = a.user
password = a.password
self.db = pymssql.connect(server=server, user=user, password=password)
self.cur = self.db.cursor()
#########################常规#########################
def Job_Task(self):
'''展示作业执行状况'''
sql = """SELECT
j.job_id AS '作业唯一标识符',
j.name AS '作业名称',
j.enabled AS '是否启用',
j.description AS '作业描述',
j.date_created AS '作业创建日期',
j.date_modified AS '作业最后修改日期',
s.name AS '作业运行服务器名称',
js.step_id AS '作业起始步骤',
js.step_name AS '步骤名称',
js.subsystem AS '步骤子系统',
js.command AS '步骤命令',
js.on_success_action AS '执行成功后操作',
js.on_fail_action AS '执行失败后操作',
js.retry_attempts AS '失败时的重试次数',
js.retry_interval AS '重试间的等待时间(分钟)',
jh.run_date AS '最后运行日期',
jh.run_duration AS '运行持续时间',
jh.run_status AS '运行状态'
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.sys.servers AS s ON j.originating_server_id = s.server_id
LEFT JOIN msdb.dbo.sysjobsteps AS js ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.sysjobhistory AS jh ON j.job_id = jh.job_id"""
self.cur.execute(sql)
SQLPut = self.cur.fetchall()
data = [["%s"% (str(item[0])),
item[1],
item[2],
item[3],
item[4].strftime('%Y-%m-%d'),
item[5].strftime('%Y-%m-%d'),
item[6],
item[7],
item[8],
item[9],
item[10],
item[11],
item[12],
item[13],
item[14],
item[15],
item[16]]
for item in SQLPut]
title = data.insert(0, ['UUID',
'作业名称',
'是否启用',
'作业描述',
'作业创建日期',
'作业最后修改日期',
'作业运行服务器名称',
'作业起始步骤',
'步骤名称',
'步骤子系统',
'步骤命令',
'执行成功后操作',
'执行失败后操作',
'失败时的重试次数',
'重试间的等待时间(分钟)',
'最后运行日期',
'运行持续时间',
'运行状态'])
print (json.dumps(data, sort_keys=True,ensure_ascii=False))
##################################################
def db_close(self):
self.db.close()
def __call__(self):
try:
a = self.args
callargs = [getattr(a, name) for name in a.argnames]
self.db_connect()
try:
return self.args.func(*callargs)
finally:
self.db_close()
except Exception as err:
print("Connection error")
print(str(err))
##################################################
if __name__ == "__main__":
main = Main()
main()
2.创建相关的外部监查脚本调用作业任务监控脚本 
3.创建监控项 键值：mssqljob[{HOST.CONN},{$ USER},{ $PASSWORD},Job_Task] 
4.创建展示面板 
5.数据展示效果 