Results 1 to 6 of 6

Thread: Where is my problem

  1. #1
    Join Date
    Jun 2005
    Posts
    4

    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

  2. #2
    Join Date
    Jun 2005
    Posts
    4
    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 :-)
    Last edited by ericm; 06-23-2005 at 07:07 AM.

  3. #3
    Join Date
    Jun 2005
    Posts
    4
    I give up on this one. Although it is perfect for what I need I cannot seem to get it to work properly :-(

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  5. #5
    Join Date
    Jun 2005
    Posts
    4

    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.

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •