Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 11, 2004

Monitor Event Viewer on Multiple SQL Servers

By Muthusamy Anantha Kumar aka The MAK

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.



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
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set oFSO = CreateObject("Scripting.FilesyStemObject")
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
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
if isnull(objEvent.User ) = true then
myuser ="N/A"
end if
If  isnull(objEvent.Message) = true then
mymessage =" "
mymessage =replace(objEvent.Message,"'"," ")
end if
'msgbox string1
ofile.writeline objEvent.Logfile & ",
  " & objEvent.Category & ",
  " & objEvent.ComputerName & ",
  " & mymessage & ",
  " & objEvent.SourceName  & ",
  " & mydate & ",
  " & objEvent.Type  & "," & myuser
Msgbox "Event Log Monitor Completed Successfully"
Function Convert2DMTFDate(dDate, nTimeZone)
Dim sTemp
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
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.

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.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.