SQL Server Log Monitor

# 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
import string
import sys
import 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

"""