Wednesday, May 23, 2012

Copy Windows Event Logs into an SQLite Database

For anyone that has to administer, troubleshoot, or audit Windows systems, the records stored in the Windows Event Log can be a treasure trove of information.  The one potential problem is that there are often many thousands of entries in such logs which can often make finding information of interest a challenge.  The Perl script below makes use of the Win32::EventLog module as a means of offloading the records stored in the Event Log to an SQLite database, which can later be searched for pertinent information.  The script is as follows:


# Copyright 2012- Christopher M. Frenz
# This script is free software - it may be used, copied, redistributed, and/or modified
# under the terms laid forth in the Perl Artistic License
use DBI;
use Win32::EventLog; #v0.076 used for development
use strict;
use warnings;

my $server="\\\\"; #put UNC name or IP address here

#creates/opens SQLite DB
my $dbh = DBI->connect("dbi:SQLite:dbname=EventLogData.sql","","");

#Removes Table Data if it already exists and creates new Table Data
$dbh->do("DROP TABLE Data");
$dbh->do( "CREATE TABLE Data (Record INTEGER PRIMARY KEY, EventLog,Server,Time,Source,Message,EventID);" );

my %type = (1 => "ERROR",
2 => "WARNING",

$Win32::EventLog::GetMessageText = 1;
my $i=0;
#processes System, Appication, and Security Logs
#inserts each event log record into SQLite DB
for my $eventlog ("System", "Application", "Security") {
   my $handle = new Win32::EventLog($eventlog, $server)
   or die "Unable to open system log:$^En";
   $handle->GetNumber(my $recs)
        or die "Can't get number of EventLog records\n";
   $handle->GetOldest(my $base)
        or die "Can't get number of oldest EventLog record\n";
   my $j=0;
   while ($j < $recs) {
                                  my $hashRef)
                or die "Can't read EventLog entry #$j\n";
                my $time=scalar localtime($hashRef->{TimeGenerated});
                my $source=$type{$hashRef->{EventType}};
                my $message=$hashRef->{Message};
                my $eventID=($hashRef->{EventID}& 0xffff);
                my $sql= 'INSERT INTO Data(Record, EventLog,Server,Time,Source,Message,EventID) VALUES (?,?,?,?,?,?,?)';
                my $insert=$dbh->prepare($sql);
                $insert->execute($i,$eventlog, $server,$time,$source,$message,$eventID);



To examine the output of the script, I would recommend the SQLite Database Browser ( which provides a nice GUI interface for visually inspecting the contents of a SQLite database.  A screenshot showing a sample database created by the script can be seen below:

No comments: