Monitor Event Viewer on Multiple SQL Servers

February 11, 2004

In the typical IT department, an un-avoidable task is to monitor the Event Log on all of the servers. It is tedious to go through all the logs one by one, on a daily basis. In this article, the methods presented will help in monitoring the Event Viewer on many servers from a single place. In addition, I am going to discuss three different ways to monitor Event Log on a list of servers and store the output either in a .CSV file or on a database table. These methods fetch all of those errors and warnings that occurred in the last 24 hours.

Method 1:

This method checks the Event Log for Errors and Warnings on all of the servers listed in the text file and create a .csv output file.

Let's say we have 3 servers and need to monitor the Event log on all of those servers.

Step1: Create a folder C:\EventLog and create a text file c:\EventLog\Serverlist.txt with a list of server names.

Example:

SQL2K
ETL
YUKON

Step2: Copy and paste the code below into c:\EventLog\monitoreventlogtocsv.vbs.

'Objective: Find Errors and Warnings in Event Log on all the listed servers and write the output to a .csv file
'Author: MAK
'Contact: mak_999@yahoo.com
On error resume next
Const CONVERT_TO_LOCAL_TIME = True
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set oFSO = CreateObject("Scripting.FilesyStemObject")
InputFile="c:\EventLog\Serverlist.txt"
Outputfile="c:\EventLog\EventlogOutput_" + cstr(Month(now()))+"_"+cstr(day(now()))+".csv"
Set ofile = ofso.createTextFile(OutputFile, True)
Set ifile = iFSO.OpenTextFile(inputfile)  
Const MBCONVERSION= 1048576  
ofile.writeline "LogType,Category,ComputerName,Message,SourceName,TimeWritten,Type,User"
Do until ifile.AtEndOfLine
strComputer = ifile.ReadLine
Set objService =GetObject("winmgmts:{impersonationLevel=impersonate,"& "(Security)}!root\cimv2")
Set objWMICol = objService.ExecQuery("SELECT Currenttimezone FROM Win32_ComputerSystem")
For Each objComputer In objWMICol
nTimeZone = objComputer.Currenttimezone
Next
string1=""
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & 
  strComputer & "\root\cimv2")
Query = "SELECT * from Win32_NTLogEvent where 
  (Logfile = 'Application' or Logfile = 'Security' or Logfile = 'System')  and 
  (Type<>'Information' and  TimeWritten >= '"+ Convert2DMTFDate(Now, nTimeZone ) +"') and 
  (Type = 'Warning' or Type = 'Error')"
'msgbox Query
Set colRetrievedEvents = objWMIService.ExecQuery (Query)

For Each objEvent in colRetrievedEvents
Mydate="'"+cstr(objEvent.TimeWritten)+"'" 
if isnull(objEvent.User ) = true then
myuser ="N/A"
else
myuser=objEvent.User 
end if
If  isnull(objEvent.Message) = true then
mymessage =" "
else
mymessage =replace(objEvent.Message,"'"," ")
end if
'msgbox string1
ofile.writeline objEvent.Logfile & ",
  " & objEvent.Category & ",
  " & objEvent.ComputerName & ",
  " & mymessage & ",
  " & objEvent.SourceName  & ",
  " & mydate & ",
  " & objEvent.Type  & "," & myuser
Next 
Loop
Msgbox "Event Log Monitor Completed Successfully"
 
Function Convert2DMTFDate(dDate, nTimeZone)
Dim sTemp
dDate=dDate-1
sTemp = cstr(year(dDate))+right("00"+cstr(month(dDate)),2)+right("00"+cstr(day(dDate)),2)
sTemp = sTemp & cstr(hour(dDate))+right("00"+cstr(minute(dDate)),2)
If nTimeZone>=0 Then
sTemp = sTemp & "00.000000" & "+" & nTimeZone
Else
sTemp = sTemp & "00.000000" & nTimeZone
End If
Convert2DMTFDate = sTemp
End Function

Step3: Execute c:\EventLog\monitoreventlogtocsv.vbs.

When we execute this VB script, it will create a time stamped, comma separated value file on c:\Evenlog\EventlogOutput_1_22.csv that will contain details similar to those below.

LogType,Category,ComputerName,Message,SourceName,TimeWritten,Type,User
Application,0,SQL2K, ,Microsoft Internet Explorer,'20040122190940.000000-300',error,N/A
System,0,SQL2K,DCOM Can't communicate ,DCOM,'20040122223203.000000-300',error,AMERICAS\Administrator
System,0,SQL2K,DCOM Can't communicate ,DCOM,'20040122223142.000000-300',error,AMERICAS\Administrator
Application,0,YUKON,Windows cannot determine the user or computer name. Return value (1722).,Userenv,
  '20040122220223.000000-300',error,NT AUTHORITY\SYSTEM

Step4: Click OK on the Message box.