> ## Documentation Index
> Fetch the complete documentation index at: https://doc.lwops.cn/llms.txt
> Use this file to discover all available pages before exploring further.
# Microsoft SQL监控作业任务
# 1.获取作业任务的SQL
```plain theme={null}
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 jLEFT JOIN msdb.sys.servers AS s ON j.originating_server_id = s.server_idLEFT JOIN msdb.dbo.sysjobsteps AS js ON j.job_id = js.job_idLEFT JOIN msdb.dbo.sysjobhistory AS jh ON j.job_id = jh.job_id;
```
# 2.监控脚本
```plain theme={null}
#!/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()
```
# 3.监控效果
```plain theme={null}
[["UUID", "作业名称", "是否启用", "作业描述", "作业创建日期", "作业最后修改日期", "作业运行服务器名称", "作业起始步骤", "步骤名称", "步骤子系统", "步骤命令", "执行成功后操作", "执行失败后操作", "失败时的重试次数", "重试间的等待时间(分钟)", "最后运行日期", "运行持续时间", "运行状态"], ["c8a54c35-dba3-46c3-a3e8-b24f9abf8108", "Backup Database", 1, "每天定时备份数据库", "2023-09-12", "2023-09-12", "localhost", 1, "a", "TSQL", "PRINT 'Hello, World!'", 1, 2, 0, 0, null, null], ["62e703c0-5acd-42b9-aa37-dc8033f5fbac", "MyJob", 1, "My simple job", "2023-09-12", "2023-09-12", "localhost", 1, "MyJobStep", "TSQL", "PRINT 'Hello, World!'", 1, 2, 0, 0, null, null]]
```
# 4.放置脚本及创建监控项
1.将脚本mssqlJob.py放置于脚本目录，并赋权 
2.创建相关的外部监查脚本调用作业任务监控脚本 
3.创建监控项 键值：mssqljob\[\{HOST.CONN},\{\$ USER},\{ \$PASSWORD},Job\_Task] 
4.创建展示面板 
5.数据展示效果 