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:

 #!usr/bin/perl

# 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="\\\\192.168.1.2"; #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",
4 => "INFORMATION",
8 => "AUDIT_SUCCESS",
16 => "AUDIT_FAILURE");


$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) {
        $handle->Read(EVENTLOG_FORWARDS_READ|EVENTLOG_SEEK_READ,
                                  $base+$j,
                                  my $hashRef)
                or die "Can't read EventLog entry #$j\n";
                Win32::EventLog::GetMessageText($hashRef);
                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);
        $j++;
        $i++;

}}

$dbh->disconnect();

To examine the output of the script, I would recommend the SQLite Database Browser (http://sqlitebrowser.sourceforge.net/) 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: