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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

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.



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.



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