WHEN 4 THEN cast('00:' + Left(right(run_duration,4),2) +':' + right(run_duration,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(run_duration,5),1) +':' + Left(right(run_duration,4),2) +':' + right(run_duration,2) as char (8)) WHEN 6 THEN cast(Left(right(run_duration,6),2) +':' + Left(right(run_duration,4),2) +':' + right(run_duration,2) as char (8)) END as 'duration' from msdb..sysjobs sj inner join sysjobhistory sjh on sj.job_id=sjh.job_id where sjh.step_id = 0 and sjh.run_date >= '%s' and sj.name not in ('syspolicy_purge_history', 'RefreshADGroupInfo') order by runDate, startTime, jobName, duration """ % yesterday for server in serverList: cn = pyodbc.connect( 'DRIVER={SQL Server};SERVER=%s;DATABASE=msdb;Trusted_Connection=yes' % server.strip()) cursor = cn.cursor() cursor.execute(sql) rows = cursor.fetchall() toAddrs = ['*****@*****.**'] subj = '%s job status since yesterday' % server.strip() msg = """\ %s """ % cursorResultsPrettyPrint(cursor, rows, True) sendEmail(toAddrs, subj, msg)
+ Left(right(run_duration,3),1) +':' + right(run_duration,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(run_duration,4),2) +':' + right(run_duration,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(run_duration,5),1) +':' + Left(right(run_duration,4),2) +':' + right(run_duration,2) as char (8)) WHEN 6 THEN cast(Left(right(run_duration,6),2) +':' + Left(right(run_duration,4),2) +':' + right(run_duration,2) as char (8)) END as 'duration' from msdb..sysjobs sj inner join sysjobhistory sjh on sj.job_id=sjh.job_id where sjh.step_id = 0 and sjh.run_date >= '%s' and sj.name not in ('syspolicy_purge_history', 'RefreshADGroupInfo') order by runDate, startTime, jobName, duration """ % yesterday for server in serverList: cn = pyodbc.connect('DRIVER={SQL Server};SERVER=%s;DATABASE=msdb;Trusted_Connection=yes' % server.strip()) cursor = cn.cursor() cursor.execute(sql) rows = cursor.fetchall() toAddrs = ['*****@*****.**'] subj = '%s job status since yesterday' % server.strip() msg = """\ %s """ % cursorResultsPrettyPrint(cursor, rows, True) sendEmail(toAddrs, subj, msg)
import argparse from mssqlUtility import getVersionNumber from allPlatUtility import sendEmail parser = argparse.ArgumentParser(description='Check SQL Server version number') parser.add_argument('-f', '--fileInput', help='The presence of this parameter indicates server names come from a file', action='store_true', default=False, dest='fileInput') parser.add_argument('-i', '--input', help='When -f is present, please provide the name of the file that contains a list of servers, each on its own line. Otherwise, please type a list of servers, separated by comma.', required=True, dest='iInput') argList = parser.parse_args() standardVersion = '10.50.2789.0' emailMsg = '' if argList.fileInput: text_file = open("%s" % argList.iInput, "r") serverList = text_file.readlines() else: serverList = argList.iInput.split(',') for server in serverList: version = getVersionNumber(server.strip()) if version != standardVersion: if version is None: emailMsg = emailMsg + server.strip() + ' is down or does not exist\n' else: emailMsg = emailMsg + server.strip() + ' is version ' + version + ' not ' + standardVersion + ', which is our standard\n' if emailMsg != '': toAddress = ['*****@*****.**'] subject = 'Server connectivity and version test' sendEmail(toAddress, subject, emailMsg)
'--input', help= 'When -f is present, please provide the name of the file that contains a list of servers, each on its own line. Otherwise, please type a list of servers, separated by comma.', required=True, dest='iInput') argList = parser.parse_args() standardVersion = '10.50.2789.0' emailMsg = '' if argList.fileInput: text_file = open("%s" % argList.iInput, "r") serverList = text_file.readlines() else: serverList = argList.iInput.split(',') for server in serverList: version = getVersionNumber(server.strip()) if version != standardVersion: if version is None: emailMsg = emailMsg + server.strip( ) + ' is down or does not exist\n' else: emailMsg = emailMsg + server.strip( ) + ' is version ' + version + ' not ' + standardVersion + ', which is our standard\n' if emailMsg != '': toAddress = ['*****@*****.**'] subject = 'Server connectivity and version test' sendEmail(toAddress, subject, emailMsg)