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 - Page 2

By Muthusamy Anantha Kumar aka The MAK

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



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