Monitor Event Viewer on Multiple SQL Servers

Wednesday Feb 11th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

Muthusamy Anantha Kumar discusses three methods to monitor SQL Server Event Log on a list of servers and store the output either in a .CSV file or on a database table.



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.

Method 2:

This method checks the Event Log for Errors and Warnings on all of the servers listed in a SQL Server table and stores the output in a SQL Server table.

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

Step1: Create database, table, Login and users in SQL Server.

Create Database EventLogMonitor
go
use EventLogMonitor
go
Create Table Servers
  (Id int identity(1,1), ServerName varchar(128))
go
Insert into Servers(Servername)  select 'SQL2K'
Insert into Servers(Servername)  select 'ETL'
Insert into Servers(Servername)  select 'YUKON'
Go
Create table eventlog (LogType varchar(100),
  Category varchar(200),ComputerName varchar(200),

Message varchar(2000), SourceName varchar(200),
  TimeWritten varchar(200),Type varchar(200),

AUser varchar(200), 
  MonitorDate datetime default getdate())

go
use master
go 
sp_addlogin 'EventLoguser','Event','EventLogMonitor'
go
use EventLogMonitor
go
sp_adduser 'EventLoguser'
go
sp_addrolemember 'db_datawriter', 'EventLoguser'
go
sp_addrolemember 'db_datareader', 'EventLoguser'
go
Use EventLogMonitor

Go

Create table logtable (id int identity(1,1), 
  notes varchar(1000), 
  date datetime default getdate())

go

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

'Objective: Find Errors and Warnings in Event Log on all the listed servers and write the output to a SQL Table
'Author: MAK
'Contact: mak_999@yahoo.com
on error resume next
Const CONVERT_TO_LOCAL_TIME = True
Set AdCn = CreateObject("ADODB.Connection")
AdCn.commandtimeout =36000
Set AdRec = CreateObject("ADODB.Recordset")
Set AdRec1 = CreateObject("ADODB.Recordset")
AdCn.Open = "Provider=SQLOLEDB.1;Data Source=SQL2k\instance1;Initial Catalog=EventLogMonitor;user id = 
  'EventLoguser';password='Event' "
SQL1 = "Select ltrim(rtrim(Servername)) as servername from Servers order by ID Asc"
AdRec1.Open SQL1, AdCn,1,1
ErrorSQL="insert into event_logtable(notes) values ('Event Log Monitoring Started')"
AdRec.Open ErrorSQL, AdCn,1,1
while not Adrec1.EOF 
strComputer= Adrec1("ServerName")
'Change 24 hours to any hours depending on your environment and requirement.
iDuration = 24
dtmNow = Now
dtmStartDate = UTC(Dateadd("h", -1 * iDuration, dtmNow),strComputer)
dtmEndDate = UTC(dtmNow,strComputer)
ErrorSQL="insert into event_logtable(notes) values ('"+ strcomputer+ ": Event Log Monitoring Started')"
AdRec.Open ErrorSQL, AdCn,1,1
Set colRetrievedEvents=nothing
 
Set colRetrievedEvents= GetObject("winmgmts:{impersonationLevel=impersonate}\\"+strcomputer+"\root\cimv2")
  .ExecQuery("SELECT * FROM Win32_NTLogEvent 
  WHERE Logfile = 'Application'  and 
  ( Type ='Warning' or Type='Error' ) and TimeWritten >='"+ dtmStartDate +"'")
if err.number <>0 then
ErrorSQL="insert into event_logtable(notes) values ('" + strcomputer+  ": Error " + err.description+" ')"
AdRec.Open ErrorSQL, AdCn,1,1
else
 
For Each objEvent in colRetrievedEvents
 string1=""
 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
 
 string1 = "insert into eventlog (LogType,Category,ComputerName,Message,SourceName,TimeWritten,Type,AUser) 
   values ('"+ objEvent.Logfile + "',
   '" + Cstr(objEvent.Category)+ "',
   '" + objEvent.ComputerName + "',
   '" + mymessage  + "',
   '" + objEvent.SourceName + "',
   convert(datetime,left(" + Mydate +" ,8) + ' '+substring("+ Mydate+ ",9,2) + ':
   '+substring(" + Mydate + " ,11,2) ,112)" +" ,'" + objEvent.Type + "','" + myuser+ "')"
 AdRec.Open string1, AdCn,1,1
 Next 
end if
err.clear
Set colRetrievedEvents=nothing
 
Set colRetrievedEvents= GetObject("winmgmts:{impersonationLevel=impersonate}\\
  "+strcomputer+"\root\cimv2").ExecQuery("SELECT * 
  FROM Win32_NTLogEvent 
  WHERE Logfile = 'System'  and 
  ( Type ='Warning' or Type='Error' ) and 
  TimeWritten >='"+ dtmStartDate +"'")
if err.number <>0 then
ErrorSQL="insert into event_logtable(notes) values ('" + strcomputer+  ": Error " + err.description+" ')"
AdRec.Open ErrorSQL, AdCn,1,1
else
 
For Each objEvent in colRetrievedEvents
 string1=""
 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
 
 string1 = "insert into eventlog 
   (LogType,Category,ComputerName,Message,SourceName,TimeWritten,Type,AUser) values 
   ('"+ objEvent.Logfile + "',
   '" + Cstr(objEvent.Category)+ "',
   '" + objEvent.ComputerName + "',
   '" + mymessage  + "',
   '" + objEvent.SourceName + "',
   convert(datetime,left(" + Mydate +" ,8) + ' '+substring
   ("+ Mydate+ ",9,2) + ':'+substring(" + Mydate + " ,11,2) ,112)" +" ,
   '" + objEvent.Type + "','" + myuser+ "')"
 AdRec.Open string1, AdCn,1,1
 Next 
 
end if
 
   err.clear
 
   ErrorSQL="insert into event_logtable(notes) values ('" + strComputer + ":  monitoring Completed')"
   AdRec.Open ErrorSQL, AdCn,1,1
  Adrec1.movenext
  Wend
 
ErrorSQL="insert into event_logtable(notes) values ('Event Log Monitoring Completed')"
AdRec.Open ErrorSQL, AdCn,1,1
AdCn.close
 
Function UTC(dtmDate, strComputer2)
'Function to convert the date/time to UTC format.
 
     Dim objSWbemServices
     Dim colTimeZone
     Dim objTimeZone
     Dim strBias
     Dim dtmCurrentDate
     Dim dtmTargetDate
     Dim dtmMonth
     Dim dtmDay
     Dim dtmHour
     Dim dtmMinute
     Dim dtmSecond
 
     Set objSWbemServices = GetObject("winmgmts:"   & 
	   "{impersonationLevel=impersonate}!\\" & strComputer2 & "\root\cimv2")
     Set colTimeZone = objSWbemServices.ExecQuery          ("SELECT * FROM Win32_TimeZone")
     For Each objTimeZone in colTimeZone
         strBias = objTimeZone.Bias
     Next
 
     dtmCurrentDate = dtmDate
     dtmTargetDate = Year(dtmCurrentDate)
      dtmMonth = Month(dtmCurrentDate)
     If Len(dtmMonth) = 1 Then
         dtmMonth = "0" & dtmMonth
     End If
      dtmTargetDate = dtmTargetDate & dtmMonth
      dtmDay = Day(dtmCurrentDate)
     If Len(dtmDay) = 1 Then
         dtmDay = "0" & dtmDay
     End If
      dtmTargetDate = dtmTargetDate & dtmDay
      dtmHour = Hour(dtmCurrentDate)
     If Len(dtmHour) = 1 Then
         dtmHour = "0" & dtmHour
     End If
      dtmTargetDate = dtmTargetDate & dtmHour
      dtmMinute = Minute(dtmCurrentDate)
     If Len(dtmMinute) = 1 Then
         dtmMinute = "0" & dtmMinute
     End If
      dtmTargetDate = dtmTargetDate & dtmMinute
      dtmSecond = Second(dtmCurrentDate)
     If Len(dtmSecond) = 1 Then
         dtmSecond = "0" & dtmSecond
     End If
      dtmTargetDate = dtmTargetDate & dtmSecond
      dtmTargetDate = dtmTargetDate & ".000000"
     dtmTargetDate = dtmTargetDate & Cstr(strBias)
     UTC = dtmTargetDate
End Function
 

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

When we execute this VB script, the output is stored to a SQL Server table. The results look similar to the image below.

In addition, Log information will be stored in the logtable similar to the image below.

You can also create a small .asp page (see attachment and rename the files from .txt to .asp) to display the status of Event log monitor status of all of the servers on a web page. The ASP page will look like below.

Event Log Monitor

Logtype

Category

Server

Message

SourceName

TimeWritten

Type

User

TimeStamp

System

0

SQL2K

DCOM was unable to communicate with the computer YUKON using any of the configured protocols.

DCOM

Jan 22 2004 10:32PM

error

AMERICAS\Administrator

1/22/2004 11:26:04 PM

System

0

SQL2K

DCOM was unable to communicate with the computer ETL using any of the configured protocols.

DCOM

Jan 22 2004 10:26PM

error

AMERICAS\Administrator

1/22/2004 11:26:04 PM

The Log table will appear in the asp page and be similar to the one below.

Log Table

Description

TimeStamp

Event Log Monitoring Started

1/22/2004 11:25:59 PM

ETL: The remote server machine does not exist or is unavailable

1/22/2004 11:26:05 PM

YUKON: ErrorPermission denied

1/22/2004 11:26:05 PM

Event Log Monitoring Completed

1/22/2004 11:26:06 PM

Conclusion:

By scheduling one of the above VB Scripts as a batch job, we can monitor the Event Log on all of the servers on the network daily.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved