Code:
'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 GoTo DBerr;
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;Data Source='emierimar-lt\ecrsql';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) "
string1 = string1 & " values ('" + objEvent.Logfile + "',"
string1 = string1 & "'" + CStr(objEvent.Category) + "',"
string1 = string1 & "'" + objEvent.ComputerName + "',"
string1 = string1 & "'" + mymessage + "',"
string1 = string1 & " '" + objEvent.SourceName + "',"
string1 = string1 & "convert(datetime,left(" + Mydate + " ,8) + ' '+substring(" + Mydate + ",9,2) + ':"
string1 = string1 & "'+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) "
string1 = string1 & " values ('" + objEvent.Logfile + "',"
string1 = string1 & "'" + CStr(objEvent.Category) + "',"
string1 = string1 & "'" + objEvent.ComputerName + "',"
string1 = string1 & "'" + mymessage + "',"
string1 = string1 & " '" + objEvent.SourceName + "',"
string1 = string1 & "convert(datetime,left(" + Mydate + " ,8) + ' '+substring(" + Mydate + ",9,2) + ':"
string1 = string1 & "'+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 colRetrievedEvents= GetObject("winmgmts:{impersonationLevel=impersonate}\\"+strcomputer+"\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