?
Solved

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

Posted on 2008-11-07
4
Medium Priority
?
618 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:hmishimamster
  • 2
  • 2
4 Comments
 

Author Comment

by:hmishimamster
ID: 22908219
Ok, the lines are off compared to my script.  Line 271 is 224 on here, and line 309 is 256 here.  Sorry about that.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22963550
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 ...
0
 

Author Comment

by:hmishimamster
ID: 22963699
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
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 total points
ID: 22963816
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

0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question