Welcome, guest | Sign In | My Account | Store | Cart

MS SQL Server log monitor (Python recipe) by Matt Keranen
ActiveState Code (http://code.activestate.com/recipes/286189/)

A script to gather errors, warnings, and failures from Micrsoft SQL Servers and SQL Server Agents. Creates a single HTML file from multiple server logs. Used as a quick daily check to determine if particular servers require administrator intervention.

Python, 112 lines
Download
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
# sqlaerrorlog.py - M.Keranen (mksql@yahoo.com) [06/23/2004]
# ------------------------------------------------------------------------------------------------
# A script to gather error and warning information from MS SQL Server and SQL Server Agent logs,
# from multiple servers, and create a single report in HTML. Currently looks for Errors, Warnings,
# and failed Agent jobs. Used as a start of day / quick check to determine if any servers need
# attention from an administrator.
#
# Can authenticate using wither native SQL logins, or a Windows Domain login. Server names and
# authentication method stored in a text config file.
# -------------------------------------------------------------------------------------------------
# Usage: drivespace.py drive_list_cfg_file | *  (* = prompt for external domain user ID)

import getpass,string,sys,win32com.client
from win32com.client import DispatchBaseClass

cfgfile=sys.path[0]+'/sqlaerrorlog.cfg'

print
pw = getpass.getpass()
uid = getpass.getuser()

htmfile = open('C:\\Documents and Settings\\All Users\\DESKTOP\\Logs\\SQLErrorLog.htm','w')

htmfile.write('<TITLE>SQL Server Error Logs</TITLE>\n')

for line in open(cfgfile,'r').readlines():
	if line[0]<>'#':
		servers = string.split(string.strip(line),',')
		svr=servers[0]
		auth=servers[1]

		print '%s (%s)' % (svr,auth)

		htmfile.write('<TABLE WIDTH=100% CELLPADDING=2 BORDER=2>\n')

		htmfile.write('<TR>\n')
		htmfile.write('<TD BGCOLOR=#D4D4D4 ALIGN=CENTER VALIGN=top WIDTH=10%><B><FONT FACE="ARIAL" SIZE=2>' + svr + '</FONT></B></TD>\n')
		htmfile.write('<TD BGCOLOR=#D4D4D4 ALIGN=CENTER VALIGN=top WIDTH=90%><B><FONT FACE="ARIAL" SIZE=2>SQL Log Entry</FONT></B></TD>\n')
		htmfile.write('</TR>\n')

		sql = win32com.client.Dispatch('SQLDMO.SQLServer')
		
		if auth == 'S':
			sql.LoginSecure = 0
			sql.Connect(svr,uid,pw)
		else:
			sql.LoginSecure = 1
			sql.Connect(svr)
				  
		log = sql.ReadErrorLog()
		for r in range(1,log.Rows):
			lrow = log.GetColumnString(r,1)
			lurow = string.upper(lrow)
			if (('ERROR:' in lurow) and not ('0 ERRORS' in lurow)) or ('FAIL' in lurow) or ('WARN' in lurow):
				while r<log.Rows and log.GetColumnLong(r+1,2) > 0:
					r += 1
					lrow = lrow + log.GetColumnString(r,1)               
				htmfile.write('<TR>\n')
				htmfile.write('<TD VALIGN=top><FONT FACE="COURIER" SIZE=2>%s</FONT></TD>\n' % (svr))
				htmfile.write('<TD VALIGN=top><FONT FACE="COURIER" SIZE=2>%s</FONT></TD>\n' % (lrow))
				htmfile.write('</TR>\n')
		sql = None

		htmfile.write('<TR>\n')
		htmfile.write('<TD BGCOLOR=#E4E4E4 ALIGN=CENTER VALIGN=top WIDTH=10%><B><FONT FACE="ARIAL" SIZE=2>' + svr + '</FONT></B></TD>\n')
		htmfile.write('<TD BGCOLOR=#E4E4E4 ALIGN=CENTER VALIGN=top WIDTH=90%><B><FONT FACE="ARIAL" SIZE=2>Agent Log Entry</FONT></B></TD>\n')
		htmfile.write('</TR>\n')

		adoConn = win32com.client.Dispatch('ADODB.Connection')

		if auth == 'S':
			connect = "Provider=SQLOLEDB.1;Data Source=%s;Initial Catalog=msdb;User ID=%s;Password=%s;" % (svr,uid,pw)
		else:
			connect = "Provider=SQLOLEDB.1;Data Source=%s;Initial Catalog=msdb;Integrated Security=SSPI;" % (svr)

		sql = '''
		SELECT sysjobs.name, 
			hist.message + '(' + CAST(hist.run_date as varchar) + '-' + CAST(hist.run_time as varchar) + ')' as message
			FROM sysjobs, sysjobhistory as hist
			WHERE sysjobs.job_id = hist.job_id
				AND hist.run_status = 0
				AND hist.instance_id = (SELECT MAX(instance_id) FROM sysjobhistory WHERE sysjobhistory.job_id = sysjobs.job_id)
		'''

		adoConn.Open(connect)
		alog = adoConn.Execute(sql)

		while not alog[0].EOF:
			task=alog[0].Fields(0).Value
			entry=alog[0].Fields(1).Value
			htmfile.write('<TR>\n')
			htmfile.write('<TD VALIGN=top><FONT FACE="COURIER" SIZE=2>%s</FONT></TD>\n' % (task))
			htmfile.write('<TD VALIGN=top><FONT FACE="COURIER" SIZE=2>%s</FONT></TD>\n' % (entry))
			htmfile.write('</TR>\n')
			alog[0].MoveNext()
		
		htmfile.write('</TABLE>\n')

htmfile.close()
adoConn = None

xit = raw_input('\nPress Enter...')

"""
Example of sqlaerrorlog.cfg file:

#server_name, S/W = SQL / Windows Authentication
#-----------------------------------------------
SQLSERVER1,S
SQLSERVER2,W

"""
Tags: database