Monitor Event Viewer on Multiple SQL Servers

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: [email protected]
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.

Latest Articles