Neste script Python é criado um relatório HTML com o resultado do processamento de JOBs SQL Server, apresentando-o no Browser.
import pyodbc
import webbrowser
import os
from datetime import datetime
date = datetime.today().strftime('%Y%m%d')
cnxn = pyodbc.connect('Driver={SQL Server}; Server=MyServer; Database=Master; Trusted_Connection=yes;')
cursor = cnxn.cursor()
cursor.execute("SELECT distinct sj.name , sjh.step_id, ISNULL(sjs.step_name, 'Job Status') StepName,sjh.run_date, msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) RunDateAndTime , CASE sjh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In Progress' END RunStatus FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON sjh.job_id = sjs.job_id AND sjh.step_id = sjs.step_id WHERE sj.name like 'JOB_NAME%' and sjh.run_status in (0) and (run_date >= "+date+") and ( sjh.step_id = '0' ) ORDER BY sj.name, step_id ")
#os.unlink("C:\Users\myuser\Documents\lks\_Python\monitoria.html")
# to open/create a new html file in the write mode
f = open('monitoria.html', 'w')
# Write HTML content
f.write("<html>")
f.write("<head>")
f.write("<style>")
f.write("table, td, th {")
f.write ("border: 1px solid black;")
f.write("}")
f.write("")
f.write("table {")
f.write(" border-collapse: collapse;")
f.write(" width: 70%;")
f.write(" margin-left: auto;")
f.write(" margin-right: auto;")
f.write("}")
f.write("")
f.write("td {")
f.write(" text-align: center;")
f.write("}")
f.write("</style>")
f.write("<title>Monitoria</title>")
f.write("</head>")
f.write("<body>")
f.write("<h1>Monitoria JOBs ETL</h1>")
f.write("<table>")
while 1:
row = cursor.fetchone()
if not row:
break
f.write("<tr>")
f.write("<td>"+row.name+"</td>")
f.write("<td>"+str(row.step_id)+"</td>")
f.write("<td>"+row.RunStatus+"</td>")
f.write("<td>"+str(row.RunDateAndTime)+"</td>")
f.write("</tr><br>")
cnxn.close()
f.write("</table>")
f.write("</body>")
f.write("</html>