Copying Events to a Database

Microsoft® Windows® 2000 Scripting Guide

microsoft windows 2000 scripting guide

« Previous | Next »   

WMI provides a way to programmatically access the records in an event log. Although this is an extremely useful capability, WMI does have at least two limitations when it comes to querying large data sources such as event logs:

  • The WMI Query Language (WQL) contains only a small subset of the keywords and commands in Structured Query Language (SQL).

    For example, you might want to tally frequency statistics for the events in an event log. (This would tell you that Event ID 1 has occurred X number of times, Event ID 2 has occurred Y number of times, and so forth.) This type of query can be constructed in SQL by using code similar to the following (assuming you have a database named EventLog):

    SELECT Count(EventID) AS CountOfEventID, EventID FROM EventLog GROUP BY CountOfEventID 

    However, this kind of query cannot be constructed using WQL.

  • Unlike a database, event logs are not optimized to process queries and rapidly return information.

    A query against an event log with a large number of records can take a long time to complete. On a Windows 2000 based test computer with an event log containing 12,000 records, a query that returned events with a specific Event ID required 48 seconds to complete. The same query run against an SQL database holding the exact same information completed in less than 1 second.

To carry out a regular and detailed analysis of your event logs, you might want to periodically extract the records from the event log and copy them to a database. Copying the records to a database enables you to create more sophisticated queries and greatly decreases the time it takes to run these queries.

Copying records to a database also allows you to combine the event logs from multiple computers. For example, you might combine all the DNS event logs from all your DNS servers into a single database. Using that database, you can easily construct queries that compare the events that occur on one DNS server with the events that occur on your other DNS servers.

Scripting Steps

You can periodically copy all the events from an event log to a database, clear the event log, and allow events to accumulate until you copy the events and clear the log again.

Listing 12.12 contains a script that retrieves all the events from an event log and copies them to a database. Before you create this script, you need to create the following:

  • A database with the System Data Source Name (DSN) of EventLogs.

    For information about accessing databases by using scripts, see "Creating Enterprise Scripts" in this book.

  • A table in the database named EventTable with field names equivalent to the field names used in the script:
    • Category
    • ComputerName
    • EventCode
    • Message
    • RecordNumber
    • SourceName
    • TimeWritten
    • Type
    • User

To carry out this task, the script must perform the following steps:

  1. Create the following two objects:
    • ADODB.Connection, representing a connection to an Active Data Objects (ADO) database.
    • ADODB.Recordset, representing an ADO recordset.
  2. Open the database with the System DSN of EventLogs.
  3. Open the table EventTable, using the SQL query "Select * from EventTable."
  4. Create a variable to specify the computer name.
  5. Use a GetObject call to connect to the WMI namespace root\cimv2 on the computer, and set the impersonation level to "impersonate."
  6. Use the ExecQuery method to query the Win32_NTLogEvent class.

    This query returns a collection consisting of all the events from all the event logs, except the Security event log.

  7. For each event in the collection, use the AddNew command to add a new record to the database.

    The appropriate fields in the new record are then populated using the properties of the event record.

  8. After the record has been populated, use the Update command to write the new data to the database.
  9. Close the database connection and the recordset.

Listing 12.12   Copying Events to a Database

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 
Set objConn = CreateObject("ADODB.Connection") Set objRS = CreateObject("ADODB.Recordset") objConn.Open "DSN=EventLogs;" objRS.CursorLocation = 3 objRS.Open "SELECT * FROM EventTable" , objConn, 3, 3 strComputer = "." Set objWMIService = GetObject("winmgmts:" _     & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") Set colRetrievedEvents = objWMIService.ExecQuery _     ("SELECT * FROM Win32_NTLogEvent") 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") = objEvent.TimeWritten     objRS("Type") = objEvent.Type     objRS("User") = objEvent.User     objRS.Update Next objRS.Close objConn.Close

send us your feedback Send us your feedback « Previous | Next »   


Microsoft Windows 2000 Scripting Guide(c) Automating System Administration 2003
Microsoft Windows 2000 Scripting Guide(c) Automating System Administration 2003
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 635

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net