Monitor System Event Logs

Jonathan Kehayias, October 28, 2008

In addition to the SQL Server Error Logs, the Windows System Logs are also important to monitor. Errors outside of SQL Server at the Operating System level can have an impact on the availability, stability, and performance of SQL Server as a service. Driver conflicts, controller failures, as well as system restart information are logged in the Windows System Logs, and can provide additional information to a DBA as to potential or real problems with the server itself. The System Event Logs can be read using a VBScript that calls into the Windows Management Instrumentation (WMI) interface. Just as with the SQL Server Error Logs, the System Event Logs contain information events along with error events. Filtering can be done so that information events are ignored and only errors are sent to a DBA for notifications.

WMI Query Local System Logs.vbs Script

 
Const CONVERT_TO_LOCAL_TIME = True
 
Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
Set dtmEndDate = CreateObject("WbemScripting.SWbemDateTime")
RecCount = 0
 
'Note: change the email ID to your email ID
NotificationEmail="""SQL Monitor"" <SQLMonitor@domain.com>"
SMTPServer = "mail.domain.com"
 
dtmStartDate.SetVarDate Now, CONVERT_TO_LOCAL_TIME
 
DateToCheck = dtmStartDate.GetVarDate(CONVERT_TO_LOCAL_TIME)
 
DateToCheck1 =  DateAdd("n",-10,DateToCheck) 
 
dtmEndDate.SetVarDate DateToCheck1, CONVERT_TO_LOCAL_TIME
 
strComputer = "."
 
GetEvents(strComputer)
 
 
Function GetEvents(strComputer)
 
	Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
	Set colLoggedEvents = objWMIService.ExecQuery ( _
				"Select * " _
				& "from Win32_NTLogEvent " _
				& "Where ((Logfile = 'System' and Type != 'Information')" _
				& "   OR (Logfile = 'Application' and Type != 'Information')) " _
				& "  and TimeWritten <= '"& dtmStartDate & "' " _
				& "  and TimeWritten > '" & dtmEndDate & "'") 
 
	For Each objEvent in colLoggedEvents
		'SendMail(objEvent)
		WriteOutput(objEvent)
	Next
 
End Function
 
 
Function WMIDateStringToDate(dtmEventDate)
 
    WMIDateStringToDate = CDate(Mid(dtmEventDate, 5, 2) & "/" & _
        Mid(dtmEventDate, 7, 2) & "/" & Left(dtmEventDate, 4) _
            & " " & Mid (dtmEventDate, 9, 2) & ":" & _
                Mid(dtmEventDate, 11, 2) & ":" & Mid(dtmEventDate, _
                    13, 2))
 
End Function
 
Function SendMail(objEvent)
 
	dtmEventDate = objEvent.TimeWritten
	strTimeWritten = WMIDateStringToDate(dtmEventDate)
 
	Set objMessage = CreateObject("CDO.Message") 
 
	objMessage.Subject = "Test" 
	objMessage.Sender = NotificationEmail
	objMessage.To = NotificationEmail
	objMessage.TextBody = "HOST:   "  & objEvent.ComputerName & vbCrLf _
				& "SID:    " & objEvent.Type & vbCrLf _
				& "SOURCE: " & objEvent.SourceName & vbCrLf _
				& "COMP:   Windows Event Log" & vbCrLf _
				& "MSG:    " & objEvent.Message & vbCrLf _
				& "TS:     " & strTimeWritten 
 
	'The line below shows how to send a webpage from a remote site 
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
 
	'Name or IP of Remote SMTP Server 
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
 
	'Server port (typically 25) 
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
		objMessage.Configuration.Fields.Update 
 
	objMessage.Send
 
End Function
 
Function WriteOutput(objEvent)
 
	dtmEventDate = objEvent.TimeWritten
	strTimeWritten = WMIDateStringToDate(dtmEventDate)
 
	WScript.Echo "HOST:   "  & objEvent.ComputerName & vbCrLf _
				& "SID:    " & objEvent.Type & vbCrLf _
				& "SOURCE: " & objEvent.SourceName & vbCrLf _
				& "COMP:   Windows Event Log" & vbCrLf _
				& "MSG:    " & objEvent.Message & vbCrLf _
				& "TS:     " & strTimeWritten 
	WScript.Echo ""
 
End Function
   

Return to Top

WMI Query ServerList System Logs.vbs Script

 
on error resume next
 
Const CONVERT_TO_LOCAL_TIME = True
 
Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
    dtmStartDate.SetVarDate Now, CONVERT_TO_LOCAL_TIME
 
Set dtmEndDate = CreateObject("WbemScripting.SWbemDateTime")
    dtmEndDate.SetVarDate DateAdd("n",-10, Now), CONVERT_TO_LOCAL_TIME
 
'Note: change the email ID to your email ID
NotificationEmail="""SQL Monitor"" <SQLMonitor@domain.com>"
SMTPServer = "mail.domain.com"
 
 
	Set iFSO = CreateObject("Scripting.FilesyStemObject")
 
	InputFile="Serverlist.txt"
 
	Set ifile = iFSO.OpenTextFile(inputfile)
 
	Do until ifile.AtEndOfLine
	
		strComputer = ifile.ReadLine
		WScript.Echo strComputer
		GetEvents(strComputer)
	Loop
 
 
Function GetEvents(strComputer)
 
	Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
	Set colLoggedEvents = objWMIService.ExecQuery ( _
				"Select * " _
				& "from Win32_NTLogEvent " _
				& "Where ((Logfile = 'System' and Type != 'Information')" _
				& "   OR (Logfile = 'Application' and Type != 'Information')) " _
				& "  and TimeWritten <= '"& dtmStartDate & "' " _
				& "  and TimeWritten > '" & dtmEndDate & "'") 
 
	For Each objEvent in colLoggedEvents
		'SendMail(objEvent)
		WriteOutput(objEvent)
	Next
 
End Function
 
 
Function WMIDateStringToDate(dtmEventDate)
 
    WMIDateStringToDate = CDate(Mid(dtmEventDate, 5, 2) & "/" & _
        Mid(dtmEventDate, 7, 2) & "/" & Left(dtmEventDate, 4) _
            & " " & Mid (dtmEventDate, 9, 2) & ":" & _
                Mid(dtmEventDate, 11, 2) & ":" & Mid(dtmEventDate, _
                    13, 2))
 
End Function
 
Function SendMail(objEvent)
 
	dtmEventDate = objEvent.TimeWritten
	strTimeWritten = WMIDateStringToDate(dtmEventDate)
 
	Set objMessage = CreateObject("CDO.Message") 
 
	objMessage.Subject = "Test" 
	objMessage.Sender = NotificationEmail
	objMessage.To = NotificationEmail
	objMessage.TextBody = "HOST:   "  & objEvent.ComputerName & vbCrLf _
				& "SID:    " & objEvent.Type & vbCrLf _
				& "SOURCE: " & objEvent.SourceName & vbCrLf _
				& "COMP:   Windows Event Log" & vbCrLf _
				& "MSG:    " & objEvent.Message & vbCrLf _
				& "TS:     " & strTimeWritten 
 
	'The line below shows how to send a webpage from a remote site 
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
 
	'Name or IP of Remote SMTP Server 
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
 
	'Server port (typically 25) 
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
		objMessage.Configuration.Fields.Update 
 
	objMessage.Send
 
End Function
 
Function WriteOutput(objEvent)
 
	dtmEventDate = objEvent.TimeWritten
	strTimeWritten = WMIDateStringToDate(dtmEventDate)
 
	WScript.Echo "HOST:   "  & objEvent.ComputerName & vbCrLf _
				& "SID:    " & objEvent.Type & vbCrLf _
				& "SOURCE: " & objEvent.SourceName & vbCrLf _
				& "COMP:   Windows Event Log" & vbCrLf _
				& "MSG:    " & objEvent.Message & vbCrLf _
				& "TS:     " & strTimeWritten 
	WScript.Echo ""
 
End Function
 

Return to Top

ServerList.txt Sample

ServerName
ServerName2
ServerName3

Return to Top


This is article is part of a series on Automating Common DBA tasks in SQL Server. To see the full series see: Automating Common DBA Tasks

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Oct 28, 2008