Monitor Event Viewer on Multiple SQL Servers - Page 2

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

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

Log Table

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