VB Script Event Log Script - problems when dumping to MYSQL Table

I'm having a couple of problems with this script.  I pasted this together from various sources, but I am not familiar with programming in any language.  I understand the process behind what's happening here in my script (I think my comments are pretty spot on), but not what actually makes them work, or how to make them work more efficiently.

It is supposed to check the size of my Event Logs, then dump them to a MySQL DB, and then backup and clear the log.

First of all, this script uses extreme amounts of memory, and I *think* I know the problem.  It works up through the part where it's going to dump the Security Log.  I think that when it gets (line 271)to "objRS.Open "SELECT * FROM Security" , objConn, 3, 3"
It is reading the entire SQL table for the Security Log (at this point it's a hair over 700MB) and then crashing because it runs out of memory.  I watch it run in 'Task Manager" and see it using over 800MB of RAM, then suddenly stopping.  At that point I get an out of memory error if I run it using wscript.  The problem is, I don't know what to do to fix it.  Can I not read the entire table and just dump the logs?  How would I even go about that?

Secondly, this script runs on several different servers, and one errors out at line 309.  I suspect the message field is too long to fit in the table, but I have no clue what to do to allow it to be longer.  I already have the Column defined as TEXT, and I tried it as LONGTEXT, but neither work.  Any thoughts?

It will also not run AT ALL on Server 2008.  It never even shows up in the Task Manager.  I can find no information on this problem.

Any help you could give me would be greatly appreciated.  I've been trudging through various forums and google for about a week now with no luck.

Thanks

Dan
' Copy Event Log Events to a Database
' Windows Server 2003 : Yes
' Windows XP : Yes
' Windows 2000 : Yes
' Windows NT 4.0 : Yes
' Windows 98 : Yes
 
''''''''''''''''''''''''''''''''
'Begin Code for Application Log'
''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This section converts the Event Logs Unicode Time format to a standard Time format that can be utilized by SQL'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function dConvertWMItoVBSTime(sDate)
Dim sMonth, sDay, sYear, sHour, sMinutes, sSeconds
sMonth = Mid(sDate,5,2)
sDay = Mid(sDate,7,2)
sYear = Mid(sDate,1,4)
sHour = Mid(sDate,9,2)
sMinutes = Mid(sDate,11,2)
sSeconds = Mid(sDate,13,2)
longDateTime = DateSerial (sYear, sMonth, sDay) + TimeSerial (sHour, sMinutes, sSeconds)
dConvertWMItoVBSTime = Hour(longDateTime) & ":" & Minute(longDateTime) & ":" & Second(longDateTime)
End Function
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This section converts the Event Logs Unicode Date format to a standard Date format that can be utilized by SQL'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
Private Function dConvertWMItoVBSDate(sDate)
 Dim sMonth, sDay, sYear, sHour, sMinutes, sSeconds
 sMonth = Mid(sDate,5,2)
 sDay = Mid(sDate,7,2)
 sYear = Mid(sDate,1,4)
 sHour = Mid(sDate,9,2)
 sMinutes = Mid(sDate,11,2)
 sSeconds = Mid(sDate,13,2)
 longDateTime = DateSerial (sYear, sMonth, sDay) + TimeSerial (sHour, sMinutes, sSeconds)
 dConvertWMItoVBSDate = Left(longDateTime,10)
End Function
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section sets Date and Time for file names when the logs are saved to disk'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
dtmThisDay = Day(Date)
dtmThisMonth = Month(Date)
dtmThisYear = Year(Date)
dtmThisHour = Hour(Time)
dtmThisMinute = Minute(Time)
dtmThisSecond = Second(Time)
 
'''''''''''''''''''''''''''''''''''''''''''''
'Variable for the date/time of the file name'
'''''''''''''''''''''''''''''''''''''''''''''
 
strBackupName = dtmThisYear & "_" & dtmThisMonth & "_" & dtmThisDay & "--" & dtmThisHour & "-" & dtmThisMinute & "-" & dtmThisSecond
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section set the ODBC connection to the SQL Database and sets the correct table for the log dump'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "DSN=2003main;"
objRS.CursorLocation = 3
objRS.Open "SELECT * FROM Application" , objConn, 3, 3
strComputer = "."
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section sets the variables and the permissions for the script to manipulate the log files'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
Set objWMIService = GetObject("winmgmts:" _
   & "{impersonationLevel=impersonate, (Backup, Security)}!\\" & strComputer & "\root\cimv2")
Set colRetrievedEvents = objWMIService.ExecQuery _
   ("Select * from Win32_NTLogEvent Where Logfile = 'Application'")
Set colLogFiles = objWMIService.ExecQuery _
   ("Select * from Win32_NTEventLogFile where LogFileName='Application'")
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section checks the file size and determines if the log needs dumped to SQL or not'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
For Each objLogfile in colLogFiles
If objLogFile.FileSize > 500000 Then
 
''''''''''''''''''''''''''''''
'Main loop for dumping events'
''''''''''''''''''''''''''''''
For Each objEvent in colRetrievedEvents
   objRS.AddNew
   objRS("Category") = objEvent.Category
   objRS("ComputerName") = objEvent.ComputerName
   objRS("EventCode") = objEvent.EventCode
   objRS("Message") = objEvent.Message
   objRS("RecordNumber") = objEvent.RecordNumber
   objRS("SourceName") = objEvent.SourceName
   objRS("TimeWritten") = dConvertWMItoVBSTime(objEvent.TimeWritten)
objRS("DateWritten") = dConvertWMItoVBSDate(objEvent.TimeWritten)
   objRS("Type") = objEvent.Type
   objRS("User") = objEvent.User
   objRS.Update
Next
 
'''''''''''''''''''''''''''
'Closing connection to SQL'
'''''''''''''''''''''''''''
objRS.Close
objConn.Close
End If
Next
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This loop check file size and determines of the file must be backed up and cleared.  If the first loop was true,'
'this will be as well                                                                                            '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
For Each objLogfile in colLogFiles
   If objLogFile.FileSize > 500000 Then
      strBackupLog = objLogFile.BackupEventLog _
          ("c:\Event Logs\" & strBackupName & _
       "_Application.evt")
      objLogFile.ClearEventLog()
   End If
Next
 
''''''''''''''''''''''''''''''
'End Code for Application Log'
''''''''''''''''''''''''''''''
 
'''''''''''''''''''''''''''
'Begin Code for System Log'
'''''''''''''''''''''''''''
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section set the ODBC connection to the SQL Database and sets the correct table for the log dump'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "DSN=2003main;"
objRS.CursorLocation = 3
objRS.Open "SELECT * FROM System" , objConn, 3, 3
strComputer = "."
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section sets the variables and the permissions for the script to manipulate the log files'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
Set objWMIService = GetObject("winmgmts:" _
   & "{impersonationLevel=impersonate, (Backup, Security)}!\\" & strComputer & "\root\cimv2")
Set colRetrievedEvents = objWMIService.ExecQuery _
   ("Select * from Win32_NTLogEvent Where Logfile = 'System'")
   
Set colLogFiles = objWMIService.ExecQuery _
   ("Select * from Win32_NTEventLogFile where LogFileName='System'")
   
   
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section checks the file size and determines if the log needs dumped to SQL or not'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
For Each objLogfile in colLogFiles
If objLogFile.FileSize > 500000 Then
 
''''''''''''''''''''''''''''''
'Main loop for dumping events'
''''''''''''''''''''''''''''''
   
For Each objEvent in colRetrievedEvents
   objRS.AddNew
   objRS("Category") = objEvent.Category
   objRS("ComputerName") = objEvent.ComputerName
   objRS("EventCode") = objEvent.EventCode
   objRS("Message") = objEvent.Message
   objRS("RecordNumber") = objEvent.RecordNumber
   objRS("SourceName") = objEvent.SourceName
   objRS("TimeWritten") = dConvertWMItoVBSTime(objEvent.TimeWritten)
objRS("DateWritten") = dConvertWMItoVBSDate(objEvent.TimeWritten)
   objRS("Type") = objEvent.Type
   objRS("User") = objEvent.User
   objRS.Update
Next
 
'''''''''''''''''''''''''''
'Closing connection to SQL'
'''''''''''''''''''''''''''
 
objRS.Close
objConn.Close
End If
Next
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This loop check file size and determines of the file must be backed up and cleared.  If the first loop was true,'
'this will be as well                                                                                            '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
For Each objLogfile in colLogFiles
   If objLogFile.FileSize > 500000 Then
      strBackupLog = objLogFile.BackupEventLog _
          ("c:\Event Logs\" & strBackupName & _
       "_System.evt")
      objLogFile.ClearEventLog()
   End If
Next
 
'''''''''''''''''''''''''
'End Code for System Log'
'''''''''''''''''''''''''
 
'''''''''''''''''''''''''''''
'Begin Code for Security Log'
'''''''''''''''''''''''''''''
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section set the ODBC connection to the SQL Database and sets the correct table for the log dump'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "DSN=2003main;"
objRS.CursorLocation = 3
objRS.Open "SELECT * FROM Security" , objConn, 3, 3
strComputer = "."
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section sets the variables and the permissions for the script to manipulate the log files'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
Set objWMIService = GetObject("winmgmts:" _
   & "{impersonationLevel=impersonate, (Backup, Security)}!\\" & strComputer & "\root\cimv2")
Set colRetrievedEvents = objWMIService.ExecQuery _
   ("Select * from Win32_NTLogEvent Where Logfile = 'Security'")
   
Set colLogFiles = objWMIService.ExecQuery _
   ("Select * from Win32_NTEventLogFile where LogFileName='Security'")
   
   
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This section checks the file size and determines if the log needs dumped to SQL or not'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
For Each objLogfile in colLogFiles
If objLogFile.FileSize > 9000000 Then
 
''''''''''''''''''''''''''''''
'Main loop for dumping events'
''''''''''''''''''''''''''''''    
 
For Each objEvent in colRetrievedEvents
   objRS.AddNew
   objRS("Category") = objEvent.Category
   objRS("ComputerName") = objEvent.ComputerName
   objRS("EventCode") = objEvent.EventCode
   objRS("Message") = objEvent.Message
   objRS("RecordNumber") = objEvent.RecordNumber
   objRS("SourceName") = objEvent.SourceName
   objRS("TimeWritten") = dConvertWMItoVBSTime(objEvent.TimeWritten)
objRS("DateWritten") = dConvertWMItoVBSDate(objEvent.TimeWritten)
   objRS("Type") = objEvent.Type
   objRS("User") = objEvent.User
   objRS.Update
Next
 
'''''''''''''''''''''''''''
'Closing connection to SQL'
'''''''''''''''''''''''''''
 
objRS.Close
objConn.Close
End If
Next
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This loop check file size and determines of the file must be backed up and cleared.  If the first loop was true,'
'this will be as well                                                                                            '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
For Each objLogfile in colLogFiles
   If objLogFile.FileSize > 9000000 Then
      strBackupLog = objLogFile.BackupEventLog _
          ("c:\Event Logs\" & strBackupName & _
       "_Security.evt")
      objLogFile.ClearEventLog()
   End If
Next
 
'''''''''''''''''''''''''''
'End Code for Security Log'
'''''''''''''''''''''''''''
''''''''''''
'End Script'
''''''''''''

Open in new window

hmishimamsterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hmishimamsterAuthor Commented:
Ok, the lines are off compared to my script.  Line 271 is 224 on here, and line 309 is 256 here.  Sorry about that.
Daniel WilsonCommented:
re: line 271 / 225 ...
  1. Opening a recordset just to AddNew to it is not the most efficient way to work.   I would explicitly run an INSERT command for each record.
  2. If you really prefer to stick w/ opening the RS and doing AddNew, make it an empty recordset by appending WHERE 1=0 to your query.
Please see if that gets you closer ...
hmishimamsterAuthor Commented:
Just some clarification please:

Instead of:
objRS.AddNew

I'd use:
objRS.INSERT
?

Like I said, I'm not intensely familiar with the language, so I just want to be clear with what I change so I know for sure I can get an accurate test.


Or, in the 2nd case, use
objRS.Open "SELECT * FROM Security WHERE 1=0?

I'd rather go the first route and be more efficient.  Memory use can be HUGE with my logs since I am supposed to monitor success and failure.

Thanks
Daniel WilsonCommented:
in place of lines 252-265:


dim cmd
dim sql
set cmd = CreateObject("AdoDB.command")
set cmd.ActiveConnection =objConn
For Each objEvent in colRetrievedEvents
  with objEvent
   sql = "Insert Into Security(Category, computerName, EventCode, Message, RecordNumber, Sourcename, TimeWritten, DateWritten, type, User) VALUES ('" & .Category & "', '" & .ComputerName & "', '" & .EventCode & "', '" & "', '" & .RecordNumber & "', '" & .SourceName & "', '" & .TimeWritten & "', '" & .Type & "', '" & .User & "')"
  end with
  cmd.CommandText = sql
  cmd.Execute
Next

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.