-
Where is my problem
I found this script here on this site and have been able to get it to finally work without getting some errors. (It is the way I copy and pasted the code that was the problem) Anyways the script runs with no errors reported but when I open the db to look for new data everything is blank. So I am assuming it is not connecting to the db and there is no error being returned. (I am new at this so this is a big assumption on my part) Anyways will someone take a look at this quickly and see if the error is in the AdCn.OPEN line.
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
-
ok I figured out one thing. I commented out the on error resume next part and now I see the error.
Line 10
Char 1
Login Failed for user ... Not associated with a trusted SQL Server connection
code 80004005
source Microsoft OLE DB Provider
Ok Fixed that one. Now onto another. I guess I just needed to post it to get a different look at it. I'll let you all know if I am going to need additional assistance. It does help when you can see errors appear :-)
-
I give up on this one. Although it is perfect for what I need I cannot seem to get it to work properly :-(
-
-
Working now
The problem was with the way everything got pasted from the webpage article to notepad and then into .net. Anyways after all the debugging here is how I got it to work. the only issue I cannot seem to get to work now is the asp pages from the article.
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 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;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 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 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 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 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 logtable(notes) values ('" + strComputer + ": monitoring Completed')"
AdRec.Open ErrorSQL, AdCn,1,1
Adrec1.movenext
Wend
ErrorSQL="insert into 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}!\\" & strComputer & "\root\cimv2")
Set colTimeZone = objSWbemServices.ExecQuery _
("SELECT * FROM Win32_TimeZone")
For Each objTimeZone in colTimeZone
strBias = objTimeZone.Bias
' 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
I made some changes in the function UTC routine but not sure if those are what is effecting the asp pages.
-
I wrote this long time back. It is a simple asp page which queries the Eventlog where the monitordate is todays date.
Make sure you have adovbs.inc on default folder. update the connection information.
<!-- #include file="adovbs.inc" -->
<HTML>
<center>
<H1> Event Log Monitor </H1>
<BR>
<BR><BR>
<%
Session.Timeout = 480
set connection=server.createObject("ADODB.Connection")
connection.OPEN "SERVER=SQl2k\instance1;DATABASE=EventLogMonitor;U ID=EventLogUser;PWD=Event;DRIVER={SQL Server};"
set rs = server.createobject("ADODB.Recordset")
rs.cursortype = adopenstatic
query = "select * from Eventlog where convert(varchar(10),Monitordate,112) >= convert(varchar(10),getdate(),112)"
rs.open query,connection %>
<TABLE border=1 bordercolor=BLACK cellPadding=1 cellSpacing=0 width="75%" align=center>
<TD></TD>
<TR>
<TD bgColor=YELLOW><B><font color = "blue" face="VERDANA" STYLE="font-size:12px">Logtype</B></TD>
<TD bgColor=YELLOW><B><font color = "blue" face="VERDANA" STYLE="font-size:12px">Category</B></TD>
<TD bgColor=YELLOW><B><font color = "blue" face="VERDANA" STYLE="font-size:12px">Server</B></TD>
<TD bgColor=YELLOW><B><font color = "blue" face="VERDANA" STYLE="font-size:12px">Message</B></TD>
<TD bgColor=YELLOW><B><font color = "blue" face="VERDANA" STYLE="font-size:12px">SourceName</B></TD>
<TD bgColor=YELLOW><B><font color = "blue" face="VERDANA" STYLE="font-size:12px">TimeWritten</B></TD>
<TD bgColor=YELLOW><B><font color = "blue" face="VERDANA" STYLE="font-size:12px">Type</B></TD>
<TD bgColor=YELLOW><B><font color = "blue" face="VERDANA" STYLE="font-size:12px">User</B></TD>
<TD bgColor=YELLOW><B><font color = "blue" face="VERDANA" STYLE="font-size:12px">TimeStamp</B></TD>
<!--rgb(61,32,87) -->
<TR><TD></td></tr>
<%do while not rs.eof%>
<TR>
<TD bgColor=C7C7C7><font color = "black" face="VERDANA" STYLE="font-size:11px" ><%=rs("Logtype")%></B></TD>
<TD bgColor=C7C7C7><font color = "black" face="VERDANA" STYLE="font-size:10px" ><%=rs("Category")%></B></TD>
<TD bgColor=C7C7C7><font color = "black" face="VERDANA" STYLE="font-size:10px" ><%=rs("Computername")%></B></TD>
<TD bgColor=C7C7C7><font color = "black" face="VERDANA" STYLE="font-size:10px" ><%=rs("Message")%></B></TD>
<TD bgColor=C7C7C7><font color = "black" face="VERDANA" STYLE="font-size:10px" ><%=rs("Sourcename")%></B></TD>
<TD bgColor=C7C7C7><font color = "black" face="VERDANA" STYLE="font-size:10px" ><%=rs("TimeWritten")%></B></TD>
<TD bgColor=C7C7C7><font color = "black" face="VERDANA" STYLE="font-size:10px" ><%=rs("Type")%></B></TD>
<TD bgColor=C7C7C7><font color = "black" face="VERDANA" STYLE="font-size:10px" ><%=rs("AUser")%></B></TD>
<TD bgColor=C7C7C7><font color = "black" face="VERDANA" STYLE="font-size:10px" ><%=rs("Monitordate")%></B></TD>
<%rs.movenext%>
</TR>
<%loop%>
</table>
<BR><BR>
<center><HR color=maroon></center>
<BR>
<BR>
